[edit]
1 SSMS 焔伎 磯 SP #
'蟲 -> 旧 -> る慨' 覃企ゼ 覲企...
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
--.. 譟磯 手海. ＜;;
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