list

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

   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

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

if @sysobj_type is null or (@sysobj_type <> 'U ' and @sysobj_type <> 'P ')

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

if @option is null
   begin

   if @sysobj_type = 'U '
           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(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'
   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
   print @msg
   end

end        -- list
go

grant execute on list to public
go