查询表索引

t1292582463

--修改表名为具体表名
CREATE TABLE #Table(name NVARCHAR(256))
INSERT INTO #Table
SELECT '表名1' UNION ALL
SELECT '表名2' UNION ALL
SELECT '表名3' UNION ALL
SELECT '表名4'

SELECT 'IF EXISTS(SELECT TOP 1 1 FROM sys.indexes WHERE name='''+t.name+''') DROP INDEX IX_esBase_Inspect_InfoPoint ON dbo.'+tob.name+'; CREATE NONCLUSTERED INDEX '+t.name+' ON dbo.'+tob.name+' ( '+tc.name+' ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];',   tob.name AS fldTableName, t.name AS fldIndex, tc.name AS fldIndexColumn,t.data_space_id
        FROM sys.indexes t
        INNER JOIN sys.objects  tob ON tob.object_id = t.object_id
        INNER JOIN #Table tab ON tab.name = tob.name
        OUTER APPLY(SELECT ISNULL((SELECT STUFF((SELECT ','+ name FROM (SELECT CAST(name AS NVARCHAR(256)) AS name FROM sys.columns a  INNER JOIN sys.index_columns tci ON a.column_id = tci.column_id AND a.object_id = tci.object_id WHERE tci.index_id = t.index_id AND tci.object_id = t.object_id )ty FOR XML PATH('')),1,1,'')),'') AS name) tc
        WHERE t.is_primary_key = 0 AND t.data_space_id = 1
        --WHERE t.data_space_id > 1

DROP TABLE #Table