生成索引的语句
来源:互联网 发布:玩游戏学编程app 编辑:程序博客网 时间:2024/05/16 19:50
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)
,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')
- 生成索引的语句
- 新的索引生成语句
- 生成指定表rebuild所有索引的语句
- 通过数据泵生成索引创建语句
- 提取创建索引的语句
- mysql-影响索引的语句
- MySQL 创建索引、修改索引、删除索引的命令语句
- oracle数据库,通过sql生成sql,自动生成表分析语句和索引分析语句
- mysql 创建索引,删除索引的sql语句 【转】
- Oracle 建立索引及利用索引的SQL语句优化
- Oracle 建立索引及利用索引的SQL语句优化
- mysql索引学习----2----创建索引、修改索引、删除索引的命令语句
- 生成日历的SQL语句
- 生成日历的SQL语句
- 日程安排生成的sql语句
- MyBatis自动生成的语句
- 生成数据表的SQL 语句
- MSSQL维护索引语句的性能比较
- hadoop常见问题汇总(一)
- 自然摄影指南——第一章:曝光:自动曝光
- sql语句一条记录拆分为多条记录
- 自然摄影指南——第一章:曝光:多重曝光
- IT界名人:Zynga CEO不关注股价涨跌 目标是长期价值
- 生成索引的语句
- 企业为什么会陷入财务危机?财务危机案例启示录——china-pub网上书店
- 自然摄影指南——第一章:曝光:长时间曝光
- 南海争端
- ASP.NET MVC——螺旋进步的产物
- stax解析xml时, 使用栈来处理,不错的代码
- undo表空间及与redo日志的区别
- Struts2 Model-Driven
- 浅谈内部控制在企业管理信息化中的应用