use master
go

-- AJZ 2001/04/02
drop proc sp_tableinfo
go

---------------------------------------------------------------------------------------------
-- This proc displays the column and index information for all tables in the current database
---------------------------------------------------------------------------------------------

create proc sp_tableinfo
	@tablepat sysname = NULL,	-- limit tables displayed based on pattern
	@orderby smallint = NULL, 	-- 'Name' (case insensitive) orders tables by column name
	@tabtype char(1) = NULL		-- Default is user tables	-- AJZ 2001/03/28
as
-- OBTAIN DISPLAY STRINGS FROM spt_values UP FRONT --
declare @no varchar(10), @yes varchar(10), @none varchar(35)
select @no = name from master.dbo.spt_values where type = 'B' and number = 0
select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
select @none = name from master.dbo.spt_values where type = 'B' and number = 2

declare @objname varchar(30)
declare @namelen integer

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(2078) 	-- string build index key list, length = (16*max_id_length)+(15*2)

set nocount on

if @tablepat = "?" or (@tablepat is null and @orderby is null and @tabtype is null)
begin
	select Syntax="sp_tableinfo [ {table name pattern | '' | '?'} [, {order-by-column-name | 0} ] [, ''{table type}'' ]"
	union all
	select Syntax="  - Default {table name pattern} is '%', viz., all tables will be displayed; '?' = help"
	union all	
	select Syntax="  - Default {order-by-column-name} is 0; columns are ordered by id."
	union all
	select Syntax="  - Columns will be ordered by name if {order-by-column-name} is non-zero."
	union all
	select Syntax="  - Table type: U=user tables only (default), S=system tables only, V=views only"

	select Examples="sp_tableinfo 'A%',1  -- display user tables starting with 'A' ordered by column name"
	union all
	select Examples="sp_tableinfo '%[_]%' -- display all user tables with an underscore, e.g., pubs_info"
	union all
	select Examples="sp_tableinfo '?'     -- display this help text (same as 'sp_tableinfo [null[,null[,null]]]')"
	union all
	select Examples="sp_tableinfo 'sysremote%',1,S  -- display only the SYSREMOTE... system tables ordered by column name"

	select Notes="If argument 2 is specified and argument 1 is null or empty, '', then all tables will be selected (pattern = '%')"
	union all
	select Notes="Use wildcards in argument 1, the table name pattern, to determine which tables get selected."
end
else
	if @tabtype is null or @tabtype = '' select @tabtype = "U"	-- Default is user tables  AJZ 2001/03/28
	if @tablepat is null or @tablepat = '' select @tablepat = '%'
	select @objname = Min(o.name)
   	from sysobjects o, master.dbo.spt_values v
   	where o.xtype = substring(v.name,1,2) and v.type = 'O9T' and
        	o.xtype = @tabtype and o.name like + @tablepat and o.name <> 'dtproperties'	-- AJZ 2001/04/02

