新的索引生成语句
来源:互联网 发布:java语言编程手机游戏 编辑:程序博客网 时间:2024/05/19 19:58
select d.name
,c.name
,a.name
,a.[type_desc] ,
(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) 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))
- 新的索引生成语句
- 生成索引的语句
- 生成指定表rebuild所有索引的语句
- C# 利用sql语句生成新的数据表
- C# 利用sql语句生成新的数据表
- 通过数据泵生成索引创建语句
- mysql sql语句合并生成新表
- 提取创建索引的语句
- mysql-影响索引的语句
- SQL Server复制表结构和表数据生成新表的语句
- MySQL 创建索引、修改索引、删除索引的命令语句
- SQL2012 重新组织和新生成索引sp_RefreshIndex
- break语句/Continue语句的新认识
- break语句/Continue语句的新认识
- oracle数据库,通过sql生成sql,自动生成表分析语句和索引分析语句
- VBA 生成新的表格
- stringByReplacingOccurrencesOfString 生成新的NSString
- 诊断索引健全性的新工具
- Struts2 自定义Result类型
- 自己写的C语言简单万年历
- AndroidManifest.xml文件详解(permission-tree)
- ssh无密码登陆
- 每个程序员都应该知道的福利
- 新的索引生成语句
- java如何遍历List里面的每个元素
- ASP.NET用户控件操作ASPX页面(在ASPX页面捕捉用户控件的事件)
- TS流的解码过程-ES-PES-DTS-PTS-PCR
- python笔记[更新中]
- jm像素(当前帧-前一帧)
- 架构设计:生产者/消费者模式[0]:概述
- 2011中国民营企业500强名单,广东仅13家江浙310家
- IIS7 配置端口后不能访问