by
0
6
4,963
3
Top 1% !
Popular
Famous
Tagged
Refined
OpenSource
Popularity: 1695th place
Architecturex86_64
DBMSMS SQL Server
Extensionsql
LanguageSQL
LicenseMIT_X11
OSWindows

Recreate the index structure of an MS SQL Server DB in T-SQL

This script prepares and executes several dynamic SQL statements to recreate indexes exactly as they are built on the original DB
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/206181/Recreate-the-index-structure-of-an-MS-SQ?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
USE Snip GO DECLARE @indexName nvarchar(70), @tableName nvarchar(100), @sql nvarchar(4000) = '', @colName nvarchar(100), @desc bit, @inclCol bit, @prevInclCol bit,@unique bit, @typeDesc nvarchar(40), @previousIndexName nvarchar(70) = '', @filegroup nvarchar(100) = '[PRIMARY]', @fgName nvarchar(100), @prevFGName nvarchar(100) DECLARE fk CURSOR FOR SELECT TableName = t.name, IndexName = ind.name, ColumnName = col.name, FGName = '[' + f.name + ']', ic.is_descending_key, ic.is_included_column, ind.is_unique, ind.type_desc FROM sys.indexes ind INNER JOIN sys.index_columns ic ON ind.object_id = ic.object_id and ind.index_id = ic.index_id INNER JOIN sys.columns col ON ic.object_id = col.object_id and ic.column_id = col.column_id INNER JOIN sys.tables t ON ind.object_id = t.object_id INNER JOIN sys.[filegroups] f ON f.[data_space_id] = ind.data_space_id WHERE ind.is_unique_constraint = 0 AND t.is_ms_shipped = 0 ORDER BY t.name, ind.name, ind.index_id, ic.index_column_id OPEN fk FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc WHILE @@fetch_status = 0 BEGIN IF (@prevFGName = '') SET @prevFGName = @fgName; print 'prev=' + @previousIndexName + ';new=' + @indexName + ';tbl=' + @tableName + ';col=' + @colName + ';desc=' + CAST(@desc as nvarchar(10)) + ';fgName=' + @fgName + ';filegroup=' + @filegroup + ';include=' + CAST(@inclCol as nvarchar(10)) + ';unique=' + CAST(@unique as nvarchar(10)) + ';type=' + @typeDesc IF (@previousIndexName = @indexName) BEGIN --continue with the previous index: if (@inclCol = 1 AND @prevInclCol = 0) SET @sql += ') INCLUDE ( ' + @colName; ELSE SET @sql += ',' + @colName; SET @prevInclCol = @inclCol; print 'sql in equal=' + @sql END ELSE BEGIN --complete and exec the last statement: SET @prevInclCol = 0; IF (LEN(@sql) != 0) BEGIN IF (@prevFGName != @filegroup) BEGIN SET @sql += ') ON ' + @filegroup + ';'; --SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';'; print 'sql to exec=' + @sql EXEC( @sql); END END --create a new statement: SET @sql = ' CREATE '; IF (@unique = 1) SET @sql += ' UNIQUE '; SET @sql += @typeDesc + ' INDEX ' + @indexName + '_1 ON dbo.' + @tableName + ' (' + @colName; print 'sql created=' + @sql END IF (@inclCol = 0) BEGIN IF (@desc = 1) SET @sql += ' DESC '; ELSE SET @sql += ' ASC '; END SELECT @previousIndexName = @indexName, @prevFGName = @fgName FETCH NEXT FROM fk INTO @tableName, @indexName, @colName, @fgName, @desc, @inclCol, @unique, @typeDesc END CLOSE fk DEALLOCATE fk --complete and exec the last statement: IF (LEN(@sql) != 0) BEGIN IF (@prevFGName != @filegroup) BEGIN SET @sql += ') ON ' + @filegroup + ';'; --SET @sql += ') WITH (DROP_EXISTING = ON) ON ' + @filegroup + ';'; print 'sql to exec=' + @sql EXEC( @sql); END END
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus