IF OBJECT_ID('sp_desc') IS NOT NULL
BEGIN
    DROP PROCEDURE sp_desc
    IF OBJECT_ID('sp_desc') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE sp_desc >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE sp_desc >>>'
END
go

create procedure sp_desc @table_name char(30) = NULL
--
-- Snarfed from CDS, cannot remember who posted the original.
-- Update for dec and numeric data types, plus ensured that
-- varchars came out as that.
--
-- David Owen 2001 (dowen@midsomer.org)

as
  -- This stored procedure returns a description of a SQL Server table in
  -- a format more like the Oracle DESC command.

    if (@table_name IS NULL)
    begin
        raiserror 20001 "Must specify table name for sp_desc!"
        return
    end

    declare @min_id int

    select
        C.colid    'column_id',
        C.name     'column_name',
        T.name     'column_type',
        T.usertype 'user_type',
        T.type     'base_type',
        C.length   'column_length',
        C.scale    'column_scale',
        C.status   'column_is_null'
    into
        #tab_descr
    from
        syscolumns C,
        sysobjects O,
        systypes   T
    where
        C.id       = O.id
    and C.usertype = T.usertype
    and O.name     = @table_name

    if (@@rowcount = 0)
    begin
        raiserror 20001 "Table specified does not exist"
        return
    end

    update
        #tab_descr
    set
        user_type = systypes.usertype
    from
        systypes
    where
        systypes.type     = #tab_descr.base_type
    and systypes.usertype < 100

--    update
--        #tab_descr
--    set
--        column_type = name
--    from
--        systypes
--    where
--        #tab_descr.user_type = systypes.usertype

    update
        #tab_descr
    set
        column_type = name
    from
        systypes   st,
        #tab_descr td
    where td.base_type = st.type
      and td.user_type > 100

    update
        #tab_descr
    set
        column_type = column_type + "(" + LTRIM(RTRIM(str(column_length)))+")"
    where
        column_type in ("char", "varchar", "nchar", "nvarchar", "binary", "varbinary")

    update
        #tab_descr
    set
        column_type = column_type + "(" +
                                     LTRIM(RTRIM(str(column_length))) +
                                     "," +
                                     LTRIM(RTRIM(str(column_scale))) +
                                     ")"
    where
        column_type in ("dec", "numeric", "decimal")

--    update
--        #tab_descr
--    set
--        column_type = "varchar("+LTRIM(RTRIM(str(column_length)))+")"
--    where
--        column_type = "sysname"

    select
        @min_id = min(column_id)
    from
        #tab_descr

    update
        #tab_descr
    set
        column_id = column_id - @min_id + 1

    print @table_name

    select
        convert(char(5), "("+LTRIM(str(column_id))+")") 'No.',
        column_name                                     'Column Name',
        convert(char(20), column_type)                  'Datatype',
        case column_is_null
        when 0 then "NOT NULL"
        else ""
        end
    from
        #tab_descr
    order by column_id
go

IF OBJECT_ID('dbo.sp_desc') IS NOT NULL
BEGIN
    PRINT '<<< CREATED PROCEDURE dbo.sp_desc >>>'
    GRANT EXECUTE ON dbo.sp_desc TO public
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_desc >>>'
go
