How to get indexes?

I want to get a list of indexes for a certain table. I like the
sp_helpindex [table_name], but it also returns the primary key index.
How can I get what sp_helpindex returns without the primary key index?
Hi,
Query either SYSINDEXES table or use sp_help <tablename>
Thanks
Hari
SQL Server MVP
"Frank Rizzo" <none@.none.com> wrote in message
news:upXtVEmvFHA.3756@.tk2msftngp13.phx.gbl...
>I want to get a list of indexes for a certain table. I like the
>sp_helpindex [table_name], but it also returns the primary key index. How
>can I get what sp_helpindex returns without the primary key index?
|||I have created one procedure named "Proc_helpindex_without_pk". 99%
code is copied from sp_helpindex procedure and i added 1% piece of code
to meet your requirments.
Do not update sql server system tables and system procedure.
The Customizied Code is as follows.
create proc Proc_helpindex_without_pk
@.objname nvarchar(776)-- the table to check for indexes
as
-- PRELIM
set nocount on
declare @.objid int,-- the object id of the table
@.indid smallint,-- the index id of an index
@.groupid smallint, -- the filegroup id of an index
@.indname sysname,
@.groupname sysname,
@.status int,
@.keys nvarchar(2126),--Length
(16*max_identifierLength)+(15*2)+(16*3)
@.dbnamesysname
-- Check to see that the object names are local to the current
database.
select @.dbname = parsename(@.objname,3)
if @.dbname is not null and @.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
select @.dbname=db_name()
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 indid, groupid, name, status from sysindexes
where id = @.objid and indid > 0 and indid < 255 and (status & 64)=0
and name not in (select constraint_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = object_name(@.objid) and
constraint_type = 'primary key')
order by indid
open ms_crs_ind
fetch ms_crs_ind into @.indid, @.groupid, @.indname, @.status
-- IF NO INDEX, QUIT
if @.@.fetch_status < 0
begin
deallocate ms_crs_ind
raiserror(15472,-1,-1) --'Object does not have any indexes.'
return (0)
end
-- create temp table
create table #spindtab
(
index_namesysnamecollate database_default NOT NULL,
statsint,
groupnamesysname collate database_default NOT NULL,
index_keysnvarchar(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 = groupname from sysfilegroups where groupid =
@.groupid
-- INSERT ROW FOR INDEX
insert into #spindtab values (@.indname, @.status, @.groupname, @.keys)
-- Next index
fetch ms_crs_ind into @.indid, @.groupid, @.indname, @.status
end
deallocate ms_crs_ind
-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
declare @.empty varchar(1) select @.empty = ''
declare @.des1varchar(35),-- 35 matches spt_values
@.des2varchar(35),
@.des4varchar(35),
@.des32varchar(35),
@.des64varchar(35),
@.des2048varchar(35),
@.des4096varchar(35),
@.des8388608varchar(35),
@.des16777216varchar(35)
select @.des1 = name from master.dbo.spt_values where type = 'I' and
number = 1
select @.des2 = name from master.dbo.spt_values where type = 'I' and
number = 2
select @.des4 = name from master.dbo.spt_values where type = 'I' and
number = 4
select @.des32 = name from master.dbo.spt_values where type = 'I' and
number = 32
select @.des64 = name from master.dbo.spt_values where type = 'I' and
number = 64
select @.des2048 = name from master.dbo.spt_values where type = 'I' and
number = 2048
select @.des4096 = name from master.dbo.spt_values where type = 'I' and
number = 4096
select @.des8388608 = name from master.dbo.spt_values where type = 'I'
and number = 8388608
select @.des16777216 = name from master.dbo.spt_values where type = 'I'
and number = 16777216
-- 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 (stats & 16)<>0 then 'clustered' else 'nonclustered' end
+ case when (stats & 1)<>0 then ', '+@.des1 else @.empty end
+ case when (stats & 2)<>0 then ', '+@.des2 else @.empty end
+ case when (stats & 4)<>0 then ', '+@.des4 else @.empty end
+ case when (stats & 64)<>0 then ', '+@.des64 else case when (stats
& 32)<>0 then ', '+@.des32 else @.empty end end
+ case when (stats & 2048)<>0 then ', '+@.des2048 else @.empty end
+ case when (stats & 4096)<>0 then ', '+@.des4096 else @.empty end
+ case when (stats & 8388608)<>0 then ', '+@.des8388608 else @.empty
end
+ case when (stats & 16777216)<>0 then ', '+@.des16777216 else
@.empty end
+ ' located on ' + groupname),
'index_keys' = index_keys
from #spindtab
order by index_name
return (0) -- sp_helpindex
exec Proc_helpindex_without_pk 'authors'
try. all the best
|||Thank you. This is great.
Praveen wrote:

>I have created one procedure named "Proc_helpindex_without_pk". 99%
>code is copied from sp_helpindex procedure and i added 1% piece of code
>to meet your requirments.
>Do not update sql server system tables and system procedure.
>The Customizied Code is as follows.
>create proc Proc_helpindex_without_pk
>@.objname nvarchar(776)-- the table to check for indexes
>as
>-- PRELIM
>set nocount on
>declare @.objid int,-- the object id of the table
>@.indid smallint,-- the index id of an index
>@.groupid smallint, -- the filegroup id of an index
>@.indname sysname,
>@.groupname sysname,
>@.status int,
>@.keys nvarchar(2126),--Length
>(16*max_identifierLength)+(15*2)+(16*3)
>@.dbnamesysname
>-- Check to see that the object names are local to the current
>database.
>select @.dbname = parsename(@.objname,3)
>if @.dbname is not null and @.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
>select @.dbname=db_name()
>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 indid, groupid, name, status from sysindexes
>where id = @.objid and indid > 0 and indid < 255 and (status & 64)=0
>and name not in (select constraint_name
> from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where table_name = object_name(@.objid) and
> constraint_type = 'primary key')
>order by indid
>open ms_crs_ind
>fetch ms_crs_ind into @.indid, @.groupid, @.indname, @.status
>-- IF NO INDEX, QUIT
>if @.@.fetch_status < 0
>begin
>deallocate ms_crs_ind
>raiserror(15472,-1,-1) --'Object does not have any indexes.'
>return (0)
>end
>-- create temp table
>create table #spindtab
>(
>index_namesysnamecollate database_default NOT NULL,
>statsint,
>groupnamesysname collate database_default NOT NULL,
>index_keysnvarchar(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 = groupname from sysfilegroups where groupid =
>@.groupid
>-- INSERT ROW FOR INDEX
>insert into #spindtab values (@.indname, @.status, @.groupname, @.keys)
>-- Next index
>fetch ms_crs_ind into @.indid, @.groupid, @.indname, @.status
>end
>deallocate ms_crs_ind
>-- SET UP SOME CONSTANT VALUES FOR OUTPUT QUERY
>declare @.empty varchar(1) select @.empty = ''
>declare @.des1varchar(35),-- 35 matches spt_values
>@.des2varchar(35),
>@.des4varchar(35),
>@.des32varchar(35),
>@.des64varchar(35),
>@.des2048varchar(35),
>@.des4096varchar(35),
>@.des8388608varchar(35),
>@.des16777216varchar(35)
>select @.des1 = name from master.dbo.spt_values where type = 'I' and
>number = 1
>select @.des2 = name from master.dbo.spt_values where type = 'I' and
>number = 2
>select @.des4 = name from master.dbo.spt_values where type = 'I' and
>number = 4
>select @.des32 = name from master.dbo.spt_values where type = 'I' and
>number = 32
>select @.des64 = name from master.dbo.spt_values where type = 'I' and
>number = 64
>select @.des2048 = name from master.dbo.spt_values where type = 'I' and
>number = 2048
>select @.des4096 = name from master.dbo.spt_values where type = 'I' and
>number = 4096
>select @.des8388608 = name from master.dbo.spt_values where type = 'I'
>and number = 8388608
>select @.des16777216 = name from master.dbo.spt_values where type = 'I'
>and number = 16777216
>-- 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 (stats & 16)<>0 then 'clustered' else 'nonclustered' end
>+ case when (stats & 1)<>0 then ', '+@.des1 else @.empty end
>+ case when (stats & 2)<>0 then ', '+@.des2 else @.empty end
>+ case when (stats & 4)<>0 then ', '+@.des4 else @.empty end
>+ case when (stats & 64)<>0 then ', '+@.des64 else case when (stats
>& 32)<>0 then ', '+@.des32 else @.empty end end
>+ case when (stats & 2048)<>0 then ', '+@.des2048 else @.empty end
>+ case when (stats & 4096)<>0 then ', '+@.des4096 else @.empty end
>+ case when (stats & 8388608)<>0 then ', '+@.des8388608 else @.empty
>end
>+ case when (stats & 16777216)<>0 then ', '+@.des16777216 else
>@.empty end
>+ ' located on ' + groupname),
>'index_keys' = index_keys
>from #spindtab
>order by index_name
>
>return (0) -- sp_helpindex
>--
>exec Proc_helpindex_without_pk 'authors'
>try. all the best
>
>