How to get the database name dynamically?

I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%'

and (DATABASENAME=?)

Can someone help me out?

Thanks.

Try the DB_NAME() function.

|||

DB_NAME() works but if i have to run this query dynamically in the database as per the database parameter how do i do it?

like

declare @.dbname varchar(100)

Select Table_name

From Information_schema.Tables

Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0

and table_name like 'Item%Master'

and DB_NAME=@.dbname

|||

You need to use dynamic SQL to execute commands in multiple databases at run-time. See example below on how to do it the safe way (without SQL injection risks):

Code Snippet

-- SQL Server 2005 version:

-- Will work with minimal changes in SQL Server 2000 too:

declare @.sp nvarchar(500), @.dbname nvarchar(128);

declare @.dbs cursor;

set @.dbs = cursor fast_forward for

select name from sys.databases

where name not in ('master', 'model', 'msdb', 'tempdb');

open @.dbs;

while(1=1)

begin

fetch @.dbs into @.dbname;

if @.@.fetch_status < 0 break;

set @.sp = quotename(@.dbname) + N'.sys.sp_executesql';

exec @.sp N'

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = ''BASE TABLE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + N''.'' + QUOTENAME(TABLE_NAME)),

''IsMSShipped'') = 0

'

end;

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
>
>

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)
@.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
>
>