Hi Lowell
I am using your procedure for an automatic update script (from develpoment --> productive server).
During the test of the update script I found some differences which have lead to the improvements.
So it was a matter of chance.
I made an another improvement to add the filegroup of the table or indexes.
This time I will just post the changed script to keep the post shorter.
I added the declartion of the @DATASPACE_NAME variable.
DECLARE @TBLNAME VARCHAR(200),
@SCHEMANAME VARCHAR(255),
@STRINGLEN INT,
@TABLE_ID INT,
@FINALSQL VARCHAR(max),
@CONSTRAINTSQLS VARCHAR(max),
@CHECKCONSTSQLS VARCHAR(max),
@RULESCONSTSQLS VARCHAR(max),
@FKSQLS VARCHAR(max),
@TRIGGERSTATEMENT VARCHAR(max),
@EXTENDEDPROPERTIES VARCHAR(max),
@INDEXSQLS VARCHAR(max),
@vbCrLf CHAR(2),
@ISSYSTEMOBJECT INT,
@PROCNAME VARCHAR(256),
@DATASPACE_NAME VARCHAR(max)
The "dataspace_name" column was added on the end of the @Results table.
The "ISNULL(DSP.name, '---') dataspace_name" line was added in the select part.
The "INNER JOIN sys.data_spaces DSP ON DSP.data_space_id = IDX.data_space_id" line was added in the from part.
--##############################################################################
--PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax
--##############################################################################
DECLARE @Results TABLE (
[schema_id] INT,
[schema_name] VARCHAR(255),
[object_id] INT,
[object_name] VARCHAR(255),
[index_id] INT,
[index_name] VARCHAR(255),
[Rows] INT,
[SizeMB] DECIMAL(19,3),
[IndexDepth] INT,
[type] INT,
[type_desc] VARCHAR(30),
[fill_factor] INT,
[is_unique] INT,
[is_primary_key] INT ,
[is_unique_constraint] INT,
[index_columns_key] VARCHAR(max),
[index_columns_include] VARCHAR(max),
[dataspace_name]VARCHAR(MAX))
INSERT INTO @Results
SELECT
SCH.schema_id, SCH.[name] AS schema_name,
OBJS.[object_id], OBJS.[name] AS object_name,
IDX.index_id, ISNULL(IDX.[name], '---') AS index_name,
partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,
IDX.type, IDX.type_desc, IDX.fill_factor,
IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,
ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,
ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include,
ISNULL(DSP.name, '---') dataspace_name
FROM sys.objects OBJS
INNER JOIN sys.schemas SCH ON OBJS.schema_id=SCH.schema_id
INNER JOIN sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]
INNER JOIN sys.data_spaces DSP ON DSP.data_space_id = IDX.data_space_id
INNER JOIN (
SELECT
[object_id], index_id, SUM(row_count) AS Rows,
CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB
FROM sys.dm_db_partition_stats STATS
GROUP BY [object_id], index_id
) AS partitions
ON IDX.[object_id]=partitions.[object_id]
AND IDX.index_id=partitions.index_id
The last "case when dataspace_name ...." statement was added in the select part.
--##############################################################################
--constriants
--##############################################################################
SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS
+ CASE
WHEN is_primary_key = 1 or is_unique = 1
THEN @vbCrLf
+ 'CONSTRAINT [' + index_name + '] '
+ SPACE(@STRINGLEN - LEN(index_name))
+ CASE
WHEN is_primary_key = 1
THEN ' PRIMARY KEY '
ELSE CASE
WHEN is_unique = 1
THEN ' UNIQUE '
ELSE ''
END
END
+ type_desc
+ CASE
WHEN type_desc='NONCLUSTERED'
THEN ''
ELSE ' '
END
+ ' (' + index_columns_key + ')'
+ CASE
WHEN index_columns_include <> '---'
THEN ' INCLUDE (' + index_columns_include + ')'
ELSE ''
END
+ CASE
WHEN fill_factor <> 0
THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor)
ELSE ''
END
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
ELSE ''
END
ELSE ''
END + ','
FROM @RESULTS
WHERE [type_desc] != 'HEAP'
AND is_primary_key = 1
OR is_unique = 1
ORDER BY
is_primary_key DESC,
is_unique DESC
The last "case when dataspace_name ...." statement was added in the select part.
--##############################################################################
--indexes
--##############################################################################
SELECT @INDEXSQLS = @INDEXSQLS
+ CASE
WHEN is_primary_key = 0 or is_unique = 0
THEN @vbCrLf
+ 'CREATE ' + type_desc + ' INDEX [' + index_name + '] '
+ SPACE(@STRINGLEN - LEN(index_name))
+ ' ON [' + [object_name] + ']'
+ ' (' + index_columns_key + ')'
+ CASE
WHEN index_columns_include <> '---'
THEN ' INCLUDE (' + index_columns_include + ')'
ELSE ''
END
+ CASE
WHEN fill_factor <> 0
THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor)
ELSE ''
END
+ CASE
WHEN dataspace_name <> '---'
THEN ' ON [' + dataspace_name + ']'
ELSE ''
END
END
FROM @RESULTS
WHERE [type_desc] != 'HEAP'
AND is_primary_key = 0
AND is_unique = 0
ORDER BY
is_primary_key DESC,
is_unique DESC
The "SELECT @DATASPACE_NAME .." statement was added
and the last line was changed.
The "WHERE type < 2" condition retuns either the filegroup of the clusterd index (if exists)
which is the physical sort order of the table or the filegroup of the heap entry which is the filegroup of the table.
--##############################################################################
--FINAL CLEANUP AND PRESENTATION
--##############################################################################
--at this point, there is a trailing comma, or it blank
SELECT @DATASPACE_NAME = dataspace_name FROM @Results
WHERE type < 2
SELECT
@FINALSQL = @FINALSQL
+ @CONSTRAINTSQLS
+ @CHECKCONSTSQLS
+ @FKSQLS
--note that this trims the trailing comma from the end of the statements
SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;
SET @FINALSQL = @FINALSQL + ') ON [' + @DATASPACE_NAME + ']' + @vbCrLf ;
I am pretty far with the testing now,
so I don't think that I will find anything else to improve.
As I said earlier this procedure saved me a lot of work.
Thanks again for sharing it.
Nikus