I often find myself needing a list of columns from a table or a view for a query, documentation, troubleshooting, etc. Easy enough to drag and drop them into a query window using SSMS but that's a pain. I have better things to do with my time. All of this information is available through SQL Server system views. The following query is also ANSI compliant.
SELECT
ORDINAL_POSITION
,COLUMN_NAME
,DATA_TYPE
,CHARACTER_MAXIMUM_LENGTH
,IS_NULLABLE
,COLUMN_DEFAULT
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Customer'
ORDER BY
ORDINAL_POSITION ASC;
You can do something like the following to give you a comma separated list that can then be used for a query.
SELECT
COLUMN_NAME + ','
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'Customer'
ORDER BY
ORDINAL_POSITION ASC;
Simple. Straightforward. Time-saver.
