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)
@.dbname sysname
-- 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_name sysname collate database_default NOT NULL,
stats int,
groupname sysname collate database_default NOT 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 = 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 @.des1 varchar(35), -- 35 matches spt_values
@.des2 varchar(35),
@.des4 varchar(35),
@.des32 varchar(35),
@.des64 varchar(35),
@.des2048 varchar(35),
@.des4096 varchar(35),
@.des8388608 varchar(35),
@.des16777216 varchar(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)
> @.dbname sysname
> -- 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_name sysname collate database_default NOT NULL,
> stats int,
> groupname sysname collate database_default NOT 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 = 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 @.des1 varchar(35), -- 35 matches spt_values
> @.des2 varchar(35),
> @.des4 varchar(35),
> @.des32 varchar(35),
> @.des64 varchar(35),
> @.des2048 varchar(35),
> @.des4096 varchar(35),
> @.des8388608 varchar(35),
> @.des16777216 varchar(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
>
>
