하여튼. 토다아저씨는 맨날 이상한거만 시키드라 -_-
해주믄 믿지도 않음시렁.닝길.
암턴 토다아저씨가 해달라는 대로 사용중인 테이블에 설정되어 있는 인덱스 정보를 뿌리는 쿼리를 맹글어 보았다....고 말하고 싶지만 어차피 시스템 프로시져 고대로 쓴거일뿐.
평소 테이블 정보 볼 때는 alt + F1 으로 보는데, 이게 실은 sp_help 'table_name' 한거랑 같다. 그래서 sp_help 프로시져를 까봤더니, 인덱스 정보를 뿌리는 부분은 sp_helpindex를 호출하더라는 말씀. 그래서 sp_helpindex 프로시져를 다시 까서 그안에다가 몇줄 추가.
----------------------------------------------------------------------------
CREATE TABLE #indexinfo
(
table_name nvarchar(210) NOT NULL,
index_name nvarchar(210) NOT NULL,
index_description nvarchar(210) NOT NULL,
index_keys nvarchar(2126) NOT NULL
)
declare @objname nvarchar(776) -- the table to check for indexes
declare Index_Cursor cursor local fast_forward
FOR
SELECT A.name AS TableName--, B.rows AS Counts
FROM sysobjects A
JOIN sysindexes B ON B.id = A.id AND B.indid <2
WHERE A.xtype = N'U'
AND B.name IS NOT NULL
for read only
open Index_Cursor
fetch next from Index_Cursor
into @objname
WHILE @@FETCH_STATUS = 0
BEGIN
--select @objname = 'ItemSiteMaster'
--as
-- 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)
@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
-- 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
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
-- 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 table
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
)
-- 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
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)
-- 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
end
deallocate ms_crs_ind
-- DISPLAY THE RESULTS
insert into #indexinfo (table_name, index_name, index_description, index_keys)
select @objname,
'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
from #spindtab
order by index_name
--return (0) -- sp_helpindex
drop table #spindtab
FETCH NEXT from Index_Cursor
into @objname
END
close Index_Cursor
deallocate Index_Cursor
select * from #indexinfo
order by table_name
drop table #indexinfo
------------------------------------------------------------------------
이상 발로 짠 쿼리...털썩.
근데 토다아저씨가 결과값의 index_name이 이상하다고 우긴다 -_-;;;
뒤에 막 숫자 붙어있고 하는게 이상하단다... 거 아저씨 참 내말 안믿어 하여튼.
결과 값을 안믿으믄 나보고 어쩌라는겨. 암턴. 확인 시켜 주기 위해 index rebuild 해서
갱신 시간 뿌려줬다.
alter index 인덱스명 on 테이블명 rebuild ;
SELECT 'Index Name' = i.name, 'Statistics Date' = STATS_DATE(i.object_id, i.index_id)
FROM sys.objects o
JOIN sys.indexes i ON o.name = 테이블명 AND o.object_id = i.object_id;
시간 정보 보는 두번째 쿼리는 SQLER에서 GET!! 근데 작성자분의 이름이;;;이름이;;;;
죄송합니당.
