Contents

1 SSMS 焔伎 磯 SP
2 碁煙 覲 譟壱
3 企 一危 覲願鍵(TOP 100蟇)


1 SSMS 焔伎 磯 SP #

'蟲 -> 旧 -> る慨' 覃企ゼ 覲企...
ssms.jpg

SP襯 master 一危磯伎れ 焔伎 磯 . 2005覯朱 殊る伎 ろる 螳 れ願覃伎 覿ク 譟. 襯 れ, Ctrl + 3 sp_helpindex 襦螳 焔 螻, 'Databaser.TableA' 螳 企 る 'TableA'襷 襷一る Ctrl + 3襯 襯企 觧 . 蠏碁 螳 SP襯 讌 襷れ 伎 . (譯 蠏碁.. 蠏谿 譯所.)

2 碁煙 覲 譟壱 #

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
--.. 譟磯 手海. <;;

3 企 一危 覲願鍵(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