IT狂想曲~

/* 取得目前資料庫，所有 Table 的索引狀況 */
set nocount on;

/*暫存 sp_helpindex 用的臨時表*/
create table #Index (
	[index_name] nvarchar(255) null,
	[index_description] varchar(210) null,
	[index_keys] nvarchar(2078) null );

/* 最終處理的彙整表單 */
create table #Summary (
    [schema_name] nvarchar(255) null ,
	[table_name] nvarchar(255) null ,
	[index_name] nvarchar(255) null ,
	[index_description] varchar(210) null,
	[index_keys] nvarchar(2078) null ,
	[IsPrimary] bit ,
	[IsClustered] bit ,
	[IsUnique] bit ,
	[IsNoIndex] bit );

declare @TableName nvarchar(100);
declare @TableSchema nvarchar(100);
declare @objname nvarchar(255);



declare tablename cursor  for
    select TABLE_SCHEMA , TABLE_NAME from INFORMATION_SCHEMA.TABLES
	order by TABLE_SCHEMA , TABLE_NAME

open tablename

fetch next from tablename into @TableSchema , @TableName
while @@fetch_status = 0
	begin

		/*清空上一次的結果*/
		truncate table #Index;
		set @objname = N'['+@TableSchema +N'].[' + @TableName + N']';
		/*把 sp_helpindex 塞入臨時表備用*/
		insert into #Index
		exec sp_helpindex @objname;

		if ( exists(select top 1 [index_name] from #Index ) ) begin
			insert into #Summary (
				[schema_name] , [table_name] , [index_name] , [index_description] , [index_keys] ,
				[IsPrimary] ,
				[IsClustered] , 
				[IsUnique] ,
				[IsNoIndex]
			) select 
				@TableSchema , @TableName , [index_name] , [index_description] , [index_keys] ,
				case when [index_description] like '%primary key%' then 1 else 0 end [IsPrimary] ,
				case when [index_description] like 'clustered%' then 1 else 0 end [IsClustered] ,
				case when [index_description] like '%unique%' then 1 else 0 end [IsUnique] ,
				0 [IsNoIndex]
			from #Index;
		end else begin
			insert into #Summary (
				[schema_name] , [table_name] , [index_name] , [index_description] , [index_keys] , 
				[IsPrimary] ,[IsClustered] , [IsUnique] , [IsNoIndex]
			) values (
				@TableSchema , @TableName , '缺索引' , '缺索引' , '缺索引' , 
				0 , 0 , 0 , 1
			);
		end

		/*取下一筆資料出來跑*/
		fetch next from tablename into @TableSchema , @TableName

	end

close tablename
deallocate tablename

select * from #Summary;
drop table #Index;
drop table #Summary;