Technical Article

Column_Gitter

,

This little utility comes in handy if you do a lot of SQL coding. Given a table name, it'll return four results: The names of the columns, the names with their datatypes, the list of columns with each one set to an identically-named variable, and a list of variables with the same names as the columns.

If you run it as a script, just type in the name of the table on the second line. I prefer to make this a stored procedure, and pass the table name as a parameter. If you do this, then you can open a Query Analyzer window, type

Column_Gitter 'myTableName'

and hit F5, in less time than it takes to browse the table properties.

No more column-name typing! I hope this helps you.

Rick

Declare @theTable varchar(100)
Set @theTable = 'My_Table_Name_Here'

Declare @ColumnName varchar(100)
 , @theDatatype varchar(100)
 , @thePrecision int
 , @theScale int
 , @theXType int
 , @theString varchar(8000)
Declare C Cursor for
 Select Name
 From syscolumns
 Where ID in
  (Select ID
  From sysobjects
  Where name = @theTable)
Set @theString = ''
Open C
Fetch Next from C into @ColumnName
While @@Fetch_Status <> -1
BEGIN
 Set @theString = @theString + @ColumnName + ', '
 Fetch Next from C into @ColumnName
END
Set @theString = Left (@theString, Len (@theString) - 1)
Close C
Deallocate C
--
Select @theString ColumnList
--
Declare C Cursor for
SELECT syscolumns.name
 , systypes.name
 , syscolumns.prec
 , syscolumns.scale
 , syscolumns.xtype
FROM syscolumns INNER JOIN sysobjects
 ON syscolumns.id = sysobjects.id INNER JOIN systypes
 ON syscolumns.xtype = systypes.xtype
WHERE sysobjects.name = @theTable
Set @theString = ''
Open C
Fetch Next from C into @ColumnName, @theDatatype, @thePrecision, @theScale, @theXType
While @@Fetch_Status <> -1
BEGIN
 Set @theString = @theString + @ColumnName + ' ' + @theDatatype
 If @theXType NOT in (35, 36, 48, 52, 56, 58, 59, 60, 61, 104) -- text, smallint, smalldatetime, tinyint
  --  int, real, datetime, uniqueidentifier, money, and bit doesn't need a length
 BEGIN
  Set @theString = @theString + '(' + CAST (@thePrecision AS varchar(5))
  If @theXType in (106, 108) -- numerics and decimals need a scale, too
   Set @theString = @theString + ', ' + CAST (@theScale AS varchar(5))
  Set @theString = @theString + '), '
 END
 Else
  Set @theString = @theString + ', '
 Fetch Next from C into @ColumnName, @theDatatype, @thePrecision, @theScale, @theXType
END
Set @theString = Left (@theString, Len (@theString) - 1)
Close C
Deallocate C
--
Select @theString ColumnsWithDatatypes
--
Declare C Cursor for
 Select Name
 From syscolumns
 Where ID in
  (Select ID
  From sysobjects
  Where name = @theTable)
Set @theString = ''
Open C
Fetch Next from C into @ColumnName
While @@Fetch_Status <> -1
BEGIN
 Set @theString = @theString + @ColumnName + ' = ' + '@' +  @ColumnName + ', '
 Fetch Next from C into @ColumnName
END
Set @theString = Left (@theString, Len (@theString) - 1)
Close C
Deallocate C
--
Select @theString ColumnsSetFromVariables
--
Declare C Cursor for
 Select Name
 From syscolumns
 Where ID in
  (Select ID
  From sysobjects
  Where name = @theTable)
Set @theString = ''
Open C
Fetch Next from C into @ColumnName
While @@Fetch_Status <> -1
BEGIN
 Set @theString = @theString + '@' +  @ColumnName + ', '
 Fetch Next from C into @ColumnName
END
Set @theString = Left (@theString, Len (@theString) - 1)
Close C
Deallocate C
--
Select @theString VariableListFromColumns

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating