/* Sccsid = "%Z% generic/sproc/%M% %I% %G%" */
/*	4.8	1.1	06/14/90	sproc/src/help */
/*
** 
*/

/*
** IMPORTANT NOTE:
** This batch sql uses the built-in function object_id() in the
** where clause of a select query. If you intend to change this query
** or use the object_id() or db_id() builtin in this procedure, please read the
** READ.ME file in the $DBMS/generic/sproc directory to ensure that the rules
** pertaining to object-id's and db-id's outlined there, are followed.
*/
set nocount on
go
declare my_cursor1 cursor
for select a.name from  sysobjects a, sysusers b where type='U' 
and a.uid=b.uid
go

declare @indid int			/* the index id of an index */
declare @keys varchar(1024)		/* string to build up index key in */
declare @inddesc varchar(68)		/* string to build up index desc in */
declare @msg varchar(1024)
declare @len1 int, @len2 int
declare @objname varchar(92)
declare @table_name varchar(20)
open my_cursor1
fetch my_cursor1 into @table_name
while (@@sqlstatus=0)
begin

select @objname = @table_name


if @@trancount = 0
begin
	set chained off
    
end

set transaction isolation level 1

/*
**  Check to see that the object names are local to the current database.
*/
if @objname like '%.%.%' and
	substring(@objname, 1, charindex('.', @objname) - 1) != db_name()
begin
	/* 17460, "Object must be in the current database." */
	/*raiserror 17460*/
	goto loop_next 
end

/*
**  Check to see the the table exists 
*/
if not exists (select id
		from sysobjects
			where id = object_id(@objname))
/*
**  Table doesn't exist so goto loop_next.
*/
begin
	/* 17461, "Object does not exist in this database." */
	/*raiserror 17461*/
	goto loop_next 
end

/*
**  See if the object has any indexes.
**  Since there may be more than one entry in sysindexes for the object,
**  this select will set @indid to the index id of the first index.
*/
select @indid = min(indid)
	from sysindexes
		where id = object_id(@objname)
			and indid > 0
			and indid < 255

/*
**  If no indexes, goto loop_next.
*/
if @indid is NULL
begin
	/* 17640, "Object does not have any indexes." */
        /*exec sp_getmessage 17640, @msg output*/
        /*print @msg*/
	goto loop_next 
end

/*
**  Now check out each index, figure out it's type and keys and
**  save the info in a temporary table that we'll print out at the end.
*/
create table #spindtab
(
	table_id    int,
    col0        varchar(30) default '<EOC>',
    index_name	varchar(30),
    col1        varchar(30) default '<EOC>',
	index_keys	varchar(1024),
    col2        varchar(30) default '<EOC>',
	index_description	varchar(68),
    col3        varchar(30) default '<EOC>',
	index_max_rows_per_page	smallint,
    col4        varchar(30) default '<EOC>',
	index_fillfactor	smallint,
    col5        varchar(30) default '<EOC>',
	index_reservepagegap	smallint,
    col6        varchar(30) default '<EOC>',
	index_created		datetime NULL,
    col7        varchar(30) default '<EOC>',
    row1        varchar(30) default '<EOR>'
)

/*  Create temporary table for sysattributes data */
create table #spindattr
(
	name		varchar(30), 
	class		smallint,
	attribute	smallint,
	int_value	int NULL,
	char_value	varchar(255) NULL,
	comments	varchar(255) NULL
)

while @indid is not NULL
begin
	
	/*
	**  First we'll figure out what the keys are.
	*/
	declare @i int
	declare @thiskey varchar(30)
	declare @sorder char(4)	
	declare @lastindid int

	select @keys = '', @i = 1

	set nocount on

	while @i <= 31
	begin
		select @thiskey = index_col(@objname, @indid, @i)

		if (@thiskey is NULL) 
		begin
			goto keysdone
		end

		if @i > 1
		begin
			select @keys = @keys + ', '
		end

		/*select @keys = @keys + index_col(@objname, @indid, @i)*/
		select @keys = @keys + @thiskey

		/*
		** Get the sort order of the column using index_colorder()
		** This support is added for handling descending keys.
		*/
		select @sorder = index_colorder(@objname, @indid, @i)
		if (@sorder = 'DESC')
			select @keys = @keys + ' ' + @sorder

		/*
		**  Increment @i so it will check for the next key.
		*/
		select @i = @i + 1

	end


	/*
	**  When we get here we now have all the keys.
	*/
	keysdone:
		set nocount on


	/*
	**  Initialize the index description by figuring out if it's a
	**  clustered or nonclustered index.
	*/
	if @indid = 1
	begin
		select @inddesc = 'clustered'
	end
	if @indid > 1
	begin
			if exists (select * from sysindexes i
					where status2 & 512 = 512
					and i.indid = @indid
					and i.id = object_id(@objname))
			begin
				select @inddesc = 'clustered'
			end 
			else
			begin
				select @inddesc = 'nonclustered'
			end
	end

	/*
	**  Now we'll check out the status bits for this index and
	**  build an english description from them.
	*/

	/*
	**  See if the index is unique (0x02).
	*/
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 2
				and i.id = object_id(@objname)
				and i.indid = @indid)
	begin
	select @inddesc = @inddesc + ', ' + v.name
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 2
				and i.id = object_id(@objname)
				and i.indid = @indid
	end
	else
	/* 
	** if this is a nonunique clustered index on dol tables, dup rows
	** are allowed
	*/
	if exists (select * from sysindexes i
			where status2 & 512 = 512
			and i.indid = @indid
			and i.id = object_id(@objname))
	begin
		select @inddesc = @inddesc + ', ' + v.name
			from master.dbo.spt_values v, sysindexes i
			where v.type = 'I'
			and v.number = 64
			and i.id = object_id(@objname)
			and i.indid = @indid
	end

	/*
	**  See if the index is ignore_dupkey (0x01).
	*/
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 1
				and i.id = object_id(@objname)
				and i.indid = @indid)
	begin
	select @inddesc = @inddesc + ', ' + v.name
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 1
				and i.id = object_id(@objname)
				and i.indid = @indid
	end

	/*
	**  See if the index is ignore_dup_row (0x04).
	*/
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 4
				and i.id = object_id(@objname)
				and i.indid = @indid)
	begin
	select @inddesc = @inddesc + ', ' + v.name
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 4
				and i.id = object_id(@objname)
				and i.indid = @indid
	end

	/*
	**  See if the index is allow_dup_row (0x40).
	*/
	if exists (select *
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 64
				and i.id = object_id(@objname)
				and i.indid = @indid)
	begin
	select @inddesc = @inddesc + ', ' + v.name   
		from master.dbo.spt_values v, sysindexes i
			where i.status & v.number = v.number
				and v.type = 'I'
				and v.number = 64
				and i.id = object_id(@objname)
				and i.indid = @indid
	end

	/*
	**  Add the location of the data.
	*/
	select @inddesc = @inddesc + ' located on ' + s.name
		from syssegments s, sysindexes i
			where s.segment = i.segment
				and i.id = object_id(@objname)
				and i.indid = @indid

	/*
	**  Now we have the whole description for the index so we'll add
	**  the goods to the temporary table.
	*/
	insert  #spindtab(table_id,index_name,index_keys,index_description,index_max_rows_per_page,
                index_fillfactor,index_reservepagegap,index_created) 
		select id, name, @keys, @inddesc, maxrowsperpage, fill_factor,
			isnull(res_page_gap,0), crdate
			from sysindexes
				where id = object_id(@objname)
					and indid = @indid
	/* 
	**  Get sysattributes data if there is any
	*/
	insert #spindattr (name, class, attribute, int_value, char_value, 
								comments)
		select i.name, a.class, a.attribute, a.int_value, a.char_value,
								a.comments
		from sysindexes i, sysattributes a
		where a.object_type = 'I'
		and a.object = object_id(@objname)
		and a.object_info1 = @indid
		and i.id = object_id(@objname)
		and i.indid = @indid

	/*
	**  Now move @indid to the next index.
	*/
	select @lastindid = @indid
	select @indid = NULL
	select @indid = min(indid)
		from sysindexes
			where id = object_id(@objname)
				and indid > @lastindid
				and indid < 255
end

/*
**  Now print out the contents of the temporary index table.
*/
select @len1 = max(datalength(index_name)),
       @len2 = max(datalength(index_description))
	from #spindtab

if (@len1 > 20 or @len2 > 56)
    select table_id = table_id,
    col0=col0,
    index_name = index_name,
    col1=col1,
	index_description = index_description,
    col2=col2,
	index_keys = index_keys,
    col3=col3,
	/*index_max_rows_per_page = index_max_rows_per_page,
    col4=col4,
	index_fillfactor = index_fillfactor,  
    col5=col5,
	index_reservepagegap = index_reservepagegap,
    col6=col6,
	index_created = convert(char(19), index_created, 100),
    col7=col7,*/
    row1=row1
    from #spindtab
else
    select table_id = table_id,
    col0=col0,
    index_name = convert(char(20), index_name),
    col1=col1,
	index_description = convert(char(56), index_description),
    col2=col2,
	index_keys = index_keys,
    col3=col3,
	/*index_max_rows_per_page = index_max_rows_per_page,
    col4=col4,
	index_fillfactor = index_fillfactor, 
    col5=col5, 
	index_reservepagegap = index_reservepagegap,
    col6=col6,
	index_created = convert(char(19), index_created, 100),
    col7=col7,*/
    row1=row1
    from #spindtab

drop table #spindtab

/*  
** Print sysattributes data if there is any.  The join with
** master..sysattributes is to get the string descriptions for the
** class (master.dbo.sysattributes cn) and attribute
** (master.dbo.sysattributes an). These should never be more than
** 30 characters, so it's okay to truncate them to 30.
*/

/*if exists (select * from #spindattr)
begin 
	select name = a.name, attribute_class = convert(char(30),cn.char_value),
		attribute = convert(char(30),an.char_value), a.int_value, 
		a.char_value, a.comments
	from #spindattr a, master.dbo.sysattributes cn,
		master.dbo.sysattributes an
	where a.class = cn.object
	and a.attribute = an.object_info1
	and a.class = an.object
	and cn.class = 0
	and cn.attribute = 0
	and an.class = 0
	and an.attribute = 1
end*/
drop table #spindattr
goto loop_next 
loop_next: 
fetch my_cursor1 into @table_name
end
close my_cursor1
go