SQL Tips #33 - Generating a Comma Separated List of Fields per Table

This query generates a comma separated list of the fields in each table - handy for building INSERT or SELECT statements.

The Query

SELECT TABLE_SCHEMA, TABLE_NAME, Stuff(
	(
	SELECT ',' + C.COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS As C
	WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME
	ORDER BY C.ORDINAL_POSITION
	FOR XML PATH('')
	), 1, 1, '') As Columns
FROM INFORMATION_SCHEMA.TABLES As T
--WHERE T.TABLE_NAME='TableName' -- Optional - set to specific table or subset of tables
ORDER BY T.TABLE_NAME

Disclaimer

These queries are provided as a guide, and are by no means perfect. I didn't write all of these - many are collated from MSDN documentation and community sources over the years.

Tags