开发者社区> 杰克.陈> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句

简介: 原文:SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句 BEGIN WITH tx AS ( SELECT a.
+关注继续查看
原文:SQL SERVER 2005 获取表的所有索引信息以及删除和新建语句

BEGIN
        WITH tx AS
        (
                SELECT a.object_id
                      ,b.name AS schema_name
                      ,a.name AS table_name
                      ,c.name as ix_name
                      ,c.is_unique AS ix_unique
                      ,c.type_desc AS ix_type_desc
                      ,d.index_column_id
                      ,d.is_included_column
                      ,e.name AS column_name
                      ,f.name AS fg_name
                      ,d.is_descending_key AS is_descending_key
                      ,c.is_primary_key
                      ,c.is_unique_constraint
                  FROM sys.tables AS a
                 INNER JOIN sys.schemas AS b            ON a.schema_id = b.schema_id AND a.is_ms_shipped = 0
                 INNER JOIN sys.indexes AS c            ON a.object_id = c.object_id
                 INNER JOIN sys.index_columns AS d      ON d.object_id = c.object_id AND d.index_id = c.index_id
                 INNER JOIN sys.columns AS e            ON e.object_id = d.object_id AND e.column_id = d.column_id
                 INNER JOIN sys.data_spaces AS f        ON f.data_space_id = c.data_space_id
        )
        SELECT
               Drop_Index   = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)
                                   THEN 'ALTER TABLE ' + a.table_name + ' DROP CONSTRAINT ' + a.ix_name
                                   ELSE 'DROP INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name + '.' + a.table_name  END
              ,Create_Index = CASE WHEN (a.is_primary_key = 1 OR a.is_unique_constraint = 1)
                                   THEN 'ALTER TABLE ' + a.table_name + ' ADD CONSTRAINT ' + a.ix_name 
                                       + CASE WHEN a.is_primary_key = 1 THEN ' PRIMARY KEY' ELSE ' UNIQUE' END + '(' + indexColumns.ix_index_column_name + ')'
                                   ELSE 'CREATE ' + CASE WHEN a.ix_unique = 1 THEN 'UNIQUE ' ELSE '' END 
                                       + a.ix_type_desc + ' INDEX ' + a.ix_name collate SQL_Latin1_General_CP1_CI_AS + ' ON ' + a.schema_name
                                       + '.' + a.table_name + '(' + indexColumns.ix_index_column_name + ')'
                                       + CASE WHEN IncludeIndex.ix_included_column_name IS NOT NULL THEN ' INCLUDE (' + IncludeIndex.ix_included_column_name + ')' ELSE '' END
                                       + ' ON [' + a.fg_name +']' END
              ,CASE WHEN a.ix_unique = 1 THEN 'UNIQUE' END AS ix_unique
              ,a.ix_type_desc
              ,a.ix_name
              ,a.schema_name
              ,a.table_name
              ,indexColumns.ix_index_column_name
              ,IncludeIndex.ix_included_column_name
              ,a.fg_name
              ,a.is_primary_key
              ,a.is_unique_constraint                                       
        FROM
        (
                SELECT DISTINCT
                       ix_unique
                      ,ix_type_desc
                      ,ix_name
                      ,schema_name
                      ,table_name
                      ,fg_name
                      ,is_primary_key
                      ,is_unique_constraint
                  FROM tx
        ) AS a
        OUTER APPLY
        (
                SELECT ix_index_column_name
                       = STUFF((
                                SELECT ',' + column_name + CASE WHEN is_descending_key = 1 THEN ' DESC' ELSE '' END
                                  FROM tx AS b
                                 WHERE schema_name = a.schema_name
                                   AND table_name=a.table_name
                                   AND ix_name=a.ix_name
                                   AND ix_type_desc=a.ix_type_desc
                                   AND fg_name=a.fg_name
                                   AND is_included_column=0
                                 ORDER BY index_column_id
                                   FOR XML PATH('')
                                ),1,1,'')
        )IndexColumns
        OUTER APPLY
        (
                SELECT ix_included_column_name
                       = STUFF((
                                SELECT ',' + column_name
                                  FROM tx AS b
                                 WHERE schema_name = a.schema_name
                                   AND table_name=a.table_name
                                   AND ix_name=a.ix_name
                                   AND ix_type_desc=a.ix_type_desc
                                   AND fg_name=a.fg_name
                                   AND is_included_column=1
                                 ORDER BY index_column_id
                                   FOR XML PATH('')
                                ), 1,1,'')
        )IncludeIndex
        ORDER BY a.schema_name,a.table_name,a.ix_name;
END

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Mysql常用sql语句(13)- having 过滤分组结果集
Mysql常用sql语句(13)- having 过滤分组结果集
69 0
Mysql常用sql语句(5)- as 设置别名
Mysql常用sql语句(5)- as 设置别名
85 0
mysql常用sql语句
本文主要介绍基本的 SQL 语句,数据的增删查改等。
74 0
MySQL系列专题(2)-MySQL的SQL语句和高级特性
MySQL系列专题(2)-MySQL的SQL语句和高级特性
93 0
MySQL关于SQL语句的一些题目
MySQL关于SQL语句的一些题目 1、用一条SQL语句查询出每门课都大于80的学生的姓名 (1)SELECT NAME FROM score GROUP BY NAME HAVING MIN(grade) > 80 (2)SELECT DISTINCT NAME FROM score WH.
1537 0
MySQL的常用SQL语句
修改密码 这是常见的大家一般都要用的   首先安装成功了打开cmd –> mysql -u root -p –>输入你的密码 –>修改mysql root用户密码    格式:mysql> set password for 用户名@localhost = password(‘新密码’);    举例:mysql> set password for root@localhost = password(‘root’); 上面例子将用户root的密码更改为root; 如果是学习使用一般密码该简单些,如root,123等,这样便于记忆。
1592 0
MySQl之最全且必会的sql语句
原文链接:http://blog.csdn.net/qq_32059827/article/details/51763950 创建一个名称为mydb1的数据库,如果有mydb1数据库则直接使用,如果无则创建mydb1数...
817 0
【C/C++学院】(21)Mysql数据库编程--开发简介/SQL语句
<h1><span style="line-height:1.5"><span style="font-size:18px">一.mysql安装与管理</span></span></h1> <div><span style="line-height:1.5; font-size:18px; font-family:'Microsoft YaHei UI','Microsoft YaHei
1219 0
利用tcpdump抓取mysql sql语句
这个脚本是我之前在网上无意间找个一个利用tcpdump 抓包工具获取mysql流量,并通过过滤把sql 语句输入。 脚本不是很长,但是效果很好。 #!/bin/bash #this script used montor mysql network traffic.
999 0
+关注
杰克.陈
一个安静的程序猿~
文章
问答
文章排行榜
最热
最新
相关电子书
更多
SQL Sever迁移PG经验
立即下载
SQL Server 2017
立即下载
时序数据库TSDB新功能 - 如何用SQL进行时序查询
立即下载