블로그 이미지
SQL Server VS. 석이 minsouk@hotmail.com MSSQL 쿼리성능 관련해 궁금한 사항이 있다면 언제나 누구나 TeamViewer + Line (네이버 japan 메신저) 에 minsouk1 추가 후 연락주세요~ 010-9967-0955 보미아빠

카테고리

보미아빠, 석이 (446)
밥벌이 (16)
싸이클 (1)
일상 (1)
Total211,711
Today13
Yesterday45

달력

« » 2018.06
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

공지사항

readtrace

분류없음 / 2010.12.27 17:13

Posted by 보미아빠
.
Posted by 보미아빠

sp_helpindex sql 2008

분류없음 / 2010.12.27 11:01
/*============================================================================
  File:     sp_helpindex2.sql

 Summary:  So, what are the included columns?!
This is a MODIFIED sp_helpindex script that includes INCLUDEd
columns AND filtered index columns.
  
  Date:     April 2008

  SQL Server *2008* Version: 10.00.1300.13 (SQL Server 2008 CTP6)
------------------------------------------------------------------------------
  Written by Kimberly L. Tripp, SYSolutions, Inc.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by SQLskills instructors.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

USE master
go

create procedure sp_helpindex2
@objname nvarchar(776) -- the table to check for indexes
as

-- Updated in April 2008 by Kimberly L. Tripp, SQLskills.com
-- to add included columns AND filter_definition to the output. 
-- See Kimberly's blog for updates and/or additional information
-- http://www.SQLskills.com/blogs/Kimberly

-- PRELIM
set nocount on

declare @objid int, -- the object id of the table
@indid smallint, -- the index id of an index
@groupid int,   -- the filegroup id of an index
@indname sysname,
@groupname sysname,
@status int,
@keys nvarchar(2126), --Length (16*max_identifierLength)+(15*2)+(16*3)
@inc_columns nvarchar(max),
@inc_Count smallint,
@loop_inc_Count smallint,
@dbname sysname,
@ignore_dup_key bit,
@is_unique bit,
@is_hypothetical bit,
@is_primary_key bit,
@is_unique_key bit,
@auto_created bit,
@no_recompute bit,
@filter_definition nvarchar(max)

-- Check to see that the object names are local to the current database.
select @dbname = parsename(@objname,3)
if @dbname is null
select @dbname = db_name()
else if @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

-- Check to see the the table exists and initialize @objid.
select @objid = object_id(@objname)
if @objid is NULL
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end

-- OPEN CURSOR OVER INDEXES (skip stats: bug shiloh_51196)
declare ms_crs_ind cursor local static for
select i.index_id, i.data_space_id, i.name,
i.ignore_dup_key, i.is_unique, i.is_hypothetical, i.is_primary_key, i.is_unique_constraint,
s.auto_created, s.no_recompute, i.filter_definition
from sys.indexes i join sys.stats s
on i.object_id = s.object_id and i.index_id = s.stats_id
where i.object_id = @objid
open ms_crs_ind
fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition

-- IF NO INDEX, QUIT
if @@fetch_status < 0
begin
deallocate ms_crs_ind
raiserror(15472,-1,-1,@objname) -- Object does not have any indexes.
return (0)
end

-- create temp tables
CREATE TABLE #spindtab
(
index_name sysname collate database_default NOT NULL,
index_id int,
ignore_dup_key bit,
is_unique bit,
is_hypothetical bit,
is_primary_key bit,
is_unique_key bit,
auto_created bit,
no_recompute bit,
groupname sysname collate database_default NULL,
index_keys nvarchar(2126) collate database_default NOT NULL, -- see @keys above for length descr
filter_definition nvarchar(max),
inc_Count smallint,
inc_columns nvarchar(max)
)

CREATE TABLE #IncludedColumns
( RowNumber smallint,
[Name] nvarchar(128)
)

-- Now check out each index, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
while @@fetch_status >= 0
begin
-- First we'll figure out what the keys are.
declare @i int, @thiskey nvarchar(131) -- 128+3

select @keys = index_col(@objname, @indid, 1), @i = 2
if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)
select @keys = @keys  + '(-)'

select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'

while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@objname, @indid, @i)
if ((@thiskey is not null) and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))
select @thiskey = @thiskey + '(-)'
end

-- Second, we'll figure out what the included columns are.
SELECT @inc_Count = count(*)
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS si 
ON (si.index_id > 0 
and si.is_hypothetical = 0) 
AND (si.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic 
ON (ic.column_id > 0 
and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
INNER JOIN sys.columns AS clmns 
ON clmns.object_id = ic.object_id 
and clmns.column_id = ic.column_id
WHERE ic.is_included_column = 1 and
(si.index_id = @indid) and 
(tbl.object_id= @objid)

IF @inc_Count > 0
INSERT #IncludedColumns
SELECT ROW_NUMBER() OVER (ORDER BY clmns.column_id) 
, clmns.name 
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS si 
ON (si.index_id > 0 
and si.is_hypothetical = 0) 
AND (si.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic 
ON (ic.column_id > 0 
and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) 
AND (ic.index_id=CAST(si.index_id AS int) AND ic.object_id=si.object_id)
INNER JOIN sys.columns AS clmns 
ON clmns.object_id = ic.object_id 
and clmns.column_id = ic.column_id
WHERE ic.is_included_column = 1 and
(si.index_id = @indid) and 
(tbl.object_id= @objid)
SELECT @inc_columns = [Name] FROM #IncludedColumns WHERE RowNumber = 1

SET @loop_inc_Count = 1

WHILE @loop_inc_Count < @inc_Count
BEGIN
SELECT @inc_columns = @inc_columns + ', ' + [Name] 
FROM #IncludedColumns WHERE RowNumber = @loop_inc_Count + 1
SET @loop_inc_Count = @loop_inc_Count + 1
END
select @groupname = null
select @groupname = name from sys.data_spaces where data_space_id = @groupid

-- INSERT ROW FOR INDEX
insert into #spindtab values (@indname, @indid, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @groupname, @keys, @filter_definition, @inc_Count, @inc_columns)

-- Next index
fetch ms_crs_ind into @indid, @groupid, @indname, @ignore_dup_key, @is_unique, @is_hypothetical,
@is_primary_key, @is_unique_key, @auto_created, @no_recompute, @filter_definition
end
deallocate ms_crs_ind

-- DISPLAY THE RESULTS
select
'index_name' = index_name,
'index_description' = convert(varchar(210), --bits 16 off, 1, 2, 16777216 on, located on group
case when index_id = 1 then 'clustered' else 'nonclustered' end
+ case when ignore_dup_key <>0 then ', ignore duplicate keys' else '' end
+ case when is_unique <>0 then ', unique' else '' end
+ case when is_hypothetical <>0 then ', hypothetical' else '' end
+ case when is_primary_key <>0 then ', primary key' else '' end
+ case when is_unique_key <>0 then ', unique key' else '' end
+ case when auto_created <>0 then ', auto create' else '' end
+ case when no_recompute <>0 then ', stats no recompute' else '' end
+ ' located on ' + groupname),
'index_keys' = index_keys,
--'num_included_columns' = inc_Count,
'included_columns' = inc_columns,
'filter_definition' = filter_definition
from #spindtab
order by index_name

return (0) -- sp_helpindexwinc2
go

exec sys.sp_MS_marksystemobject 'sp_helpindex2'
Posted by 보미아빠

최근에 달린 댓글

최근에 받은 트랙백

글 보관함