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