DataBaser.Net: 인덱스정보수집

--으~ 귀찮어~
declare @tname varchar(255);

create table #temp(
	index_name varchar(255)
,	index_desc varchar(1000)
,	index_keys varchar(1000)
);

create table #rs(
	table_name varchar(255)
,	index_name varchar(255)
,	index_desc varchar(1000)
,	index_keys varchar(1000)
)

declare cur cursor for
select 
	schema_name(schema_id) + '.' + name
from sys.objects
where type = 'U'
and name <> 'sysdiagrams';
/*
--2000
select 
	name
from sysobjects
where type = 'U'
and name <> 'sysdiagrams';
*/

open cur;
fetch next from cur into @tname;
while @@FETCH_STATUS not in (-1, -2)
begin
	insert #temp exec sp_helpindex @tname;

	insert #rs
	select @tname, *
	from #temp;

	if @@rowcount = 0
	begin
		insert #rs values(@tname, '없음', '없음', '없음');
	end

	truncate table #temp;
	fetch next from cur into @tname;
end
select * from #rs order by 1;

drop table #temp;
drop table #rs;
close cur;
deallocate cur;
Contents
