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

	@tablename	sysname

AS

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE	@colname	sysname,
	@cols		varchar(1000)

SET @cols = ''

DECLARE c CURSOR FAST_FORWARD FOR
	SELECT	b.name
	FROM	sysobjects	a
	JOIN	syscolumns	b ON b.id = a.id
	WHERE	a.name = @tablename
	AND	a.type = 'U'
	ORDER	BY 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

SELECT	cols = Substring(@cols, 3, 1000)

Rate

Share

Share

Rate