WITH CTE AS
(
SELECT OBJECT_NAME(a.id) as tablename,a.name,b.indid,b.colid,b.keyno from  syscolumns a inner join sysindexkeys b on a.id=b.id and a.colid=b.colid 
where  OBJECT_NAME(a.ID)='tablename' 
)
--SELECT DISTINCT CASE T.indid WHEN 1 THEN 
--'CREATE CLUSTERED INDEX IX_' ELSE 'CREATE INDEX IX_' END,tablename,'_',
--(SELECT NAME FROM CTE AS C WHERE C.indid=T.indid AND KEYNO=1 AND C.tablename=T.tablename),
--' ON(',
--LEFT((SELECT NAME+',' FROM CTE AS C WHERE  C.indid=T.indid AND KEYNO<>0 AND C.tablename=T.tablename ORDER BY KEYNO ASC FOR XML PATH('')),LEN((SELECT NAME+',' FROM CTE AS C WHERE  C.indid=T.indid AND KEYNO<>0 AND C.tablename=T.tablename ORDER BY KEYNO ASC FOR XML PATH('')))-1),
--CASE WHEN EXISTS(SELECT 1 FROM CTE I WHERE I.tablename=T.tablename AND I.indid=T.indid AND I.keyno=0) THEN 1 ELSE 0 END,
--') INCLUDE(',
--LEFT((SELECT NAME+',' FROM CTE I WHERE I.tablename=T.tablename AND I.indid=T.indid AND keyno=0 FOR XML PATH('')),LEN((SELECT NAME+',' FROM CTE I WHERE I.tablename=T.tablename AND I.indid=T.indid AND keyno=0 FOR XML PATH('')))-1),')' 
--FROM CTE AS T
SELECT DISTINCT CASE T.indid WHEN 1 THEN 
'CREATE CLUSTERED INDEX IX_' ELSE 'CREATE INDEX IX_' END+tablename+'_'+
(SELECT NAME FROM CTE AS C WHERE C.indid=T.indid AND KEYNO=1 AND C.tablename=T.tablename)+
' ON '+tablename+'('+
LEFT((SELECT NAME+',' FROM CTE AS C WHERE  C.indid=T.indid AND KEYNO<>0 AND C.tablename=T.tablename ORDER BY KEYNO ASC FOR XML PATH('')),LEN((SELECT NAME+',' FROM CTE AS C WHERE  C.indid=T.indid AND KEYNO<>0 AND C.tablename=T.tablename ORDER BY KEYNO ASC FOR XML PATH('')))-1)+') '+
CASE WHEN EXISTS(SELECT 1 FROM CTE I WHERE I.tablename=T.tablename AND I.indid=T.indid AND I.keyno=0) THEN 'INCLUDE('+
LEFT((SELECT NAME+',' FROM CTE I WHERE I.tablename=T.tablename AND I.indid=T.indid AND keyno=0 FOR XML PATH('')),LEN((SELECT NAME+',' FROM CTE I WHERE I.tablename=T.tablename AND I.indid=T.indid AND keyno=0 FOR XML PATH('')))-1)+')' ELSE '' END
FROM CTE AS T 
![](http://i2.51cto.com/images/blog/201805/17/30feaeb720dcaf8066339f1f1d9ea5fe.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_30,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=)