获取索引结构信息

老是忘记创建索引的语句结构,又不想用ddl.随便写了个脚本获取索引信息.

 

1 创建存储过程 

create procedure dc_usp_getindexinfo
as
------ get index info
create table #
(dbname sysname,
 tabname sysname,
 indexname sysname,
 index_type sysname,
 index_desc int,
 columnname sysname,
 index_column_id int,
 is_included_column sysname,
 groupname sysname
);
--- account TableHasIndex=1 of table
declare @sql varchar(max),@tsql varchar(500);
select
 @sql=''
,@tsql=
'insert into #
 select
 db_name()as dbname
,object_name(x2.[object_id]) as tabname
,x1.name as indexname
,x1.type_desc as index_type
,case when indexkey_property(x2.[object_id], x1.index_id, x3.index_column_id, ''isdescending'') = 1 then 1 else 0 end
,x4.name as columnname
,x3.index_column_id
,x3.is_included_column
,(select name from  sys.data_spaces as x5 where x5.data_space_id= x1.data_space_id) as groupname
 from  ';
select @sql=@sql+'use '+name+char(10)+@tsql
+'sys.indexes as x1
 inner join sys.objects as x2 on x1.[object_id]=x2.[object_id]  and x2.type=''U''
 inner join sys.index_columns  as x3 on x1.index_id=x3.index_id and x1.[object_id]=x3.[object_id]
 inner join sys.columns as x4 on x3.[object_id]=x4.[object_id] and x3.column_id=x4.column_id
 order by tabname,indexname,index_column_id,is_included_column; '+char(10)
 from sys.databases
 where name not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB','dts','dbcenter')
 ---and name=''
 and databaseproperty(name,'isreadonly')<>1;
--- account tabname
execute(@sql);
-----  not include index
with index_text
as
(select dbname,tabname,indexname,index_type, cast(case when index_desc =1 then columnname +' desc' else columnname end as varchar(1000)) as columnname,index_column_id,is_included_column,groupname
  from # as x1 where index_column_id=1
  and  not exists
  (select 1 from # as x2
    where x1.tabname=x2.tabname
     and x1.indexname=x2.indexname
     and x1.index_type=x2.index_type and x2.is_included_column=1)
 union all
 select x1.dbname,x1.tabname,x1.indexname,x1.index_type,
         cast(x2.columnname+','+case when index_desc =1 then x1.columnname +' desc' else   x1.columnname end as varchar(1000)) as columnname,x1.index_column_id,x1.is_included_column,x1.groupname
   from #  as x1
 inner join index_text as x2
  on x1.dbname=x2.dbname
   and x1.tabname=x2.tabname
   and x1.indexname=x2.indexname
   and x1.index_type=x2.index_type
   and x1.index_column_id=x2.index_column_id+1
)
select *  into #index
from index_text  as x1
where index_column_id=
(select max(index_column_id)  from index_text as x2
  where  x1.dbname=x2.dbname
   and x1.tabname=x2.tabname
   and x1.indexname=x2.indexname
   and x1.index_type=x2.index_type
);
----included index
with index_text
as
(select dbname,tabname,indexname,index_type,case when index_desc =1 then columnname +' desc' else columnname end  as columnname, cast('' as  varchar(800)) as includecolumnname,index_column_id,is_included_column,groupname
  from # as x1 where index_column_id=1
  and   exists
  (select 1 from # as x2
    where x1.tabname=x2.tabname
     and x1.indexname=x2.indexname
     and x1.index_type=x2.index_type and x2.is_included_column=1)
 union all
 select x1.dbname,x1.tabname,x1.indexname,x1.index_type,
        x2.columnname,cast(includecolumnname+case when index_desc =1 then x1.columnname+' desc,' else x1.columnname+',' end as varchar(800)) as includecolumnname,x1.index_column_id,x1.is_included_column,x1.groupname
   from #  as x1
 inner join index_text as x2
  on x1.dbname=x2.dbname
   and x1.tabname=x2.tabname
   and x1.indexname=x2.indexname
   and x1.index_type=x2.index_type
   and x1.index_column_id=x2.index_column_id+1
)
select * into #includeindex
  from index_text  as x1
where index_column_id=
(select max(index_column_id)  from index_text as x2
  where  x1.dbname=x2.dbname
   and x1.tabname=x2.tabname
   and x1.indexname=x2.indexname
   and x1.index_type=x2.index_type
);
insert into dc_getindexinfo
select
 dbname,
 tabname,
 indexname,
 index_type,
 groupname,
 isinclude=0,
 'create '+index_type+' index '+indexname+' on '+tabname+'('+columnname+') on'+quotename(groupname)
 as sqltext,
 cast(convert(char(10),GETDATE(),120) as datetime)
 from #index
 union all
select
 dbname,
 tabname,
 indexname,
 index_type,
 groupname,
 isinclude=1,
 'create '+index_type+' index '+indexname+' on '+tabname+'('+columnname+') include('+ left(includecolumnname,len(includecolumnname)-1)+') on'+quotename(groupname)
 as sqltext,
  cast(convert(char(10),GETDATE(),120) as datetime)
 from #includeindex

 

2 执行存储过程

execute dc_usp_getindexinfo

 

3 适用于批量服务器 做一job 每天定时抽取到 dbcenter