while @objname is not null 
begin
    print ""
    print "********************"
    print @objname
    print "********************"
    select @objid = Object_Id(@objname)

    -- SET UP NUMERIC TYPES: THESE WILL HAVE NON-BLANK PREC/SCALE 
    declare @numtypes nvarchar(80) select @numtypes=N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'
    declare @cursor CURSOR
    set @cursor = cursor read_only for
	select indid, groupid, name, status from sysindexes
	where id = @objid and indid > 0 and indid < 255 order by indid

    -- INFO FOR EACH COLUMN print ' '
    if @orderby is null select @orderby = 0
    if @orderby > 0
       select colid, 'Column_name'= substring(name,1,30), 
           'Type'= substring(type_name(xusertype),1,20), 
           'Computed'= case when iscomputed = 0 then @no else @yes end, 
           'Length'= convert(int, length), 
           'Prec'= 
            case 
                when charindex(type_name(xtype), @numtypes) > 0 then 
                       convert(char(5),ColumnProperty(id, name, 'precision')) 
                else '     ' 
            end, 
           'Scale'= case when charindex(type_name(xtype), @numtypes) > 0 then convert(char(5),OdbcScale(xtype,xscale)) else '     ' end, 
           'Nullable'= case when isnullable = 0 then @no else @yes end, 
           'TrimTrailingBlanks'= case ColumnProperty(@objid, name, 'UsesAnsiTrim') when 1 then @no when 0 then @yes else '(n/a)' end,         
           'FixedLenNullInSource'= 
            case 
                When type_name(xtype) not in ('varbinary','varchar','binary','char') Then '(n/a)' 
                When status & 0x20 = 0 Then @no 
                Else @yes 
            end
       from syscolumns where id = @objid order by name
    else
       select 'Column_name'= substring(name,1,30), 
          'Type'= substring(type_name(xusertype),1,20), 
          'Computed'= case when iscomputed = 0 then @no else @yes end, 
          'Length'= convert(int, length), 
          'Prec'= 
            case 
                when charindex(type_name(xtype), @numtypes) > 0 then 
                       convert(char(5),ColumnProperty(id, name, 'precision')) 
                else '     ' 
            end, 
          'Scale'= case when charindex(type_name(xtype), @numtypes) > 0 then convert(char(5),OdbcScale(xtype,xscale)) else '     ' end, 
          'Nullable'= case when isnullable = 0 then @no else @yes end, 
          'TrimTrailingBlanks'= case ColumnProperty(@objid, name, 'UsesAnsiTrim') when 1 then @no when 0 then @yes else '(n/a)' end,         
          'FixedLenNullInSource'= 
            case 
                When type_name(xtype) not in ('varbinary','varchar','binary','char') Then '(n/a)' 
                When status & 0x20 = 0 Then @no 
                Else @yes 
            end
       from syscolumns where id = @objid order by colid

       -- IDENTITY COLUMN?
       print ' '
       declare @colname sysname
       select @colname = name from syscolumns where id = @objid and colstat & 1 = 1
       select
          'Identity'= substring(isnull(@colname,'No identity column defined.'),1,40),
          'Seed'= ident_seed(@objname),
          'Increment'= ident_incr(@objname),
          'Not For Replication'= ColumnProperty(@objid, @colname, 'IsIDNotForRepl')

       -- ROWGUIDCOL?
       print ' '
       select @colname = null
       select @colname = name from syscolumns where id = @objid
              and ColumnProperty(@objid, name, 'IsRowGuidCol') = 1
       select 'RowGuidCol' = isnull(@colname,'No rowguidcol column defined.')

       print ""

	-- OPEN CURSOR OVER INDEXES
	open @cursor
	fetch @cursor into @indid, @groupid, @indname, @status

	-- IF NO INDEX, QUIT
	if @@fetch_status = 0
	begin
	-- create temp table
		create table #spindtab
		(
			index_name			sysname	NOT NULL,
			stats				int,
			groupname			sysname NOT NULL,
			index_keys			nvarchar(2078)	NOT NULL
		)

	-- 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 sysname
			select @keys = index_col(@objname, @indid, 1),
				@i = 2, @thiskey = index_col(@objname, @indid, 2)
			while (@thiskey is not null )
			begin
				select @keys = @keys + ', ' + @thiskey, @i = @i + 1
				select @thiskey = index_col(@objname, @indid, @i)
			end

			select @groupname = groupname from sysfilegroups where groupid = @groupid

		-- INSERT ROW FOR INDEX
			insert into #spindtab values (@indname, @status, @groupname, @keys)

		-- Next index
			fetch @cursor into @indid, @groupid, @indname, @status
		end
		close @cursor
		deallocate @cursor

	-- 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' = convert(varchar(30),index_name),
			'index_keys' = convert(varchar(60),index_keys),
			'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)
		from #spindtab
		drop table #spindtab
	end
-- End of index display

	if @objname is not null exec sp_helpconstraint2 @objname, 'nomsg'	-- AJZ 2001/04/02

       	select @objname = Min(o.name)
	   from sysobjects o, master.dbo.spt_values v
	   where o.xtype = substring(v.name,1,2) and v.type = 'O9T' and
	         o.xtype = @tabtype and o.name like @tablepat and o.name> @objname and o.name <> 'dtproperties'

end
go

grant execute on sp_tableinfo to public
go