自动列出在tmstamp上所缺失索引的创建语句

千家信息网最后更新 2025年01月31日自动列出在tmstamp上所缺失索引的创建语句SELECT o.name,user_name(o.uid),'ALTER TABLE [dbo].'+'['+object_name(co.id) /*tbname*/+']'+' ADD CONSTRAINT [TM_UNIQUE_'+''+upper(object_name(co.id))+'] UNIQUE NONCLUSTERED ('+upper(co.name) /*colname*/+' ASC)'
FROM syscolumns co
inner join sysobjects o
on co.id=o.id and o.xtype='U' and o.name<>'dtproperties'
WHERE co.name='TMSTAMP' and user_name(o.uid)='dbo' --order by o.name
and object_name(co.id) not in (
select tab.Name as Tab_Name
from sys.indexes ind
inner join sys.tables tab on ind.Object_id = tab.object_id and ind.type in (1,2)
inner join sys.index_columns index_columns on tab.object_id = index_columns.object_id and ind.index_id = index_columns.index_id
inner join sys.columns Col on tab.object_id = Col.object_id and index_columns.column_id = Col.column_id
left join sysobjects o on tab.name=o.name and o.type='U'
where Col.Name='TMSTAMP' and user_name(o.uid)='dbo' --order by tab.name
) order by object_name(co.id)