Posted By

g8rpal on 09/30/10


Tagged

indexes


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

bobbym245
Tyster


Script all Indexes


 / Published in: SQL
 

URL: http://www.sqlservercentral.com/scripts/Indexing/70737/

This script will generate CREATE Index statements for all indexes in the database

  1. -- Script out indexes completely, including both PK's and regular indexes, each clustered or nonclustered.
  2. -- DOES NOT HANDLE COMPRESSION; that's ok, since 2008 R2 RTM benchmarking shows it's faster and results in smaller indexes to insert uncompressed and then compress later
  3. -- HARDCODES [dbo] schema (i.e. it doesn't say [JohnDoe].[table], changing that to [dbo].[table]
  4. -- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx
  5.  
  6. DECLARE
  7. @idxTableName SYSNAME,
  8. @idxTableID INT,
  9. @idxname SYSNAME,
  10. @idxid INT,
  11. @colCount INT,
  12. @IxColumn SYSNAME,
  13. @IxFirstColumn BIT,
  14. @ColumnIDInTable INT,
  15. @ColumnIDInIndex INT,
  16. @IsIncludedColumn INT,
  17. @sIncludeCols VARCHAR(MAX),
  18. @sIndexCols VARCHAR(MAX),
  19. @sSQL VARCHAR(MAX),
  20. @sParamSQL VARCHAR(MAX),
  21. @sFilterSQL VARCHAR(MAX),
  22. @location SYSNAME,
  23. @IndexCount INT,
  24. @CurrentIndex INT,
  25. @CurrentCol INT,
  26. @Name VARCHAR(128),
  27. @IsPrimaryKey TINYINT,
  28. @Fillfactor INT,
  29. @FilterDefinition VARCHAR(MAX),
  30. @IsClustered BIT -- used solely for putting information into the result table
  31.  
  32.  
  33. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]'))
  34. DROP TABLE [dbo].[#IndexSQL]
  35.  
  36. CREATE TABLE #IndexSQL
  37. ( TableName VARCHAR(128) NOT NULL
  38. ,IndexName VARCHAR(128) NOT NULL
  39. ,IsClustered BIT NOT NULL
  40. ,IsPrimaryKey BIT NOT NULL
  41. ,IndexCreateSQL VARCHAR(max) NOT NULL
  42. )
  43.  
  44. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]'))
  45. DROP TABLE [dbo].[#IndexListing]
  46.  
  47. CREATE TABLE #IndexListing
  48. (
  49. [IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  50. [TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  51. [ObjectID] INT NOT NULL,
  52. [IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  53. [IndexID] INT NOT NULL,
  54. [IsPrimaryKey] TINYINT NOT NULL,
  55. [FillFactor] INT,
  56. [FilterDefinition] NVARCHAR(MAX) NULL
  57. )
  58.  
  59. IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]'))
  60. DROP TABLE [dbo].[#ColumnListing]
  61.  
  62. CREATE TABLE #ColumnListing
  63. (
  64. [ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
  65. [ColumnIDInTable] INT NOT NULL,
  66. [Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  67. [ColumnIDInIndex] INT NOT NULL,
  68. [IsIncludedColumn] BIT NULL
  69. )
  70.  
  71. INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )
  72. SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, si.filter_definition
  73. FROM sys.indexes si
  74. LEFT OUTER JOIN information_schema.table_constraints tc ON si.name = tc.constraint_name AND OBJECT_NAME(si.object_id) = tc.table_name
  75. WHERE OBJECTPROPERTY(si.object_id, 'IsUserTable') = 1
  76. ORDER BY OBJECT_NAME(si.object_id), si.index_id
  77.  
  78. SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1
  79.  
  80. WHILE @CurrentIndex <= @IndexCount
  81. BEGIN
  82.  
  83. SELECT @idxTableName = [TableName],
  84. @idxTableID = [ObjectID],
  85. @idxname = [IndexName],
  86. @idxid = [IndexID],
  87. @IsPrimaryKey = [IsPrimaryKey],
  88. @FillFactor = [FILLFACTOR],
  89. @FilterDefinition = [FilterDefinition]
  90. FROM #IndexListing
  91. WHERE [IndexListingID] = @CurrentIndex
  92.  
  93. -- So - it is either an index or a constraint
  94. -- Check if the index is unique
  95. IF (@IsPrimaryKey = 1)
  96. BEGIN
  97. SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY '
  98. -- Check if the index is clustered
  99. IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0)
  100. BEGIN
  101. SET @sSQL = @sSQL + 'NON'
  102. SET @IsClustered = 0
  103. END
  104. ELSE
  105. BEGIN
  106. SET @IsClustered = 1
  107. END
  108. SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)
  109. END
  110. ELSE
  111. BEGIN
  112. SET @sSQL = 'CREATE '
  113. -- Check if the index is unique
  114. IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)
  115. BEGIN
  116. SET @sSQL = @sSQL + 'UNIQUE '
  117. END
  118. -- Check if the index is clustered
  119. IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)
  120. BEGIN
  121. SET @sSQL = @sSQL + 'CLUSTERED '
  122. SET @IsClustered = 1
  123. END
  124. ELSE
  125. BEGIN
  126. SET @IsClustered = 0
  127. END
  128.  
  129. SELECT
  130. @sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13),
  131. @colCount = 0
  132. END
  133.  
  134. -- Get the number of cols in the index
  135. SELECT @colCount = COUNT(*)
  136. FROM sys.index_columns ic
  137. INNER JOIN sys.COLUMNS sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
  138. WHERE ic.object_id = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0
  139.  
  140. -- Get the file group info
  141. SELECT @location = f.[name]
  142. FROM sys.indexes i
  143. INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
  144. INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
  145. WHERE o.object_id = @idxTableID AND i.index_id = @idxid
  146.  
  147. -- Get all columns of the index
  148. INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )
  149. SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column
  150. FROM sys.index_columns ic
  151. INNER JOIN sys.COLUMNS sc ON ic.object_id = sc.object_id AND ic.column_id = sc.column_id
  152. WHERE ic.object_id = @idxTableID AND index_id = @idxid
  153. ORDER BY ic.index_column_id
  154.  
  155. IF @@ROWCOUNT > 0
  156. BEGIN
  157.  
  158. SELECT @CurrentCol = 1
  159.  
  160. SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''
  161.  
  162. WHILE @CurrentCol <= @ColCount
  163. BEGIN
  164. SELECT @ColumnIDInTable = ColumnIDInTable,
  165. @Name = Name,
  166. @ColumnIDInIndex = ColumnIDInIndex,
  167. @IsIncludedColumn = IsIncludedColumn
  168. FROM #ColumnListing
  169. WHERE [ColumnListingID] = @CurrentCol
  170.  
  171. IF @IsIncludedColumn = 0
  172. BEGIN
  173.  
  174. SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '
  175.  
  176. -- Check the sort order of the index cols ????????
  177. IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0
  178. BEGIN
  179. SET @sIndexCols = @sIndexCols + ' ASC '
  180. END
  181. ELSE
  182. BEGIN
  183. SET @sIndexCols = @sIndexCols + ' DESC '
  184. END
  185.  
  186. IF @CurrentCol < @colCount
  187. BEGIN
  188. SET @sIndexCols = @sIndexCols + ', '
  189. END
  190.  
  191. END
  192. ELSE
  193. BEGIN
  194. -- Check for any include columns
  195. IF LEN(@sIncludeCols) > 0
  196. BEGIN
  197. SET @sIncludeCols = @sIncludeCols + ','
  198. END
  199.  
  200. SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'
  201.  
  202. END
  203.  
  204. SET @CurrentCol = @CurrentCol + 1
  205. END
  206.  
  207. TRUNCATE TABLE #ColumnListing
  208.  
  209. --append to the result
  210. IF LEN(@sIncludeCols) > 0
  211. SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '
  212. ELSE
  213. SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '
  214.  
  215. -- Add filtering
  216. IF @FilterDefinition IS NOT NULL
  217. SET @sFilterSQL = ' WHERE ' + @FilterDefinition + ' ' + CHAR(13)
  218. ELSE
  219. SET @sFilterSQL = ''
  220.  
  221. -- Build the options
  222. SET @sParamSQL = 'WITH ( PAD_INDEX = '
  223.  
  224. IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1
  225. SET @sParamSQL = @sParamSQL + 'ON,'
  226. ELSE
  227. SET @sParamSQL = @sParamSQL + 'OFF,'
  228.  
  229. SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = '
  230.  
  231.  
  232. IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0
  233. SET @sParamSQL = @sParamSQL + 'ON,'
  234. ELSE
  235. SET @sParamSQL = @sParamSQL + 'OFF,'
  236.  
  237. SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = '
  238.  
  239. IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0
  240. SET @sParamSQL = @sParamSQL + 'ON,'
  241. ELSE
  242. SET @sParamSQL = @sParamSQL + 'OFF,'
  243.  
  244.  
  245. SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '
  246.  
  247. -- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.
  248. IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)
  249. SET @sParamSQL = @sParamSQL + 'ON'
  250. ELSE
  251. SET @sParamSQL = @sParamSQL + 'OFF'
  252.  
  253. -- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2
  254. IF ISNULL( @FillFactor, 90 ) <> 0
  255. SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )
  256.  
  257.  
  258. IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK's
  259. BEGIN
  260. SET @sParamSQL = @sParamSQL + ' ) '
  261. END
  262. ELSE
  263. BEGIN
  264. SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) '
  265. END
  266.  
  267. SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL
  268.  
  269. -- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement
  270. SET @sSQL = @sSQL + ' ON [' + @location + ']'
  271.  
  272. --PRINT @sIndexCols + CHAR(13)
  273. INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL)
  274.  
  275. END
  276.  
  277. SET @CurrentIndex = @CurrentIndex + 1
  278. END
  279.  
  280. SELECT * FROM #IndexSQL

Report this snippet  

You need to login to post a comment.