sqlsavior.com
create procedure list                -- created by: fred.williams <at> sqlsavior.com
@table_name varchar(60) = null,
@option varchar(100) = null
as
begin

set nocount on

declare @table_id                int
declare @sysobj_type             char(2)
declare @msg                     varchar(1000)
declare @column_name             varchar(61)
declare @last_column_name        varchar(60)
declare @column_id               int
declare @datatype                varchar(17)
declare @is_nullable_flag        char(1)
declare @is_identity_flag        char(1)
declare @default_expression      varchar(255)
declare @found_default_flag      char(1)
declare @constraint_expression   varchar(500)
declare @i                       int
declare @crlf                    char(2)
declare @tab                     char(1)

select @crlf = char(13) + char(10)
select @tab = char(9)

if @table_name is null
or (@option is not null
and @table_name <> 'tables'
and @table_name <> 'procedures'
and @table_name <> 'databases'
and @option not in ('@', '@=', '=@', 'isnull', 'types', 'defaults'))
  begin
  select
@msg = 'usage:' + @tab
   + 'list table_name                (output like:  column_name,' + @crlf + 'or' + @tab
   + 'list procedure_name                (output like:  @parameter_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''@''               (output like:  @parameter_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''@=''              (output like:  @parameter_name = column_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''=@''              (output like:  column_name = @parameter_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''isnull''          (output like:  column_name = isnull(@parameter_name, column_name),' + @crlf
+ 'or' + @tab
   + 'list table_name, types             (output like:  @parameter_name   datatype,' + @crlf + 'or' + @tab
   + 'list table_name, defaults          (output like:  @parameter_name   datatype [= default],' + @crlf + 'or' + @tab
   + 'list tables [, ''prepend_text'']     (output like:  [prepend_text] table_name' + @crlf + 'or' + @tab
   + 'list procedures [, ''prepend_text''] (output like:  [prepend_text] procedure_name' + @crlf + 'or' + @tab
   + 'list databases [, ''prepend_text'']  (output like:  [prepend_text] database_name' + @crlf
   
  print @msg
  return
  end

if @table_name = 'tables' and (@option is null or rtrim(@option) = '')
  begin
  select convert(varchar(60), name) as 'table' from sysobjects where type = 'U' and name not like 'dt_%' order by name
  return
  end

if @table_name = 'tables' and @option is not null and rtrim(@option) <> ''
  begin
  select @option + ' ' + convert(varchar(60), name) as 'table' from sysobjects where type = 'U' and name not like 'dt_%'
order by name
  return
  end

if @table_name = 'procedures' and (@option is null or rtrim(@option) = '')
  begin
  select convert(varchar(60), name) as 'procedure' from sysobjects where type = 'P' and name not like 'dt_%' order by
name
  return
  end

if @table_name = 'procedures' and @option is not null and rtrim(@option) <> ''
  begin
  select @option + ' ' + convert(varchar(60), name) as 'procedure' from sysobjects where type = 'P' and name not like
'dt_%' order by name
  return
  end

if @table_name = 'databases' and (@option is null or rtrim(@option) = '')
  begin
  select convert(varchar(60), name) as 'database' from sys.databases order by name
  return
  end

if @table_name = 'databases' and @option is not null and rtrim(@option) <> ''
  begin
  select @option + ' ' + convert(varchar(60), name) as 'database' from sys.databases order by name
  return
  end

-- OK, it's an object then...

select @table_id = id, @sysobj_type = xtype from sysobjects where id = object_id(@table_name)

if @sysobj_type is null or (@sysobj_type not in ('U ', 'V', 'P '))

  begin
  select @msg = 'No user table, view, or procedure named ' + @table_name + '.'
  print (@msg)
  return
  end

if @option is null
  begin

  if @sysobj_type = 'U ' or @sysobj_type = 'V '
          select convert(varchar(61), c.name + ',') as 'column name'
          from syscolumns c
          where c.id = @table_id
          order by colid
  else if @sysobj_type = 'P '
          select convert(varchar(62), c.name + ',') as 'parameter name'
          from syscolumns c
          where c.id = @table_id                -- actually an object id for procedure
          order by colid

  return
  end

if @sysobj_type <> 'U ' and @option is not null
  begin
  select @msg = 'Option ' + @option + ' is for use with user tables only.'
  print @msg
  return
  end

if @option = '@'        -- list table columns as parameters
  begin
  select convert(varchar(62), '@' + c.name + ',') as 'column/parameter name'
   from syscolumns c
   where c.id = @table_id
   order by colid
  return
  end

if @option = '@='        -- list in '@param = column' form
  begin
  select convert(varchar(125), '@' + c.name + ' = ' + c.name + ',') as '@parameter = column_name'
   from syscolumns c
   where c.id = @table_id
   order by colid
  return
  end

if @option = '=@'        -- list in 'column = @param' form
  begin
  select convert(varchar(125), c.name + ' = @' + c.name + ',') as 'column_name = @parameter'
   from syscolumns c
   where c.id = @table_id
   order by colid
  return
  end

if @option = 'isnull'        -- list in 'column = isnull(@param, column)' form
  begin
  select convert(varchar(190), c.name + ' = isnull(@' + c.name + ', ' + c.name + '),') as 'column = isnull(@parameter,
column)'
   from syscolumns c
   where c.id = @table_id
   order by colid
  return
  end

if @option = 'types'        -- list in '@param    datatype' form
  begin
  select
          convert(varchar(61), '@' + c.name) as 'column/parameter name',
          case t.name
           when 'varchar' then convert(varchar(18), 'varchar(' + convert(varchar, c.length) + '),' )
           when 'nvarchar' then convert(varchar(18), 'nvarchar(' + convert(varchar, c.length/2) + '),' )
           when 'numeric' then convert(varchar(18), 'numeric(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) +
'),' )
           when 'decimal' then convert(varchar(18), 'decimal(' + convert(varchar, c.prec) + ',' + convert(varchar, c.scale) +
'),' )
           when 'char' then convert(varchar(18), 'char(' + convert(varchar, c.length) + '),' )
           when 'nchar' then convert(varchar(18), 'nchar(' + convert(varchar, c.length/2) + '),' )
           when 'binary' then convert(varchar(18), 'binary(' + convert(varchar, c.length) + '),' )
           when 'varbinary' then convert(varchar(18), 'varbinary(' + convert(varchar, c.length) + '),' )
           else convert(varchar(18), t.name + ',')                     
          end as 'type'
  from syscolumns c, systypes t
  where c.id = @table_id
  and c.xtype = t.xtype
  and t.xtype = t.xusertype
  order by colid

  return
  end

if @option = 'defaults'        -- list suggested defaults too   (@param   datatype [= default])
  begin

  create table #columns
  (
  column_name             varchar(61)        not null,
  column_id               int                not null,
  datatype                varchar(17)        not null,
  is_nullable_flag        char(1)            not null,
  is_identity_flag        char(1)            not null,
  default_expression      varchar(255)       null
  )

  create unique index temp_column_index on #columns (column_id)

  insert #columns
          (
          column_name,
          column_id,
          datatype,
          is_nullable_flag,
          is_identity_flag,
          default_expression
          )
  select
          convert(varchar(61), c.name),
          colid,
          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,
          case isnullable when 1 then 'T' else 'F' end,
          case colstat & 1 when 1 then 'T' else 'F' end,
          null
  from syscolumns c, systypes t
  where c.id = @table_id
  and c.xtype = t.xtype
  and t.xtype = t.xusertype
  order by colid

  -- find any column defaults whose expression is legal as a parameter default expression

  declare temp_column_cursor scroll cursor for
          select column_name, column_id from #columns order by column_id

  open temp_column_cursor

  fetch temp_column_cursor into @column_name, @column_id
  while @@fetch_status >= 0
          begin
          -- look for a default constraint first
          select @constraint_expression = null
          select @found_default_flag = 'F'

          select        @constraint_expression = convert(varchar(255), text)
           from        sysobjects, syscomments
           where        sysobjects.parent_obj = @table_id
           and        sysobjects.id = syscomments.id
           and        sysobjects.xtype = 'D '
           and        col_name(@table_id, info) = @column_name
           and        syscomments.colid = 1        -- first line of text only

          if @constraint_expression is not null
                  select @found_default_flag = 'T'

          if datalength(@constraint_expression) between 3 and 254
                  begin
                  -- trim off surrounding parenthesis
                  select @constraint_expression =
                   substring(@constraint_expression, 2, (datalength(@constraint_expression) - 2))
                  end

          -- if no constraint style default found, look for an old-style bound default

          if @found_default_flag = 'F'
                  begin
                  select @constraint_expression = null

                  select        @constraint_expression = convert(varchar(255), text)
                   from        syscolumns c,syscomments m
                   where        c.id = @table_id
                   and        m.id = c.cdefault
                   and        ObjectProperty(c.cdefault, 'IsConstraint') = 0
                   and        c.name = @column_name

                  if @constraint_expression is not null
                          begin
                          select @found_default_flag = 'T'
                          select        @constraint_expression =
                                  substring(@constraint_expression, patindex('% as %', @constraint_expression) + 4,
                                  datalength(@constraint_expression) - patindex('% as %', @constraint_expression) + 4)
                          end

                  end

          select @constraint_expression =
                  rtrim(replace(replace(@constraint_expression, '' + char(10), ' '), '' + char(13), ''))

          -- check for some column default expressions that are illegal as parameter default expressions
          -- update the #columns table if it seems OK

          if @found_default_flag = 'T'
             and @constraint_expression <> 'getdate()'                -- illegal
             and @constraint_expression <> 'current_timestamp'        -- illegal
             and (substring(@constraint_expression, 1, 1) = ''''        -- strings presumed OK
              or  charindex('(', @constraint_expression) = 0)        -- non-strings containing a '(' presumed illegal
                  begin

                  update        #columns
                   set        default_expression = @constraint_expression
                   where        column_id = @column_id

                  end

          fetch temp_column_cursor into @column_name, @column_id
          end

  close temp_column_cursor
  deallocate temp_column_cursor

  select @last_column_name = column_name from #columns where column_id =
          (select max(column_id) from #columns)

  declare column_cursor scroll cursor for
          select        column_name,
                  datatype,
                  is_nullable_flag,
                  is_identity_flag,
                  default_expression
           from        #columns
           order by column_id

  open column_cursor

  fetch first from column_cursor
   into        @column_name, @datatype, @is_nullable_flag, @is_identity_flag, @default_expression

  -- print the parameter list

  select @msg = 'parameter                                                       datatype [=default]' + @crlf
              + '--------------------------------------------------------------- -------------------' + @crlf
  print @msg

  while @@fetch_status >= 0
          begin

          select @msg = '@' + @column_name
          select @i = datalength(@msg)
          while @i < 64
                  begin
                  select @msg = @msg + ' '
                  select @i = datalength(@msg)
                  end

          select @msg = @msg + @datatype
          if @is_nullable_flag = 'T' and @default_expression is null
                  select @msg = @msg + ' = NULL'
          if @default_expression is not null
                  select @msg = @msg + ' = ' + @default_expression
          if @is_identity_flag = 'T'
                  select @msg = @msg + ' OUTPUT'
          if @column_name <> @last_column_name
                  select @msg = @msg + ','
          select @msg = @msg + @crlf
          print @msg

          fetch next from column_cursor
           into        @column_name, @datatype, @is_nullable_flag, @is_identity_flag, @default_expression

          end        -- printing parameter list

  close column_cursor
  deallocate column_cursor

  end        -- list with defaults
else
  begin
  select
@msg = 'usage:' + @tab
   + 'list table_name                (output like:  column_name,' + @crlf + 'or' + @tab
   + 'list procedure_name                (output like:  @parameter_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''@''               (output like:  @parameter_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''@=''              (output like:  @parameter_name = column_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''=@''              (output like:  column_name = @parameter_name,' + @crlf + 'or' + @tab
   + 'list table_name, ''isnull''          (output like:  column_name = isnull(@parameter_name, column_name),' + @crlf
+ 'or' + @tab
   + 'list table_name, types             (output like:  @parameter_name   datatype,' + @crlf + 'or' + @tab
   + 'list table_name, defaults          (output like:  @parameter_name   datatype [= default],' + @crlf + 'or' + @tab
   + 'list tables [, ''prepend_text'']     (output like:  [prepend_text] table_name' + @crlf + 'or' + @tab
   + 'list procedures [, ''prepend_text''] (output like:  [prepend_text] procedure_name' + @crlf + 'or' + @tab
   + 'list databases [, ''prepend_text'']  (output like:  [prepend_text] database_name' + @crlf
  print @msg
  end

end        -- list
go


grant execute on list to public
go
list.sql - procedure to list columns or objects in several formats

usage:        list table_name           (output like:  column_name,
or        list procedure_name         (output like:  @parameter_name,
or        list table_name, '@'           (output like:  @parameter_name,
or        list table_name, '@='         (output like:  @parameter_name = column_name,
or        list table_name, '=@'         (output like:  column_name = @parameter_name,
or        list table_name, 'isnull'     (output like:  column_name = isnull(@parameter_name, column_name),
or        list table_name, types       (output like:  @parameter_name   datatype,
or        list table_name, defaults  (output like:  @parameter_name   datatype [= default],
or        list tables [, 'prepend_text']                  (output like: [prepend_text] table_name
or        list procedures [, 'prepend_text']        (output like: [prepend_text] procedure_name

Author:              Fred Williams

Directions:        Create this procedure in the model database, and all existing databases. (just highlight and
copy to the clipboard...)