dinsdag 23 december 2008

Capacity Planning for SQL Server 2000 Database Storage

http://www.mssqltips.com/tip.asp?tip=1267
==========================================
Problem
----------:
I am running very low on storage to support my SQL Server 2000 user defined databases on a number of my production SQL Server instances. We have made the decision to invest in some sort of NAS\SAN or direct attached storage solution. Independent of which technology, vendor and RAID sets we select, I need to plan for my database storage for the next 18 to 24 months and beyond. I have seen your script for capacity planning at a table level (Easing the Capacity Planning Burden), but how can I roll this up at a database level? I need to determine the storage needs at a database and SQL Server instance.

Solution
---------:
To me the best way to determine future storage needs is to analyze the past with an eye out for potential business changes that cannot be reflected from historical calculations. As such, below is script that calculates the needed storage at a per SQL Server instance and database level. This script will review the database backup sizes at a per database level to calculate the storage needs for the future. Check out this SQL Server database capacity planning script:

Table - SQL Server 2000 Database Capacity Planning

CREATE TABLE [dbo].[CapacityPlanning] (
[CPID] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DatabaseName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ExecutionDateTime] [datetime] NULL ,
[NewDatabaseSize] [decimal](18, 0) NULL ,
[OldDatabaseSize] [decimal](18, 0) NULL ,
[NewCreationDate] [datetime] NULL ,
[OldCreationDate] [datetime] NULL ,
[VarDiff] [decimal](18, 0) NULL ,
[PercentGrowth] [decimal](18, 0) NULL ,
[AvgGrowth] [decimal](18, 0) NULL ,
[DateDiff] [decimal](18, 0) NULL ,
[Yr1DBProjections] [decimal](18, 0) NULL ,
[Yr1LogProjections] [decimal](18, 0) NULL ,
[Yr1DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr1LogProjections15Percent] [decimal](18, 0) NULL ,
[Total1YrProj] [decimal](18, 0) NULL ,
[Total1YrProj15Percent] [decimal](18, 0) NULL ,
[Yr2DBProjections] [decimal](18, 0) NULL ,
[Yr2LogProjections] [decimal](18, 0) NULL ,
[Yr2DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr2LogProjections15Percent] [decimal](18, 0) NULL ,
[Total2YrProj] [decimal](18, 0) NULL ,
[Total2YrProj15Percent] [decimal](18, 0) NULL ,
[Yr3DBProjections] [decimal](18, 0) NULL ,
[Yr3LogProjections] [decimal](18, 0) NULL ,
[Yr3DBProjections15Percent] [decimal](18, 0) NULL ,
[Yr3LogProjections15Percent] [decimal](18, 0) NULL ,
[Total3YrProj] [decimal](18, 0) NULL ,
[Total3YrProj15Percent] [decimal](18, 0) NULL ,
[TotalNumberofDatabases] [int] NULL
) ON [PRIMARY]
GO
Script - SQL Server 2000 Database Capacity Planning

CREATE PROCEDURE spCapacityPlanning AS

/*
----------------------------------------------------------------------------
-- Object Name: spCapacityPlanning
-- Project: Capacity Planning
-- Business Process: Capacity Planning
-- Purpose: Calculate the capacity planning for 1, 2 and 3 years for the database and transaction log
-- Detailed Description: Capture static information and write infromation to the
-- dbo.CapacityPlanning table for the database and transaction log calculations
-- Database: TBD
-- Dependent Objects:
-- - Master.dbo.sysdatabases
- MSDB.dbo.backupset
- TBD.dbo.CapacityPlanning
-- Called By: TBD
-- Upstream Systems: N\A
-- Downstream Systems: N\A

--
--------------------------------------------------------------------------------------
-- Rev | CMR | Date Modified | Developer | Change Summary
--------------------------------------------------------------------------------------
-- 001 | N\A | 06.15.2007 | Edgewood | Original code
--
*/

SET NOCOUNT ON

-- Step 1 - Preliminary Information
SELECT @@SERVERNAME AS 'Server Name'
SELECT GETDATE() AS 'Execution Timestamp'
PRINT '--------------------------------------------------------'
PRINT '********************************************************'
PRINT ''
SELECT 'Disk Space Availablity'
PRINT ''
PRINT '********************************************************'
PRINT '--------------------------------------------------------'
PRINT ''
PRINT ''
EXEC Master.dbo.xp_fixeddrives


-- Step 2 - Declare the cursor variables
--Prepatory Variables
DECLARE @DatabaseName VARCHAR(50)
DECLARE @ExecutionDateTime DateTime
DECLARE @NewDatabaseSize Decimal
DECLARE @OldDatabaseSize Decimal
DECLARE @NewCreationDate DateTime
DECLARE @OldCreationDate DateTime
DECLARE @VarDiff Decimal
DECLARE @PercentGrowth Decimal
DECLARE @AvgGrowth Decimal
DECLARE @DateDiff Decimal

-- 1 Year Variables
DECLARE @Yr1DBProjections Decimal
DECLARE @Yr1LogProjections Decimal
DECLARE @Yr1DBProjections15Percent Decimal
DECLARE @Yr1LogProjections15Percent Decimal

-- 2 Year Variables
DECLARE @Yr2DBProjections Decimal
DECLARE @Yr2LogProjections Decimal
DECLARE @Yr2DBProjections15Percent Decimal
DECLARE @Yr2LogProjections15Percent Decimal

-- 3 Year Variables
DECLARE @Yr3DBProjections Decimal
DECLARE @Yr3LogProjections Decimal
DECLARE @Yr3DBProjections15Percent Decimal
DECLARE @Yr3LogProjections15Percent Decimal

-- Total Historical Variables
DECLARE @TotalRecentDatabaseSize Decimal
DECLARE @TotalOldDatabaseSize Decimal
DECLARE @TotalDiffDatabaseSize Decimal
DECLARE @TotalPercentageGrowth Decimal
DECLARE @AvgPercentageGrowth Decimal
DECLARE @AvgDateDiff Decimal
DECLARE @TotalNumberofDatabases Decimal
DECLARE @TotalDateDiff Decimal

-- Total Projection Variables
DECLARE @Total1YrDBProj Decimal
DECLARE @Total1YrLogProj Decimal
DECLARE @Total1YrDBProj15Percent Decimal
DECLARE @Total1YrLogProj15Percent Decimal
DECLARE @Total1YrProj Decimal -- Database and Log
DECLARE @Total1YrProj15Percent Decimal -- Database and Log

DECLARE @Total2YrDBProj Decimal
DECLARE @Total2YrLogProj Decimal
DECLARE @Total2YrDBProj15Percent Decimal
DECLARE @Total2YrLogProj15Percent Decimal
DECLARE @Total2YrProj Decimal -- Database and Log
DECLARE @Total2YrProj15Percent Decimal -- Database and Log

DECLARE @Total3YrDBProj Decimal
DECLARE @Total3YrLogProj Decimal
DECLARE @Total3YrDBProj15Percent Decimal
DECLARE @Total3YrLogProj15Percent Decimal
DECLARE @Total3YrProj Decimal -- Database and Log
DECLARE @Total3YrProj15Percent Decimal -- Database and Log

-- Initialize Historical Variables
SELECT @ExecutionDateTime = GETDATE()
SELECT @TotalNumberofDatabases = 0
SELECT @TotalRecentDatabaseSize = 0
SELECT @TotalOldDatabaseSize = 0
SELECT @TotalDiffDatabaseSize = 0
SELECT @TotalPercentageGrowth = 0
SELECT @AvgPercentageGrowth = 0
SELECT @AvgDateDiff = 0
SELECT @TotalDateDiff = 0

SELECT @Total1YrDBProj = 0
SELECT @Total1YrLogProj = 0
SELECT @Total1YrDBProj15Percent = 0
SELECT @Total1YrLogProj15Percent = 0
SELECT @Total1YrProj = 0 -- Database and Log
SELECT @Total1YrProj15Percent = 0-- Database and Log

SELECT @Total2YrDBProj = 0
SELECT @Total2YrLogProj = 0
SELECT @Total2YrDBProj15Percent = 0
SELECT @Total2YrLogProj15Percent = 0
SELECT @Total2YrProj = 0 -- Database and Log
SELECT @Total2YrProj15Percent = 0 -- Database and Log

SELECT @Total3YrDBProj = 0
SELECT @Total3YrLogProj = 0
SELECT @Total3YrDBProj15Percent = 0
SELECT @Total3YrLogProj15Percent = 0
SELECT @Total3YrProj = 0 -- Database and Log
SELECT @Total3YrProj15Percent = 0 -- Database and Log

-- Step 3 - Begin Cursor Processing
DECLARE CapPlanCursor CURSOR FOR

SELECT Name
FROM master.dbo.sysdatabases
ORDER BY Name

OPEN CapPlanCursor

FETCH NEXT FROM CapPlanCursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN
-- Prepatory Calculations

SELECT @NewDatabaseSize = ((backup_size)/1024/1024), @NewCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id

SELECT @OldDatabaseSize = ((backup_size)/1024/1024), @OldCreationDate = (backup_start_date)
FROM MSDB.dbo.backupset
WHERE database_name = @DatabaseName
AND TYPE = 'D'
ORDER BY backup_set_id DESC

SELECT @VarDiff = (@NewDatabaseSize - @OldDatabaseSize)

SELECT @PercentGrowth = (((@NewDatabaseSize/@OldDatabaseSize)-1)* 100)

SELECT @DateDiff = DATEDIFF(dd, @OldCreationDate, @NewCreationDate)

SELECT @AvgGrowth = (@VarDiff/@DateDiff)

-- Year 1 Figures
SELECT @Yr1DBProjections = ((@AvgGrowth * 365) + @NewDatabaseSize)
SELECT @Yr1DBProjections15Percent = ((@Yr1DBProjections * .15) + @Yr1DBProjections)
SELECT @Yr1LogProjections = (@Yr1DBProjections/4)
SELECT @Yr1LogProjections15Percent = ((@Yr1LogProjections * .15) + @Yr1LogProjections)

-- Year 2 Figures
SELECT @Yr2DBProjections = ((@AvgGrowth * 730) + @NewDatabaseSize)
SELECT @Yr2DBProjections15Percent = ((@Yr2DBProjections * .15) + @Yr2DBProjections)
SELECT @Yr2LogProjections = (@Yr2DBProjections/4)
SELECT @Yr2LogProjections15Percent = ((@Yr2LogProjections * .15) + @Yr2LogProjections)

-- Year 3 Figures
SELECT @Yr3DBProjections = ((@AvgGrowth * 1095) + @NewDatabaseSize)
SELECT @Yr3DBProjections15Percent = ((@Yr3DBProjections * .15) + @Yr3DBProjections)
SELECT @Yr3LogProjections = (@Yr3DBProjections/4)
SELECT @Yr3LogProjections15Percent = ((@Yr3LogProjections * .15) + @Yr3LogProjections)

-- Calculation Totals
SELECT @TotalRecentDatabaseSize = @TotalRecentDatabaseSize + @NewDatabaseSize
SELECT @TotalOldDatabaseSize = @TotalOldDatabaseSize + @OldDatabaseSize
SELECT @TotalDiffDatabaseSize = @TotalDiffDatabaseSize + @VarDiff
SELECT @TotalNumberofDatabases = @TotalNumberofDatabases + 1
SELECT @TotalPercentageGrowth = @TotalPercentageGrowth + @AvgGrowth
SELECT @TotalDateDiff = @TotalDateDiff + @DateDiff

-- Year 1 Projection Totals
SELECT @Total1YrDBProj = @Yr1DBProjections + @Total1YrDBProj
SELECT @Total1YrLogProj = @Yr1LogProjections + @Total1YrLogProj
SELECT @Total1YrDBProj15Percent = @Yr1DBProjections15Percent + @Total1YrDBProj15Percent
SELECT @Total1YrLogProj15Percent = @Yr1LogProjections15Percent + @Total1YrLogProj15Percent

-- Year 2 Projection Totals
SELECT @Total2YrDBProj = @Yr2DBProjections + @Total2YrDBProj
SELECT @Total2YrLogProj = @Yr2LogProjections + @Total2YrLogProj
SELECT @Total2YrDBProj15Percent = @Yr2DBProjections15Percent + @Total2YrDBProj15Percent
SELECT @Total2YrLogProj15Percent = @Yr2LogProjections15Percent + @Total2YrLogProj15Percent

-- Year 3 Projection Totals
SELECT @Total3YrDBProj = @Yr3DBProjections + @Total3YrDBProj
SELECT @Total3YrLogProj = @Yr3LogProjections + @Total3YrLogProj
SELECT @Total3YrDBProj15Percent = @Yr3DBProjections15Percent + @Total3YrDBProj15Percent
SELECT @Total3YrLogProj15Percent = @Yr3LogProjections15Percent + @Total3YrLogProj15Percent

