一键生成索引新语句
来源:互联网 发布:带着淘宝去古代 编辑:程序博客网 时间:2024/05/01 05:34
select d.name
,c.name
,a.name
,a.[type_desc] ,
'
begin try
if object_id('''+quotename(d.name)+'.'+quotename(c.name)+''') is not null and
not exists(select 1 from sys.indexes
where name ='''+a.name+''')
'+
(case
when a.is_primary_key=1
then
'
ALTER TABLE '+quotename(d.name)+'.'+quotename(c.name)+' ADD CONSTRAINT '+quotename(a.name)+' PRIMARY KEY '+a.[type_desc]+'
(
'+
stuff(
(
select ','+t2.name+' '+(case when t1.is_descending_key=1 then 'DESC' else 'ASC' end) from sys.index_columns t1
inner join sys.all_columns t2 on t1.object_id=a.object_id and t1.index_id=a.index_id
and t1.object_id=t2.object_id and t1.column_id=t2.column_id and t1.key_ordinal>0
order by t1.index_column_id
for xml path('')
),1,1,'')
+'
)
' collate Chinese_PRC_CI_AI_WS
when a.is_unique=1
then
'
ALTER TABLE '+quotename(d.name)+'.'+quotename(c.name)+' ADD CONSTRAINT '+quotename(a.name)+' UNIQUE '+
convert(varchar,a.[type_desc])+'
(
'+
stuff(
(
select ','+t2.name+' '+(case when t1.is_descending_key=1 then 'DESC' else 'ASC' end) from sys.index_columns t1
inner join sys.all_columns t2 on t1.object_id=a.object_id and t1.index_id=a.index_id
and t1.object_id=t2.object_id and t1.column_id=t2.column_id and t1.key_ordinal>0
order by t1.index_column_id
for xml path('')
),1,1,'')
+'
)
'
+
(case when exists
(select 1 from sys.index_columns t1
inner join sys.all_columns t2 on t1.object_id=a.object_id and t1.index_id=a.index_id
and t1.object_id=t2.object_id and t1.column_id=t2.column_id and t1.key_ordinal=0)
then 'INCLUDE('+
stuff(
(
select ','+t2.name from sys.index_columns t1
inner join sys.all_columns t2 on t1.object_id=a.object_id and t1.index_id=a.index_id
and t1.object_id=t2.object_id and t1.column_id=t2.column_id and t1.key_ordinal=0
order by t1.index_column_id
for xml path('')
),1,1,'')+')'
else '' end)
collate Chinese_PRC_CI_AI_WS
else
'
CREATE '+a.[type_desc]+' INDEX '+quotename(a.name)+' ON '+quotename(d.name)+'.'+quotename(c.name)+'
(
'+
stuff(
(
select ','+t2.name+' '+(case when t1.is_descending_key=1 then 'DESC' else 'ASC' end) from sys.index_columns t1
inner join sys.all_columns t2 on t1.object_id=a.object_id and t1.index_id=a.index_id
and t1.object_id=t2.object_id and t1.column_id=t2.column_id and t1.key_ordinal>0
order by t1.index_column_id
for xml path('')
),1,1,'')
+'
)
'
+
(case when exists
(select 1 from sys.index_columns t1
inner join sys.all_columns t2 on t1.object_id=a.object_id and t1.index_id=a.index_id
and t1.object_id=t2.object_id and t1.column_id=t2.column_id and t1.key_ordinal=0)
then 'INCLUDE('+
stuff(
(
select ','+t2.name from sys.index_columns t1
inner join sys.all_columns t2 on t1.object_id=a.object_id and t1.index_id=a.index_id
and t1.object_id=t2.object_id and t1.column_id=t2.column_id and t1.key_ordinal=0
order by t1.index_column_id
for xml path('')
),1,1,'')+')'
else '' end)
collate Chinese_PRC_CI_AI_WS
end) +'
end try
begin catch
print ERROR_MESSAGE()
end catch
' ad,
(CASE WHEN a.is_primary_key=1 or a.is_unique=1
THEN
'
begin try
ALTER TABLE '+quotename(d.name)+'.'+quotename(c.name)+' DROP CONSTRAINT '+quotename(a.name)+'
end try
begin catch
print ERROR_MESSAGE()
end catch
'
ELSE
'
begin try
if object_id('''+quotename(d.name)+'.'+quotename(c.name)+''') is not null and
exists(select 1 from sys.indexes
where name ='''+a.name+''')
drop index '+quotename(a.name)+' on '+quotename(d.name)+'.'+quotename(c.name)+'
end try
begin catch
print ERROR_MESSAGE()
end catch
'END) del
,a.* from sys.indexes a
inner join sys.all_objects c on a.object_id=c.object_id and c.type='u'
inner join sys.schemas d on c.schema_id=d.schema_id and a.type_desc in ('CLUSTERED','NONCLUSTERED')
--WHERE exists(select * from [master].[dbo].[UnionALL]
--where quotename(sch)+'.'+quotename(tbl)=quotename(d.name)+'.'+quotename(c.name)
-- and a.keys like '%'+col+'%')
--where exists
--(select * from sys.all_columns s1
--inner join sys.index_columns s2 on s1.object_id=s2.object_id and s1.column_id=s2.column_id and s1.object_id=a.object_id
--and exists(select * from [master].[dbo].[UnionALL] where db=db_name()
--and tbl=c.name and sch=d.name and col=s1.name))
- 一键生成索引新语句
- 解读SQL Server2008的新语句MERGE
- 解析SQL Server 2008中的新语句:MERGE
- Python编译器实现内幕:添加一个新语句
- 一键生成库里索引脚本
- 一句又一句
- oracle一句sql生成10w行测试数据
- 网络新语
- 劝学新语
- 新语录!
- 劝学新语
- pl/sql 对一存在的表生成建表语句
- 忽然想起一句
- 一句"shit happens"
- 留言一句
- 一句不错的话
- 狗尾续貂一句
- 一句受用的话
- Struts2架构图
- js函数数组
- 【转载】Javascript继承机制的设计思想
- LR性能测试结果样例分析
- 每日一得--解决tomcat 5.5 不能识别 JSP的EL表达,但tomcat 7.0 可以的问题
- 一键生成索引新语句
- 六分钟学会创建Oracle表空间的步骤
- 评论:26岁成都唐爽发现成果惊动奥巴马--意外发现新材料极可能成下一代电脑芯片
- 选择条件语句_if结构
- 追MM与Java的23种设计模式
- 选择条件语句if_else结构
- 多重if结构
- 基于EPOLL+多进程+线程池的server框架设想
- vs2010 安装mvc3