老是忘记创建索引的语句结构,又不想用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