-- Insert values into the dbo.CapacityPlanning table
INSERT INTO dbo.CapacityPlanning
(
ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
)
VALUES
(
@@ServerName
,@DatabaseName
,@ExecutionDateTime
,@NewDatabaseSize
,@OldDatabaseSize
,@NewCreationDate
,@OldCreationDate
,@VarDiff
,@PercentGrowth
,@AvgGrowth
,@DateDiff
,@Yr1DBProjections
,@Yr1LogProjections
,@Yr1DBProjections15Percent
,@Yr1LogProjections15Percent
,@Yr2DBProjections
,@Yr2LogProjections
,@Yr2DBProjections15Percent
,@Yr2LogProjections15Percent
,@Yr3DBProjections
,@Yr3LogProjections
,@Yr3DBProjections15Percent
,@Yr3LogProjections15Percent
)

FETCH NEXT FROM CapPlanCursor INTO @DatabaseName

END

-- Step 4 - Calculate Aggregates
-- Historical Totals
SELECT @AvgPercentageGrowth = (@TotalPercentageGrowth/@TotalNumberofDatabases)
SELECT @AvgDateDiff = (@TotalDateDiff/@TotalNumberofDatabases)

-- Year 1 Totals
SELECT @Total1YrProj = @Total1YrDBProj + @Total1YrLogProj -- Database and Log
SELECT @Total1YrProj15Percent = @Total1YrDBProj15Percent + @Total1YrLogProj15Percent -- Database and Log

-- Year 2 Totals
SELECT @Total2YrProj = @Total2YrDBProj + @Total2YrLogProj -- Database and Log
SELECT @Total2YrProj15Percent = @Total2YrDBProj15Percent + @Total2YrLogProj15Percent -- Database and Log

-- Year 3 Totals
SELECT @Total3YrProj = @Total3YrDBProj + @Total3YrLogProj -- Database and Log
SELECT @Total3YrProj15Percent = @Total3YrDBProj15Percent + @Total3YrLogProj15Percent -- Database and Log

-- Step 5 - Insert Into Capacity Planning Table
INSERT INTO CapacityPlanning
(ServerName
,DatabaseName
,ExecutionDateTime
,NewDatabaseSize
,OldDatabaseSize
,NewCreationDate
,OldCreationDate
,VarDiff
,PercentGrowth
,AvgGrowth
,DateDiff
,Yr1DBProjections
,Yr1LogProjections
,Yr1DBProjections15Percent
,Yr1LogProjections15Percent
,Total1YrProj
,Total1YrProj15Percent
,Yr2DBProjections
,Yr2LogProjections
,Yr2DBProjections15Percent
,Yr2LogProjections15Percent
,Total2YrProj
,Total2YrProj15Percent
,Yr3DBProjections
,Yr3LogProjections
,Yr3DBProjections15Percent
,Yr3LogProjections15Percent
,Total3YrProj
,Total3YrProj15Percent
,TotalNumberofDatabases
)
VALUES
(
@@ServerName
,'Total Calculations'
,@ExecutionDateTime
,@TotalRecentDatabaseSize
,@TotalOldDatabaseSize
,NULL
,NULL
,@TotalDiffDatabaseSize
,NULL -- @AvgPercentageGrowth
,@TotalPercentageGrowth
,@AvgDateDiff
,@Total1YrDBProj
,@Total1YrLogProj
,@Total1YrDBProj15Percent
,@Total1YrLogProj15Percent
,@Total1YrProj
,@Total1YrProj15Percent
,@Total2YrDBProj
,@Total2YrLogProj
,@Total2YrDBProj15Percent
,@Total2YrLogProj15Percent
,@Total2YrProj
,@Total2YrProj15Percent
,@Total3YrDBProj
,@Total3YrLogProj
,@Total3YrDBProj15Percent
,@Total3YrLogProj15Percent
,@Total3YrProj
,@Total3YrProj15Percent
,@TotalNumberofDatabases
)

-- Step 6 - Generate Report
SELECT *
FROM CapacityPlanning
WHERE ExecutionDateTime = @ExecutionDateTime

CLOSE CapPlanCursor

DEALLOCATE CapPlanCursor

SET NOCOUNT OFF

GO

Special Notes:
-------------

