自动列出在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)
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)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16131092/viewspace-2132105/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
