SQL SERVER – Get Column Names

I get column names using a schema-qualified table and a defined column order. The four methods below use corrected filters.

-- Run in an installed AdventureWorks sample; replace the schema and table together.
SELECT name AS ColumnName FROM sys.columns
WHERE object_id=OBJECT_ID(N'Production.Product',N'U') ORDER BY column_id;
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=N'Production' AND TABLE_NAME=N'Product' ORDER BY ORDINAL_POSITION;
SELECT s.name AS SchemaName,t.name AS TableName,c.name AS ColumnName
FROM sys.tables t JOIN sys.schemas s ON s.schema_id=t.schema_id
JOIN sys.columns c ON c.object_id=t.object_id
WHERE s.name=N'Production' AND t.name=N'Product' ORDER BY c.column_id;
EXEC sys.sp_help N'Production.Product';

Method 1 uses sys.columns and OBJECT_ID. Method 2 filters INFORMATION_SCHEMA.COLUMNS by table and schema. Method 3 joins catalogs; I previously filtered the column name instead of the table name. Method 4 uses sp_help with additional result sets.

Run in the intended database. NULL from OBJECT_ID can mean an incorrect name or insufficient metadata visibility. INFORMATION_SCHEMA supports portable column details. It does not represent every SQL Server-specific feature.

column_id or ORDINAL_POSITION retains column sequence instead of alphabetizing names. The videos provide related examples. I also introduced my book How to Become Successful Blogger in this article. Its earlier purchase and download offers are historical.

Related reading

• Pinal Dave on YouTube
• Forwarded Records and Performance – SQL in Sixty Seconds #155
• Hide Code in SSMS – SQL in Sixty Seconds #154
• Zoom in SSMS – SQL in Sixty Seconds #153
• Transfer Schema of Table – SQL in Sixty Seconds #152
• Find a Table in Execution Plan – SQL in Sixty Seconds #151

Visible metadata is not proof of every object permission, it is the catalog information available to the caller.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.