#title SSMS에 등록해서 쓰면 유용한 SP
[[TableOfContents]]

==== SSMS에 등록해서 쓰는 SP들 ====
'도구 -> 옵션 -> 키보드' 메뉴를 보면...
attachment:ssms.jpg

아래의 SP를 master 데이터베이스에 등록해서 쓰면 된다. 2005버전으로 올라오면서 스키마의 개념이 들어가면서 불편해 졌다. 예를 들어, Ctrl + 3에 sp_helpindex 저장 프로시저가 등록되어 있고, 'Databaser.TableA'와 같은 테이블이 있다면 'TableA'만 마우스로 선택하여 Ctrl + 3를 누르면 뻑이 난다. 그래서 아래와 같은 SP를 직접 만들어 사용해야 한다. (아주 그냥.. 귀찮 죽겠다.)

==== 인덱스 정보 조회 ====
{{{
use master
go
--인덱스 정보 조회
alter proc dbo.sp_helpindex_new
	@table_name nvarchar(500)
as
declare 
	@is_partitioned bit
--,	@table_name nvarchar(500)
--,	@schema_name nvarchar(500);

--set @schema_name = '스키마명';
--set @table_name = '테이블명';

;with index_keys(id, indid, column_name, keyno)
as
(
	select 
		id
	,	indid 
	,	col_name(id, colid) +
		case 
			when indexkey_property(id, colid, keyno, 'IsDescending') = 1 then '(-)' 
			else '(+)'
		end column_name
	,	keyno
	from sys.sysindexkeys
),
sys_index_key(id, indid, column_name)
as
(
	select distinct
		id
	,	indid
	,	stuff((
		select ',' + column_name as 'text()'
		from index_keys 
		where a.indid = indid
		and a.id = id
		for xml path('')
		), 1, 1, '') column_name
	from index_keys a
) 
select
	(select top 1 schema_name(schema_id) from sys.all_objects 
	where a.id = object_id) sch_name
,	object_name(b.id) table_name
,	b.name index_name
,	a.column_name index_key
,	b.rowcnt row_count
,	case when b.indid = 1 then 'clustered' else 'nonclustered' end index_type
,	(select top 1 groupname from sys.sysfilegroups where b.groupid = groupid) group_name
,	isnull((select top 1 1 from sys.partitions 
	where a.id = object_id and a.indid = index_id
	and partition_number > 1), 0) is_partitoned
,	convert(varchar, convert(numeric(36,2), b.reserved / 1024.00)) + ' MB' reserved
,	convert(varchar, convert(numeric(36,2), b.used / 1024.00)) + ' MB' used
,	b.OrigFillFactor fill_factor
from sys_index_key a
	inner join sys.sysindexes b
		on a.id = b.id
		and a.indid = b.indid
	inner join sys.all_objects c
		on a.id = c.object_id
where 1=1
and b.name not like '_WA_Sys%'
and object_name(b.id) = @table_name
order by 1, 2


if exists (select * from sys.partitions where object_name(object_id) = @table_name)
begin
	;with part
	as
	(
	select 
		a.function_id
	,	a.type_desc
	,	b.boundary_id partition_number
	,	b.value
	,	c.name 
	from sys.partition_functions a 
		inner join sys.partition_range_values b
			on a.function_id = b.function_id
		inner join sys.partition_schemes c
			on a.function_id = c.function_id
	)
	select distinct 
		schema_name(d.schema_id) sch_name
	,	object_name(a.object_id) obj_name
	,	b.name index_name
	,	c.name 
	,	a.rows
	,	e.value boundary_value
	from sys.partitions a 
		inner join sys.indexes b
			on a.object_id = b.object_id
			and a.index_id = b.index_id
		inner join sys.data_spaces c
			on b.data_space_id = c.data_space_id
		inner join sys.all_objects d
			on d.object_id = a.object_id
		inner join part e
			on c.name = e.name
			and a.partition_number = e.partition_number
	where object_name(d.object_id)=@table_name
	--and schema_name(d.schema_id) = @schema_name
	order by 1,2,3,4,6
end
go
--아.. 조낸 피곤허다. ㅡㅡ;;
}}}

==== 테이블 데이터 보기(TOP 100건) ====
기냥 'select top 100 * from '으로 등록해도 된당..
{{{
use master
go
--테이블 데이터 보기(TOP 100건)
alter proc [dbo].[sp_top100]
	@tname nvarchar(255)
as

set nocount on
set statistics io off

create table #space
(
	nm nvarchar(255)
,	rows bigint
,	reserved varchar(200)
,	data varchar(200)
,	index_size varchar(200)
,	unused varchar(200)
);

declare 
	@sql nvarchar(4000)
,	@table_name nvarchar(500)

declare cur cursor for
	select 
		'select top 100 * from [' + table_schema + '].[' + table_name + ']'
	,	'[' + table_schema + '].[' + table_name + ']'
	from information_schema.tables
	where table_name = @tname

open cur
fetch next from cur into @sql, @table_name

while @@fetch_status = 0
begin
	exec sp_spaceused @table_name
	exec(@sql)
	fetch next from cur into @sql, @table_name
end

close cur
deallocate cur
go
}}}