December 7, 2012 at 3:57 am
Hi Guys,
I am trying to write script which will generate the create script for all the indexes,key,constraints.
Below is the script
select 'alter table '+SCHEMA_NAME(o.schema_id)+'.'+o.name+' add constraint '+ i.name + ' Primary key clustered '+'('+
c.name +')' from sys.indexes i join sys.objects o
on i.object_id=o.object_id join sys.index_columns ic
on i.index_id=ic.index_id and o.object_id=ic.object_id join sys.columns c
on c.column_id=ic.column_id and c.object_id=o.object_id
where o.type='u'
Now the problem is if the index created on multiple columns how should I
modify it in above query.
December 7, 2012 at 4:03 am
Interesting. Something like this maybe: -
SELECT 'CREATE '+CASE WHEN IndexProperty(TableId, IndexName, 'IsUnique') = 1 THEN 'UNIQUE '
ELSE '' END+CASE WHEN IndexProperty(TableId, IndexName, 'IsClustered') = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END
+'INDEX ['+IndexName+'] ON ['+SchemaName+'].['+TableName+']'+' ('+
(SELECT STUFF((SELECT ','+colName + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM (SELECT IC.OBJECT_ID, IC.Index_ID, IC.key_ordinal,
SC.[Name] AS colName, IC.[is_included_column], IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC ON IC.OBJECT_ID = SC.OBJECT_ID AND IC.Column_ID = SC.Column_ID
WHERE is_included_column = 0
)cols
WHERE OBJECT_ID = TableId AND Index_ID = IndexId
ORDER BY key_ordinal
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')
)+')'+ ISNULL((SELECT ' INCLUDE ( ' + STUFF((SELECT ','+colName
FROM (SELECT IC.OBJECT_ID, IC.Index_ID, IC.key_ordinal,
SC.[Name] AS colName, IC.[is_included_column], IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC ON IC.OBJECT_ID = SC.OBJECT_ID AND IC.Column_ID = SC.Column_ID
WHERE is_included_column = 1
)cols
WHERE OBJECT_ID = TableId AND Index_ID = IndexId
ORDER BY key_ordinal
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),1,1,'')+')'
),'')+CASE WHEN Fill_Factor <> 0 THEN 'WITH (FillFactor = ' + CAST(Fill_Factor AS VARCHAR(3)) + ')' ELSE '' END
+CASE WHEN FileGroupName <> '' AND FileGroupName IS NOT NULL THEN ' ON ['+ FileGroupName + ']' ELSE '' END
+CHAR(13)+CHAR(10)+'END'+CHAR(13)+CHAR(10) AS sqlStmt
FROM (SELECT SC.NAME AS SchemaName, SO.NAME AS TableName, SI.OBJECT_ID AS TableId,
SI.[Name] AS IndexName, SI.Index_ID AS IndexId, FG.[Name] AS FileGroupName,
SI.Fill_Factor
FROM sys.indexes SI
LEFT OUTER JOIN sys.filegroups FG ON SI.data_space_id = FG.data_space_id
INNER JOIN sys.objects SO ON SI.OBJECT_ID = SO.OBJECT_ID
INNER JOIN sys.schemas SC ON SC.schema_id = SO.schema_id
WHERE OBJECTPROPERTY(SI.OBJECT_ID, 'IsUserTable') = 1 AND SI.[Name] IS NOT NULL AND
SI.is_primary_key = 0 AND
INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], 'IsStatistics') = 0
)a;
December 7, 2012 at 4:07 am
See
http://www.sqlservercentral.com/scripts/Indexing/70737/
You could probably add an xpath clause ot your query to concatenate the column names but depends on how far you want to go with this.
Cursors never.
DTS - only when needed and never to control.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply