miing`S

하여튼. 토다아저씨는 맨날 이상한거만 시키드라 -_-
해주믄 믿지도 않음시렁.닝길.
암턴 토다아저씨가 해달라는 대로 사용중인 테이블에 설정되어 있는 인덱스 정보를 뿌리는 쿼리를 맹글어 보았다....고 말하고 싶지만 어차피 시스템 프로시져 고대로 쓴거일뿐.
평소 테이블 정보 볼 때는 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!! 근데 작성자분의 이름이;;;이름이;;;;
죄송합니당.