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 
