April 22, 2011 at 12:11 pm
Wow this script has really come a long way - even better than the last time I looked. I'm gonna need to compare to a different script I have with similar functionality now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 24, 2011 at 8:57 am
May be you will get your answer on http://ektaraval.blogspot.com/2010/07/query-to-generate-create-index.html
May 24, 2011 at 9:20 am
patelekta (5/24/2011)
ekta your example only works if an index has one and only one column in it's definition...otherwise it scripts two definitions for the same index, but with different columns.
for example, compare the results if a single index from the above script to yours:
You'll want to enhance your version, maybe use the FOR XML to append all the columns together for the definition to really make yours valid for all indexes.
IF NOT EXISTS(SELECT OBJECT_ID(IX_GMATDET_HELP)
CREATE INDEX [IX_GMATDET_HELP] ON [dbo].[GMATDET] (
ACTTRANSDETTBLKEY ASC,
SOURCETBLKEY ASC,
YEARTBLKEY ASC,
SETASDTBLKEY ASC,
SUBGRANTTBLKEY ASC,
TRANSACTIONAMT ASC,
RECEIPT1AMT ASC,
RECEIPT2AMT ASC,
RECEIPT3AMT ASC,
ADJUSTMENT ASC) WITH (ONLINE = ON)
--your script is generating the same indexname for each column in the definition,
-- instead of one index featuring all columns.
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [ACTTRANSDETTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SOURCETBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [YEARTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SETASDTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SUBGRANTTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [TRANSACTIONAMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT1AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT2AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT3AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [ADJUSTMENT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
Lowell
March 13, 2012 at 4:49 pm
Code fix
It's already been commented on once, but the code has not been modified
--ORDER BY SYSCOLUMNS.COLID - This is the original code -- it will create the composite indexes with the order of columns WRONG Tested on SQL 2000 SQL 2008
ORDER BY SYSINDEXKEYS.keyno - It should be
Still a great script, saved me time, another example of test - test - test before you run a downloaded script.
March 14, 2012 at 6:30 am
Paul take a look at this post here in the Discussions thread;
http://www.sqlservercentral.com/Forums/FindPost1079779.aspx
That post ( a few posts above this) has a much more modern scripting style that correctly takes into consideration the column order on the index.
the original script was contributed when SQL 2000 was the target database version...it's come a long way since then
Lowell
May 22, 2012 at 9:07 pm
That was really helpfull.
I was trying to execute on SQL Server 2008 R2 and bumped into an issue with the Collation parameters with the "keycolumns ".
To work around, I added COLLATE DATABASE_DEFAULT for keycolumns in the sections that generates the CREATE INDEX scripts.
February 21, 2014 at 9:57 am
SELECT ' CREATE ' +
CASE
WHEN I.is_unique = 1 THEN ' UNIQUE '
ELSE ''
END +
I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
I.name + ' ON ' +
SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
KeyColumns + ' ) ' +
ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
ISNULL(' WHERE ' + I.filter_definition, '') + ' WITH ( ' +
CASE
WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
ELSE ' PAD_INDEX = OFF '
END + ',' +
'FILLFACTOR = ' + CONVERT(
CHAR(5),
CASE
WHEN I.fill_factor = 0 THEN 100
ELSE I.fill_factor
END
) + ',' +
-- default value
'SORT_IN_TEMPDB = OFF ' + ',' +
CASE
WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
ELSE ' IGNORE_DUP_KEY = OFF '
END + ',' +
CASE
WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
ELSE ' STATISTICS_NORECOMPUTE = ON '
END + ',' +
' ONLINE = OFF ' + ',' +
CASE
WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
ELSE ' ALLOW_ROW_LOCKS = OFF '
END + ',' +
CASE
WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
ELSE ' ALLOW_PAGE_LOCKS = OFF '
END + ' ) ON [' +
DS.name + ' ] ' + CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM sys.indexes I
JOIN sys.tables T
ON T.object_id = I.object_id
JOIN sys.sysindexes SI
ON I.object_id = SI.id
AND I.index_id = SI.indid
JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name + CASE
WHEN MAX(CONVERT(INT, IC1.is_descending_key))
= 1 THEN
' DESC '
ELSE
' ASC '
END
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
0
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
ORDER BY
MAX(IC1.key_ordinal)
FOR XML PATH('')
),
1,
2,
''
) KeyColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp3
)tmp4
ON I.object_id = tmp4.object_id
AND I.Index_id = tmp4.index_id
JOIN sys.stats ST
ON ST.object_id = I.object_id
AND ST.stats_id = I.index_id
JOIN sys.data_spaces DS
ON I.data_space_id = DS.data_space_id
JOIN sys.filegroups FG
ON I.data_space_id = FG.data_space_id
LEFT JOIN (
SELECT *
FROM (
SELECT IC2.object_id,
IC2.index_id,
STUFF(
(
SELECT ' , ' + C.name
FROM sys.index_columns IC1
JOIN sys.columns C
ON C.object_id = IC1.object_id
AND C.column_id = IC1.column_id
AND IC1.is_included_column =
1
WHERE IC1.object_id = IC2.object_id
AND IC1.index_id = IC2.index_id
GROUP BY
IC1.object_id,
C.name,
index_id
FOR XML PATH('')
),
1,
2,
''
) IncludedColumns
FROM sys.index_columns IC2
--WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
GROUP BY
IC2.object_id,
IC2.index_id
) tmp1
WHERE IncludedColumns IS NOT NULL
) tmp2
ON tmp2.object_id = I.object_id
AND tmp2.index_id = I.index_id
WHERE I.is_primary_key = 0
AND I.is_unique_constraint = 0
--AND I.Object_id = object_id('Person.Address') --Comment for all tables
--AND I.name = 'IX_Address_PostalCode' --comment for all indexes
October 23, 2015 at 4:15 am
Let me add my version too. No cursors in the implementation. Many (but not all) of the latest SQL server versions' index options. Both indexes and primary keys are scripted. Plus, it also generates statements to drop the indexes and/or primary keys on the selected tables.
Here it is. Please let me know if you notice any oversights.
with cte as (
select
tbl.object_id,
ix.index_id,
ix.is_primary_key,
x.create_statement,
x.drop_statement
from sys.tables tbl
inner join sys.indexes ix on (ix.object_id = tbl.object_id)
left outer join sys.key_constraints kc on (ix.is_primary_key = 1 and kc.parent_object_id = ix.object_id and kc.unique_index_id = ix.index_id and kc.type = 'PK')
left outer join sys.data_spaces ds on (ds.data_space_id = ix.data_space_id)
left outer join sys.stats st on (st.object_id = ix.object_id and st.stats_id = 1)
cross apply (
select N'create'
+ case ix.is_unique when 1 then ' unique' else '' end
+ case ix.type when 1 then ' clustered' else '' end
+ ' index ' + quotename(ix.name)
+ ' on ' + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))
+ '('
+ stuff((
select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]
from sys.index_columns c
where c.object_id = ix.object_id
and c.index_id = ix.index_id
and c.is_included_column = 0
order by c.key_ordinal,
c.index_column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '')
+ ')'
+ isnull( ' include (' +
+ stuff((
select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]
from sys.index_columns c
where c.object_id = ix.object_id
and c.index_id = ix.index_id
and c.is_included_column = 1
order by c.key_ordinal,
c.index_column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '')
+ ')', '')
+ case ix.has_filter when 1
then ' where ' + ix.filter_definition
else ''
end
+ isnull( ' with ('
+ stuff((
select ', ' + w.txt as [text()]
from (
select 'FILLFACTOR = ' + convert(nvarchar(36), ix.fill_factor) as txt where not ix.fill_factor = 0
union all select 'PAD_INDEX = ON' where not ix.is_padded = 0
union all select 'IGNORE_DUP_KEY = ON' where not ix.ignore_dup_key = 0
--SORT_IN_TEMPDB = ON
union all select 'STATISTICS_NORECOMPUTE = ON' where not isnull(st.no_recompute, 0) = 0
--DROP_EXISTING = ON
--ONLINE = ON
union all select 'ALLOW_ROW_LOCKS = ON' where not ix.allow_row_locks = 0
union all select 'ALLOW_PAGE_LOCKS = ON' where not ix.allow_page_locks = 0
--MAXDOP =
--DATA_COMPRESSION = NONE | ROW | PAGE
) w
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '') + ')'
,''
)
+ isnull(case ds.is_default when 0 then ' on ' + quotename(ds.name) end, '') as create_statement,
N'drop index ' + quotename(ix.name) + ' '
+ 'on ' + quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id)) as drop_statement
where not ix.is_primary_key = 1
union all
select N'alter table '
+ quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))
+ ' add'
+ case kc.is_system_named when 1 then '' else ' constraint ' + quotename(kc.name) end
+ ' primary key '
+ case ix.type when 1 then 'clustered ' else '' end
+ '('
+ stuff((
select ', ' + quotename(col_name(c.object_id, c.column_id)) + case c.is_descending_key when 1 then ' desc' else '' end as [text()]
from sys.index_columns c
where c.object_id = ix.object_id
and c.index_id = ix.index_id
and c.is_included_column = 0
order by c.key_ordinal,
c.index_column_id
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '')
+ ')'
+ isnull( ' with ('
+ stuff((
select ', ' + w.txt as [text()]
from (
select 'FILLFACTOR = ' + convert(nvarchar(36), ix.fill_factor) as txt where not ix.fill_factor = 0
union all select 'PAD_INDEX = ON' where not ix.is_padded = 0
union all select 'IGNORE_DUP_KEY = ON' where not ix.ignore_dup_key = 0
) w
for xml path(''), type
).value('.','nvarchar(max)'), 1, 2, '') + ')'
,''
)
+ isnull(case ds.is_default when 0 then ' on ' + quotename(ds.name) end, '') as create_statement,
N'alter table '
+ quotename(object_schema_name(ix.object_id)) + '.' + quotename(object_name(ix.object_id))
+ ' drop constraint ' + quotename(kc.name) as drop_statement
where ix.is_primary_key = 1
) x
where not tbl.is_ms_shipped = 1
and not ix.type = 0
)
select x.stmt + ';
go' as [-- script to drop and recreate all existing primary keys and indexes in the database]
from cte ix
cross apply (
select 1 as stage, ix.drop_statement as stmt
union all select 2 stage, ix.create_statement as stmt
) x
--where ix.object_id = object_id('dbo.Invoices')
order by x.stage,
object_schema_name(ix.object_id),
object_name(ix.object_id),
ix.index_id * case x.stage when 1 then -1 else 1 end
Edit: Fixed an incorrect join to sys.stats. Thanks to ChrisM@Work for notifying me.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply