Not logged in - Login

Script to list table fields

Use this script to list the fields of a table:

declare @sTableName sysname
set @sTableName = 'RM00102'

declare @lObjectID int
set @lObjectID = object_id(@sTableName)

-- declare & open a cursor to iterate thru most of the columns
DECLARE curColumns cursor fast_forward for
select 
   xusertype
   , [name]
   , fieldType = 
      case xusertype
         when 56 then 'int'
         when 52 then 'smallint'
         when 48 then 'tinyint'
         when 167 then 'varchar(' + convert(varchar(4), length) + ')'
         when 231 then 'nvarchar(' + convert(varchar(4), length) + ')'
         when 175 then 'char(' + convert(varchar(4), length) + ')'
         when 108 then 'numeric(' + convert(varchar(4), xprec) + ',' + convert(varchar(4), xscale) + ')'
         when 59 then 'real'
         when 35 then 'text'
         when 61 then 'datetime'
         when 60 then 'money'
         when 36 then 'uniqueidentifier'
         when 104 then 'bit'
         when 189 then 'stamp'
         when 173 then 'binary'
         else '<unknown>'
      end
from [syscolumns] with (nolock)
where [id]=@lObjectID
   and (xusertype <> 189)
order by colorder

OPEN curColumns

-- declare variable(s) to hold data from cursor
DECLARE @xusertype smallint
   , @sFieldType varchar(50)
   , @sDataType varchar(50)
   , @sColName sysname

-- get the 1st row of data from the cursor
FETCH next from curColumns into @xusertype, @sColName, @sFieldType

-- keep going while the last fetch was good
WHILE (@@fetch_status = 0)
begin

   print '<tr>'
      + '<td>' + isnull(@sColName, '<null>') + '</td>'
      + '<td>' + isnull(@sFieldType, '<null>') + '</td>'
      + '</tr>'

   -- get the next row of data from the cursor
   FETCH next from curColumns into @xusertype, @sColName, @sFieldType

end

-- close & deallocate the cursor
CLOSE curColumns
DEALLOCATE curColumns