sptab -- My replacement for many uses of sp_help

You can use sp_help or SSMS to find out the columns in a table, view or stored procedure parameter list.
But to use sp_help, you need to know the exact name of the object, which I usually refuse to remeber (based on inconsistant naming conventions).

Instead I use some sort of intellisence logic.

If I don't give an exact name, tell me which match, otherwise give me the information I ask for.

And here is my code (requires SQL-2005):

if object_id ('sptab', 'P') > 0     drop procedure sptab
go

create proc dbo.sptab (@BeginOfString varchar(50)= null, @type varchar(10) = '', @sort varchar(10) = 'id')
as

SET NOCOUNT ON

declare @n       varchar(100)
declare @obid    int
declare @tmp_obj table(id_ int, name_ varchar(256), type varchar(5))

if @BeginOfString is null
  begin
    print 'usage sptab [, type ][, sort (id|name|len)]    BK 2010-10-08'
    return 0
  end

if @type = ''
    insert into @tmp_obj select id, name, type from sysobjects where type in ('U', 'V', 'P', 'FN', 'IF') and name = rtrim(@BeginOfString)
else
    insert into @tmp_obj select id, name, type from sysobjects where type = @type and name = rtrim(@BeginOfString)

if @@ROWCOUNT = 1
    select @n = name_, @obid = id_ from @tmp_obj      
else
  begin
    delete @tmp_obj
    if @type = ''
        insert into @tmp_obj select id, name, type from sysobjects where type in ('U', 'V', 'P') and name like rtrim(@BeginOfString) + '%'   
    else
        insert into @tmp_obj select id, name, type from sysobjects where type = @type and name like rtrim(@BeginOfString) + '%'   

    if @@ROWCOUNT = 1               
        select @n = name_, @obid = id_ from @tmp_obj      
    else
        begin
        select type, name_ name from @tmp_obj order by 1
        return 0
        end
  end

print @n

if @sort = 'id'
    select  left (c.name, 40) col, c.colid, left (t.name, 16) type, t.xtype, c.length
     , case when isnullable <> 0 then 'J' else '' end NULLs
     , case when COLUMNPROPERTY(@obid, c.name ,'IsIdentity') <> 0 then 'J' else '' end Id
     , isnull(OBJECT_DEFINITION (c.cdefault), '') [default]
    from syscolumns c
        join systypes t on c.xtype = t.xtype and c.usertype = t.usertype
    where c.id = @obid
    order by c.colid
else if @sort = 'name'
    select  left (c.name, 40) col, c.colid, left (t.name, 16) type, t.xtype, c.length
     , case when isnullable <> 0 then 'J' else '' end NULLs
     , case when COLUMNPROPERTY(@obid, c.name ,'IsIdentity') <> 0 then 'J' else '' end Id   
     , isnull(OBJECT_DEFINITION (c.cdefault), '') [default]
    from syscolumns c
        join systypes t on c.xtype = t.xtype and c.usertype = t.usertype
    where c.id = @obid
    order by col
else if @sort = 'len'
    select  left (c.name, 40) col, c.colid, left (t.name, 16) type, t.xtype, c.length
     , case when isnullable <> 0 then 'J' else '' end NULLs
     , case when COLUMNPROPERTY(@obid, c.name ,'IsIdentity') <> 0 then 'J' else '' end Id   
     , isnull(OBJECT_DEFINITION (c.cdefault), '') [default]
    from syscolumns c
        join systypes t on c.xtype = t.xtype and c.usertype = t.usertype
    where c.id = @obid
    order by length, colid


go