SqlSavior.com
create procedure describe
@passed_object_name varchar(60)
as
begin
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
declare @table_id int
declare @table_name varchar(60)
declare @msg varchar(100)
,@cnstdes nvarchar(4000) -- string to build up index desc
,@cnstname sysname -- name of const. currently under consideration
,@i int
,@cnstid int
,@cnsttype character(2)
,@keys nvarchar(2078) --Length (16*max_identifierLength)+(15*2)
,@dbname sysname
,@index_id smallint -- the index id of an index
,@groupid smallint -- the filegroup id of an index
,@indname sysname
,@groupname sysname
,@status int
declare @thiskey sysname
declare @c_expr varchar(4000)
declare @first_equal_index int
declare @column_name varchar(60)
declare @first_left_bracket_index int
declare @first_right_bracket_index int
declare @maxlength int
declare @objid int
declare @sysobj_type char(2)
declare @schema_passed_flag char(1)
declare @matching_object_count int
declare @crlf char(2)
declare @tab char(1)
declare @schema_name varchar(60)
declare @schema_id int
declare @description varchar(60)
declare @object_name varchar(60)
declare @schema_dot_object varchar(100)
select @crlf = char(13) + char(10) -- carriage return, line feed
select @tab = char(9)
-- determine if passed object name is qualified by schema
select @schema_passed_flag = 'F'
select @schema_passed_flag = case charindex('.', @passed_object_name)
when null then 'F'
when 0 then 'F'
else 'T'
end
if @schema_passed_flag = 'F' -- if no schema passed
begin
select @schema_name = NULL
select @schema_id = NULL
select @matching_object_count = count(*)
from sys.all_objects
where name = @passed_object_name
and type in ('U', 'V', 'P')
if @matching_object_count = 0
begin
print 'No object named: ' + @passed_object_name
return
end
if @matching_object_count > 1
begin
print 'Multiple objects named: ' + @passed_object_name + @crlf +
'Try: describe ''schema.object'''
print ''
select convert(varchar(30), s.name) as 'Schema Name',
convert(varchar(32), o.type_desc) as 'Object Type',
convert(varchar(60), o.name) as 'Object Name'
from sys.all_objects o, sys.schemas s
where o.schema_id = s.schema_id
and o.name = @passed_object_name
order by s.name, o.type_desc
return
end
if @matching_object_count = 1
begin
select @schema_name = s.name,
@schema_id = s.schema_id
from sys.schemas s, sys.all_objects o
where s.schema_id = o.schema_id
and o.name = @passed_object_name
and o.type in ('U', 'V', 'P')
select @object_name = @passed_object_name, @schema_dot_object = @schema_name + '.' +
@object_name
-- print '@object_name = ' + @object_name + ', @schema_name = ' + @schema_name +
', @schema_id = ' + convert(varchar, @schema_id)
end
end -- if no schema passed
else
begin -- schema passed
select @schema_name = substring(@passed_object_name, 1, charindex('.', @passed_object_name) - 1)
select @schema_id = null
select @schema_id = schema_id from sys.schemas where name = @schema_name
if @schema_id is null
begin
print 'Schema ' + @schema_name + 'not found in sys.schemas.'
return
end
select @object_name = substring(@passed_object_name, charindex('.', @passed_object_name) +
1, len(@passed_object_name) - (len(@schema_name) + 1))
if @passed_object_name != @schema_name + '.' + @object_name
begin
print 'Huh? @passed_object_name = ' +@passed_object_name + ', @schema_name = ' +
@schema_name + ', @object_name = ' + @object_name
return
end
else
select @schema_dot_object = @passed_object_name
end -- schema passed
select @objid = object_id, @sysobj_type = type from sys.all_objects where object_id =
object_id(@schema_dot_object)
if @sysobj_type in ('P ')
begin
goto display_stored_procedure_info
end
else
begin
select @table_id = @objid
-- preserve capitalization
select @table_name = name from sys.all_objects where name = @object_name and schema_id =
@schema_id
select @object_name = @table_name
end
if @table_id is null
begin
select @msg = 'No object named ' + @table_name + '.'
print (@msg)
return
end
else
begin
if @sysobj_type = 'U '
begin
select @msg = 'Table: ' + @schema_dot_object -- @table_name
-- add filegroup
end
else if @sysobj_type = 'V '
select @msg = 'View: ' + @schema_dot_object -- @table_name
print @msg
print ' '
end
-- print the columns of the table in a format like a create table statement
select
convert(varchar(60), c.name) as 'column name',
case t.name
when 'varchar' then convert(varchar(17), 'varchar(' + convert(varchar, c.max_length) + ')' )
when 'nvarchar' then convert(varchar(17), 'nvarchar(' + convert(varchar, c.max_length/2) + ')' )
when 'numeric' then convert(varchar(17), 'numeric(' + convert(varchar, c.precision) + ',' +
convert(varchar, c.scale) + ')' )
when 'decimal' then convert(varchar(17), 'decimal(' + convert(varchar, c.precision) + ',' +
convert(varchar, c.scale) + ')' )
when 'char' then convert(varchar(17), 'char(' + convert(varchar, c.max_length) + ')' )
when 'nchar' then convert(varchar(17), 'nchar(' + convert(varchar, c.max_length/2) + ')' )
when 'binary' then convert(varchar(17), 'binary(' + convert(varchar, c.max_length) + ')' )
when 'varbinary' then convert(varchar(17), 'varbinary(' + convert(varchar, c.max_length) + ')' )
else convert(varchar(17), t.name)
end as 'type',
case c.is_nullable when 1 then ' NULL,' else 'NOT NULL,' end as 'nulls'
from sys.all_columns c, sys.types t
where c.object_id = @table_id
and c.system_type_id = t.system_type_id
and t.system_type_id = t.user_type_id
order by column_id
if @sysobj_type = 'V ' -- if view, provide definition and return
begin
exec sp_helptext @schema_dot_object
return
end
-- IDENTITY COLUMN?
if @sysobj_type in ('S ','U ','V ','TF')
begin
select @column_name = convert(varchar(60), name) from syscolumns where id = @objid
and colstat & 1 = 1
if @column_name is null
begin
select @msg = 'No identity column defined.'
print @msg
print ''
end
else
begin
select
'identity column' = @column_name,
'seed' = convert(varchar(12), ident_seed(@object_name)),
'increment' = convert(varchar(12), ident_incr(@object_name)),
'not for replication' = ColumnProperty(@objid, @column_name, 'IsIDNotForRepl')
end
end
-- PRINT OUT THE CHECKS FOR THIS TABLE
create table #spcnsttab
(
cnst_id int NOT NULL
,cnst_type nvarchar(146) NOT NULL -- 128 for name + text for DEFAULT
,cnst_name sysname NOT NULL
,cnst_nonblank_name sysname NOT NULL
,cnst_2type character(2) NULL
,cnst_disabled bit NULL
,cnst_notrepl bit NULL
,cnst_keys nvarchar(2078) NULL -- see @keys above for length descr
)
-- STATIC CURSOR OVER THE TABLE'S CONSTRAINTS
declare check_csr insensitive cursor for
select id, xtype, name from sysobjects where parent_obj = @table_id
and xtype in ('C ')
for read only
-- Now check out each constraint, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
open check_csr
fetch check_csr into @cnstid ,@cnsttype ,@cnstname
if @@fetch_status < 0
begin
print 'No CHECK constraints found for this table.'
end
else
begin
print 'CHECK CONSTRAINTS:'
print ' '
while @@fetch_status >= 0
begin
select @i = 1
select @cnstdes = text from syscomments where id = @cnstid and colid = @i
-- reformat certain check expressions to form: column_name in ('a', 'b', 'c')
select @c_expr = @cnstdes
select @first_left_bracket_index = charindex('[', @c_expr)
select @first_right_bracket_index = charindex(']', @c_expr)
if (
@first_left_bracket_index <> 0 and
@first_right_bracket_index <> 0 and
@first_left_bracket_index < @first_right_bracket_index and
patindex('% or %', @c_expr) <> 0 and
patindex('% and %', @c_expr) = 0
)
begin
select @column_name = substring(@c_expr, @first_left_bracket_index + 1,
(@first_right_bracket_index - @first_left_bracket_index) - 1) + ' = '
select @c_expr = replace(@c_expr, '(', '')
select @c_expr = replace(@c_expr, ')', '')
select @c_expr = replace(@c_expr, '[', '')
select @c_expr = replace(@c_expr, ']', '')
select @c_expr = replace(@c_expr, ' or ', ', ')
select @first_equal_index = charindex('=', @c_expr)
select @c_expr = substring(@c_expr, 1, @first_equal_index - 1) + 'in (' +
replace(substring(@c_expr, @first_equal_index + 2,
datalength(@c_expr) - (@first_equal_index + 1)), @column_name, '') + ')'
select @cnstdes = @c_expr
end
while @cnstdes is not null
begin
if @i=1
-- Check constraint
insert into #spcnsttab
(cnst_id, cnst_type ,cnst_name ,cnst_nonblank_name,
cnst_keys, cnst_disabled, cnst_notrepl, cnst_2type)
select @cnstid,
case when info = 0 then 'table'
else 'column' /* + col_name(@table_id ,info) */end,
@cnstname ,@cnstname ,substring(@cnstdes,1,2000),
ObjectProperty(@cnstid, 'CnstIsDisabled'),
ObjectProperty(@cnstid, 'CnstIsNotRepl'),
@cnsttype
from sysobjects where id = @cnstid
else
insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys,
cnst_2type)
select @cnstid,' ' ,' ' ,@cnstname ,substring(@cnstdes,1,2000), @cnsttype
if len(@cnstdes) > 2000
insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys,
cnst_2type)
select @cnstid,' ' ,' ' ,@cnstname ,substring(@cnstdes,2001,2000), @cnsttype
select @cnstdes = null
select @i = @i + 1
select @cnstdes = text from syscomments where id = @cnstid and colid = @i
end
fetch check_csr into @cnstid ,@cnsttype ,@cnstname
end
end
deallocate check_csr
-- Now print out the contents of the temporary index table.
if exists (select * from #spcnsttab)
select
convert(varchar(8), cnst_type) as 'type',
convert(varchar(30), cnst_name) as 'constraint name',
convert(varchar(100), cnst_keys) as 'constraint expression'
from #spcnsttab order by cnst_nonblank_name ,cnst_name desc
print ' '
delete from #spcnsttab -- need to delete because we reuse the table for other constraints later
-- PRINT OUT BOUND RULES AND DEFAULTS
-- Find any rules or defaults bound by the sp_bind... method.
declare @bound_count int
select @bound_count = count(*)
from syscolumns c, syscomments m
where c.id = @objid and m.id = c.domain and ObjectProperty(c.domain, 'IsRule') = 1
if @bound_count > 0
begin
print 'RULES (bound with sp_bindrule):'
print ''
select convert(varchar(40), c.name) as 'column',
replace(replace(convert(varchar(120), text), '' + char(10), ' '), '' + char(13), '') as 'rule definition'
from syscolumns c, syscomments m
where c.id = @objid and m.id = c.domain and ObjectProperty(c.domain, 'IsRule') = 1
end
select @bound_count = count(*)
from syscolumns c, syscomments m
where c.id = @objid and m.id = c.cdefault and ObjectProperty(c.cdefault, 'IsConstraint') = 0
if @bound_count > 0
begin
print 'DEFAULTS (bound with sp_bindefault):'
print ''
select convert(varchar(40), c.name) as 'column',
replace(replace(convert(varchar(120), text), '' + char(10), ' '), '' + char(13), '') as 'default definition'
from syscolumns c,syscomments m
where c.id = @objid and m.id = c.cdefault and ObjectProperty(c.cdefault, 'IsConstraint') = 0
end
-- PRINT OUT THE INDEXES FOR THIS TABLE
-- OPEN CURSOR OVER INDEXES
declare ind_curs insensitive cursor for
select index_id,
convert(varchar(33), type_desc
+ case is_unique when 1 then ', UNIQUE' else '' end
+ case is_primary_key when 1 then ', PRIMARY KEY' else '' end)
as 'Description',
name
from sys.indexes
where object_id = @table_id
and index_id > 0
and index_id < 255
order by index_id
open ind_curs
fetch ind_curs into @index_id, @description, @indname
-- IF NO INDEX, QUIT
if @@fetch_status < 0
begin
deallocate ind_curs
--raiserror(15472,-1,-1) --'Object does not have any indexes.'
print 'No indexes exist on this table.'
print ' '
end
else
begin
-- create temp table
create table #spindtab
(
index_name sysname NOT NULL,
index_desc varchar(60) NOT NULL,
--stats int,
--groupname sysname NOT NULL,
index_keys varchar(1000) NOT NULL,
--index_include_columns varchar(1000) 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
insert #spindtab select @indname, @description, 'on ('
-- OPEN CURSOR OVER INDEX COLUMNS
declare ind_col_curs insensitive cursor for
select c.name +
case ic.is_descending_key when 1 then '(desc)' else '' end +
case ic.is_included_column when 1 then '(leaf only)' else '' end
from sys.index_columns ic,
sys.columns c
where c.object_id = ic.object_id
and c.column_id = ic.column_id
and c.object_id = @table_id
and ic.index_id = @index_id
order by case ic.key_ordinal when 0 then 255 else ic.key_ordinal end
open ind_col_curs
fetch ind_col_curs into @column_name
if @@fetch_status >= 0
begin
update #spindtab set index_keys = index_keys + @column_name
where index_name = @indname
fetch ind_col_curs into @column_name
end
while @@fetch_status >= 0
begin
update #spindtab set index_keys = index_keys + ', ' + @column_name
where index_name = @indname
fetch ind_col_curs into @column_name
end -- while more columns in index
update #spindtab set index_keys = index_keys + ')'
where index_name = @indname
close ind_col_curs
deallocate ind_col_curs
fetch ind_curs into @index_id, @description, @indname
end
close ind_curs
deallocate ind_curs
select convert (varchar(35),index_name) + ': ' + index_desc + @crlf + @tab +
convert (varchar(120), index_keys) +
case when datalength(index_keys) > 120 then '!TRUNCATED!' else '' end as 'INDEXES:'
from #spindtab
end -- else, indexes found
-- PRINT CONSTRAINT LIST
-- STATIC CURSOR OVER THE TABLE'S CONSTRAINTS
declare cnst_csr insensitive cursor for
select id, xtype, name from sysobjects where parent_obj = @table_id
and xtype in ('PK','UQ','F ', 'D ') -- ONLY 6.5 sysconstraints objects
for read only
-- Now check out each constraint, figure out its type and keys and
-- save the info in a temporary table that we'll print out at the end.
open cnst_csr
fetch cnst_csr into @cnstid ,@cnsttype ,@cnstname
while @@fetch_status >= 0
begin
if @cnsttype in ('PK','UQ')
begin
-- get index_id and index description
select @index_id = indid, --index_id,
@cnstdes = case when @cnsttype = 'PK'
then 'PRIMARY KEY' else 'UNIQUE' end
+ case when (status & 16)=16
then ' (clustered)' else ' (non-clustered)' end
from sysindexes
where name = object_name(@cnstid)
and id = @table_id
-- Format keys string
select @keys = index_col(@table_name, @index_id, 1), @i = 2,
@thiskey = index_col(@table_name, @index_id, 2)
while (@thiskey is not null )
begin
select @keys = @keys + ', ' + @thiskey, @i = @i + 1
select @thiskey = index_col(@table_name, @index_id, @i)
end
-- ADD TO TABLE
insert into #spcnsttab
(cnst_id,cnst_type,cnst_name, cnst_nonblank_name,cnst_keys, cnst_2type)
values (@cnstid, @cnstdes, @cnstname, @cnstname, @keys, @cnsttype)
end
else
if @cnsttype = 'F '
begin
-- OBTAIN TWO TABLE IDs
declare @fkeyid int, @rkeyid int
select @fkeyid = fkeyid, @rkeyid = rkeyid from sysreferences where constid = @cnstid
-- USE CURSOR OVER FOREIGN KEY COLUMNS TO BUILD COLUMN LISTS
-- (NOTE: @keys HAS THE FKEY AND @cnstdes HAS THE RKEY COLUMN LIST)
declare fkey_curs cursor for select fkey, rkey from sysforeignkeys where constid = @cnstid
open fkey_curs
declare @fkeycol smallint, @rkeycol smallint
fetch fkey_curs into @fkeycol, @rkeycol
select @keys = col_name(@fkeyid, @fkeycol), @cnstdes = col_name(@rkeyid, @rkeycol)
fetch fkey_curs into @fkeycol, @rkeycol
while @@fetch_status >= 0
begin
select @keys = @keys + ', ' + col_name(@fkeyid, @fkeycol),
@cnstdes = @cnstdes + ', ' + col_name(@rkeyid, @rkeycol)
fetch fkey_curs into @fkeycol, @rkeycol
end
deallocate fkey_curs
-- ADD ROWS FOR BOTH SIDES OF FOREIGN KEY
insert into #spcnsttab
(cnst_id, cnst_type,cnst_name,cnst_nonblank_name,
cnst_keys, cnst_disabled,
cnst_notrepl, cnst_2type)
values
(@cnstid,
'FOREIGN KEY ' +
case ObjectProperty(@cnstid, 'CnstIsDeleteCascade')
when 1 then '(cascade)'
else '(restrict)'
end,
@cnstname, @cnstname,
@keys, ObjectProperty(@cnstid, 'CnstIsDisabled'),
ObjectProperty(@cnstid, 'CnstIsNotRepl'), @cnsttype)
insert into #spcnsttab
(cnst_id,cnst_type,cnst_name,cnst_nonblank_name,
cnst_keys,
cnst_2type)
select
@cnstid,' REFERENCES', ' ', @cnstname,
rtrim(user_name(ObjectProperty(@rkeyid,'ownerid')))
+ '.' + object_name(@rkeyid) + ' ('+@cnstdes + ')',
@cnsttype
end
else
if (@cnsttype = 'D ')
begin
select @i = 1
select @cnstdes = text from syscomments where id = @cnstid and colid = @i
while @cnstdes is not null
begin
if @i=1
insert into #spcnsttab
(cnst_id,cnst_type ,cnst_name ,cnst_nonblank_name ,cnst_keys, cnst_2type)
select @cnstid, 'DEFAULT on ' + col_name(@table_id ,info),
@cnstname ,@cnstname ,@cnstdes, @cnsttype
from sysobjects where id = @cnstid
else
insert into #spcnsttab (cnst_id,cnst_type,cnst_name,cnst_nonblank_name,cnst_keys,
cnst_2type)
select @cnstid,' ' ,' ' ,@cnstname ,@cnstdes, @cnsttype
select @i = @i + 1
select @cnstdes = null
select @cnstdes = text from syscomments where id = @cnstid and colid = @i
end
end
fetch cnst_csr into @cnstid ,@cnsttype ,@cnstname
end --of major loop
deallocate cnst_csr
-- Now print out the contents of the temporary index table.
if exists (select * from #spcnsttab)
begin
print 'OTHER CONSTRAINTS:'
print ' '
select
convert(varchar(35), cnst_name) as 'constraint name',
convert(varchar(45), cnst_type) as 'constraint type',
convert(varchar(80), cnst_keys) as 'constraint keys'
from #spcnsttab order by cnst_nonblank_name ,cnst_name desc
end
else
begin
/*raiserror(15469,-1,-1) --*/ print 'No other constraints have been defined for this table.'
print ' '
end
-- PRINT FORIEGN KEYS
if exists (select * from sysreferences where rkeyid = @table_id)
begin
print 'TABLE IS REFERENCED BY:'
print ' '
select convert(varchar(50), /* db_name() + '.' */
+ rtrim(user_name(ObjectProperty(fkeyid,'ownerid')))
+ '.' + object_name(fkeyid)) as 'table',
convert(varchar(60), object_name(constid)
+ case ObjectProperty(constid, 'CnstIsDeleteCascade')
when 1 then ' (cascade)'
else ' (restrict)'
end) as 'constraint'
from sysreferences where rkeyid = @table_id order by 1
end
else
print 'No foreign keys reference this table.'
return(0) -- done with table info
display_stored_procedure_info:
-- ANY PARAMS FOR THIS PROC?
if exists (select * from sys.parameters where object_id = @objid)
begin
-- INFO ON PROC PARAMS
select @msg = 'PARAMETERS for procedure ' + @object_name + ':'
print @msg
print ' '
select
convert(varchar(60), c.name) as 'parameter name',
case t.name
when 'varchar' then convert(varchar(17), 'varchar(' + convert(varchar,
c.max_length) + ')' )
when 'nvarchar' then convert(varchar(17), 'nvarchar(' + convert(varchar,
c.max_length/2) + ')' )
when 'numeric' then convert(varchar(17), 'numeric(' + convert(varchar,
c.precision) + ',' + convert(varchar, c.scale) + ')' )
when 'decimal' then convert(varchar(17), 'decimal(' + convert(varchar,
c.precision) + ',' + convert(varchar, c.scale) + ')' )
when 'char' then convert(varchar(17), 'char(' + convert(varchar, c.max_length)
+ ')' )
when 'nchar' then convert(varchar(17), 'nchar(' + convert(varchar,
c.max_length/2) + ')' )
when 'binary' then convert(varchar(17), 'binary(' + convert(varchar,
c.max_length) + ')' )
when 'varbinary' then convert(varchar(17), 'varbinary(' + convert(varchar,
c.max_length) + ')' )
else convert(varchar(17), t.name)
end as 'type',
case c.is_output when 1 then 'output' else '' end as 'output',
case has_default_value when 1 then convert(varchar, default_value) else '' end as 'default'
from sys.parameters c, sys.types t
where c.object_id = @objid
and c.system_type_id = t.system_type_id
and t.system_type_id = t.user_type_id
and c.parameter_id <> 0
order by parameter_id
end
else
begin
-- INFO ON PROC PARAMS
select @msg = 'No parameters for procedure: ' + @object_name
print @msg
print ' '
end
exec sp_helptext @object_name
end -- end of create procedure describe
describe2005.sql - an improvement on sp_help, sql 2005 version (preliminary)
usage: describe table_name
or describe view_name
or describe procedure_name
Author: Fred Williams
Directions: Create this procedure in the model database, and all existing databases. (just highlight and
copy to the clipboard...)
