Technical Article

Comma Separated List Of Field Names

,

Returns a single row with a single column containing a comma-separated list of the field names for a specified table. I find this very useful when doing INSERT statements where I have to list out each field being inserted. I simply bind this proc to a hotkey and use the hotkey to get the comma-separated list of fields.

/*******************************************************************************

Description:
Returns a single row with a single column containing a comma-separated
list of the field names in the specified table.

Note:This is very useful in SQL Query Analyzer.  Use Tools-->Customize to set up a
hotkey (i.e. Ctrl-3) for this proc.  Then any time you need a comma-separated
list of the columns for any given table, simply highlight the table name and
use your hotkey.

*******************************************************************************/CREATE PROCEDURE sp_ListCols

@tablenamesysname

AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE@colnamesysname,
@colsvarchar(1000)

SET @cols = ''

DECLARE c CURSOR FAST_FORWARD FOR
SELECTb.name
FROMsysobjectsa
JOINsyscolumnsb ON b.id = a.id
WHEREa.name = @tablename
ANDa.type = 'U'
ORDERBY b.colorder

OPEN c
FETCH FROM c INTO @colname

WHILE @@Fetch_Status = 0
BEGIN
SET @cols = @cols + ', ' + @colname

FETCH FROM c INTO @colname
END

CLOSE c
DEALLOCATE c

SELECTcols = Substring(@cols, 3, 1000)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating