describe

create procedure describe
@object_name varchar(60)
as
begin

    SET NOCOUNT ON

    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
                    ,@indid                 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)

    select @objid = id, @sysobj_type = xtype from sysobjects where id = object_id(@object_name)

    if @sysobj_type in ('P ')
            begin
            goto display_stored_procedure_info
            end
    else if @objid is null
            begin
            print 'No object named: ' + @object_name
            return
            end
    else
            begin
            select @table_id = @objid
            select @i = charindex('.', @object_name)
            if @i = 0        -- @object_name is NOT in form: owner.name
                  begin
                  select @table_name = name from sysobjects where name = @object_name -- preserve capitalization
                  select @object_name = @table_name
                  end
            else        -- @object_name is in form: owner.name
                  select @table_name = @object_name
            end

    if @table_id is null
            begin
            select @msg = 'No table named ' + @table_name + '.'
            print (@msg)
            return
            end
    else
            begin
            if @sysobj_type = 'U '
                    begin
                    select @msg = 'Table: ' + @table_name
                    -- add filegroup
                    end
            else if @sysobj_type = 'V '
                    select @msg = 'View: ' + @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.length) + ')' )
                    when 'nvarchar' then convert(varchar(17), 'nvarchar(' + convert(varchar, c.length/2) + ')' )
                    when 'numeric' then convert(varchar(17), 'numeric(' + convert(varchar, c.prec) + ',' + convert(varchar,
c.scale) + ')' )
                    when 'decimal' then convert(varchar(17), 'decimal(' + convert(varchar, c.prec) + ',' + convert(varchar,
c.scale) + ')' )
                    when 'char' then convert(varchar(17), 'char(' + convert(varchar, c.length) + ')' )
                    when 'nchar' then convert(varchar(17), 'nchar(' + convert(varchar, c.length/2) + ')' )
                    when 'binary' then convert(varchar(17), 'binary(' + convert(varchar, c.length) + ')' )
                    when 'varbinary' then convert(varchar(17), 'varbinary(' + convert(varchar, c.length) + ')' )
                    else convert(varchar(17), t.name)
            end as 'type',
            case isnullable when 1 then '    NULL,' else 'NOT NULL,' end as 'nulls'
    from syscolumns c, systypes t
    where c.id = @table_id
    and c.xtype = t.xtype
    and t.xtype = t.xusertype
    order by colid

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

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

    -- OPEN CURSOR OVER INDEXES
    declare ind_curs insensitive cursor for
            select indid, groupid, name, status from sysindexes
                    where id = @table_id and indid > 0 and indid < 255 order by indid
    open ind_curs
    fetch ind_curs into @indid, @groupid, @indname, @status

    -- 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,
                    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.
                    select @keys = index_col(@table_name, @indid, 1),
                                    @i = 2, @thiskey = index_col(@table_name, @indid, 2)
                    while (@thiskey is not null )
                    begin
                            select @keys = @keys + ', ' + @thiskey, @i = @i + 1
                            select @thiskey = index_col(@table_name, @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 ind_curs into @indid, @groupid, @indname, @status
            end
            deallocate ind_curs

            print 'INDEXES:'
            print ' '

            select
                    convert (varchar(35),index_name) as 'index name',
                    convert (varchar(64), index_keys) as 'index keys',
                    'properties' = convert (varchar(64), --bits 16 off, 1, 2, 16777216 on, located on group
                            case when (i.stats & 16)<>0 then 'clustered' else 'nonclustered' end
                            + case when (i.stats & 1)<>0 then ', '+@des1 else @empty end
                            + case when (i.stats & 2)<>0 then ', '+@des2 else @empty end
                            + case when (i.stats & 4)<>0 then ', '+@des4 else @empty end
                            + case when (i.stats & 64)<>0 then ', '+@des64 else case when (i.stats & 32)<>0 then ',
'+@des32 else @empty end end
                            + case when (i.stats & 2048)<>0 then ', '+@des2048 else @empty end
                            + case when (i.stats & 4096)<>0 then ', '+@des4096 else @empty end
                            + case when (i.stats & 8388608)<>0 then ', '+@des8388608 else @empty end
                            + case when (i.stats & 16777216)<>0 then ', '+@des16777216 else @empty end
                            + case when (i.groupname = 'PRIMARY') then @empty else ' on filegroup ' + i.groupname
end)

            from #spindtab i where (i.stats & 64) = 0   -- leave out statistics indexes

    end -- else

-- 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 indid and index description
                    select        @indid = indid,
                                  @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, @indid, 1), @i = 2,
                                    @thiskey = index_col(@table_name, @indid, 2)
                    while (@thiskey is not null )
                    begin
                            select @keys = @keys + ', ' + @thiskey, @i = @i + 1
                            select @thiskey = index_col(@table_name, @indid, @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
            raiserror(15470,-1,-1) --'No foreign keys reference this table.'

    if @sysobj_type = 'V '                        -- if view, provide definition
            exec sp_helptext @table_name

    return(0)        -- done with table info

display_stored_procedure_info:

    -- ANY PARAMS FOR THIS PROC?
    if exists (select id from syscolumns where id = @objid)
            begin

            -- INFO ON PROC PARAMS
            select @msg = 'PARAMETERS for procedure ' + @object_name + ':'
            print @msg
            print ' '
            select convert(varchar(50), c.name) as 'parameter name',
            case t.name
             when 'varchar' then convert(varchar(17), 'varchar(' + convert(varchar, c.length) + ')' )
             when 'nvarchar' then convert(varchar(17), 'nvarchar(' + convert(varchar, c.length/2) + ')' )
             when 'numeric' then convert(varchar(17), 'numeric(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale)
+ ')' )
             when 'decimal' then convert(varchar(17), 'decimal(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale)
+ ')' )
             when 'char' then convert(varchar(17), 'char(' + convert(varchar, c.length) + ')' )
             when 'nchar' then convert(varchar(17), 'nchar(' + convert(varchar, c.length/2) + ')' )
             when 'binary' then convert(varchar(17), 'binary(' + convert(varchar, c.length) + ')' )
             when 'varbinary' then convert(varchar(17), 'varbinary(' + convert(varchar, c.length) + ')' )
             else convert(varchar(17), t.name)
             end as 'type',
            case isoutparam when 1 then 'yes' else 'no' end as 'changes caller''s copy'
            from syscolumns c, systypes t
            where c.id = @objid
            and c.xtype = t.xtype
            and t.xtype = t.xusertype
            order by colid

            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
go

grant execute on describe to public
go