To the best of my knowledge, this script will work for SQL Server databases that are using third party backup products (SQL Server 2005 Backup Product Options). This is because the values in backup system tables in the MSDB database are capturing values from the SQL Server Virtual Device Interface (VDI) as opposed to the compressed values from the third party product. This means that the backups can give you a historical view to use as a reasonable planning tool.
All businesses change and therefore those applications change as well. Some applications are retired while others are brought into the production environment. As such, this script will not be able to help estimate the future growth without having a representative amount of backups for calculations.
On a related, if you know you are bringing on a new customer that is going to double the revenue for the organization, odds are that you are going to have to plan for this storage as well independent of the script listed in this tip. You may be able to anecdotally plan based on the script, but the script is making a prediction on the storage needs based on the historical trends.

Next Steps
----------:
Keep an eye on the available storage across your SQL Server 2000 instances. When you are faced with the daunting task of determining how much storage you will need to support your databases at a SQL Server instance level, consider this script.
As a proactive DBA, it would be a good idea to monitor the storage on your SQL Server instances to determine how long the current storage will support the current databases at the current growth rate.
Once you have calculated the amount of storage that is needed to support your databases at a SQL Server instance level, make sure you will have sufficient storage based on the RAID set, hot spares, etc. that are needed. Work with your vendor to figure out the needed storage based on the hardware capabilities and\or functionality.

How do I list all the indexes in a database?

http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-list-all-the-indexes-in-a-database.html

SQL Server 2000
===============:
Yes, you can use the following for a specific table:

EXEC sp_helpindex 'tablename'

This returns index_name, index_description, and index_keys. The index_description column tells whether or not the index is clustered, and which filegroup it resides on. The index_keys column tells you the column names that participate in the index, and from what I can tell, these are always in the order they are created (a negative symbol (-) denotes that the column is in DESC order).

This is great, but does not provide all of the information I'm often looking for.

In order to return everything I wanted to know about the indexes in my database, I needed to create a couple of extra helper functions. (Unfortunately, indexes are not covered in the INFORMATION_SCHEMA views, so we need to rely on system tables like sysindexes and sysfilegroups, and system functions like INDEXPROPERTY() and INDEX_COL().) The first function is not required, but makes the second function quite tidier, IMHO:

-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@object_id INT,
@index_id TINYINT,
@column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @r NVARCHAR(5)
SELECT @r = CASE INDEXKEY_PROPERTY
(
@object_id,
@index_id,
@column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @r
END
GO

-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@table_name SYSNAME,
@object_id INT,
@index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@colnames NVARCHAR(4000),
@thisColID INT,
@thisColName SYSNAME

SET @colnames = INDEX_COL(@table_name, @index_id, 1)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, 1)

SET @thisColID = 2
SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)

WHILE (@thisColName IS NOT NULL)
BEGIN
SET @thisColID = @thisColID + 1
SET @colnames = @colnames + ', ' + @thisColName

SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
END
RETURN @colNames
END
GO

These functions are based largely on sp_helpindex, and while they avoid cursors, they are still not likely to be very efficient as the functions will need to be called multiple times.

Now that we have these functions, we can create this view:

CREATE VIEW dbo.vAllIndexes
AS
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
GO

This will give you a handy resultset, but does not specify whether the index is a PRIMARY KEY CONSTRAINT. You can do that by joining against INFORMATION_SCHEMA.TABLE_CONSTRAINTS:

SELECT
v.*,
[PrimaryKey?] = CASE
WHEN T.TABLE_NAME IS NOT NULL THEN 1
ELSE 0
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'

This doesn't take into account same-named tables owned by different users, but if you look at the alternative (see the source code for sp_pkeys), it is probably a valid solution for most of us, where dbo is the de facto owner of all objects.

With that limitation in mind, we can take it one step further by generating the CREATE INDEX / ADD CONSTRAINT statements:

SELECT
CASE WHEN T.TABLE_NAME IS NULL THEN
'CREATE '
+ CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']'
+ ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP
ELSE
'ALTER TABLE ['+T.TABLE_NAME+']'
+' ADD CONSTRAINT ['+INDEX_NAME+']'
+' PRIMARY KEY '
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' (' + COLUMN_LIST + ')'
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
v.TABLE_NAME,
IS_CLUSTERED DESC

This is what I have to offer for now, and I realize it is pretty quick and dirty. I'll be working on a similar script using the SQL Server 2005 catalog views, but I'll save that for another day.