How can I get column names from a table in SQL Server?

sql

metadata

information-schema

sql-server

byNikita Barsukov·Sep 12, 2024

To retrieve column names from a SQL Server table, execute this:

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'YourTableName';

Replace 'YourTableName' with your specific table name.

When handling multiple schemas, specify the TABLE_SCHEMA too:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName';

Views for extensive metadata

The INFORMATION_SCHEMA.COLUMNS view provides column names and wide-ranging metadata, including data types, nullability, and defaults. Linking with other INFORMATION_SCHEMA views enables a more detailed view:

-- Who doesn't love pasta? Your tables are the pasta, the rest is the sauce.
SELECT 
    t.TABLE_NAME,  -- The pasta 🍝
    c.COLUMN_NAME, -- The ingredients 🧄🍅🧀
    c.DATA_TYPE,   -- Is it penne or spaghetti? 
    c.CHARACTER_MAXIMUM_LENGTH, -- How long is this spaghetti? 
    c.IS_NULLABLE, -- Are we missing any ingredients?
    k.CONSTRAINT_NAME, -- Sauce constraints (No sugar in Bolognese!) 
    k.COLUMN_NAME as CONSTRAINT_COLUMN, -- The crucial ingredient
    p.PERMISSION_TYPE -- Who can cook this pasta? 
FROM INFORMATION_SCHEMA.COLUMNS c
JOIN INFORMATION_SCHEMA.TABLES t
    ON c.TABLE_NAME = t.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
    ON c.TABLE_NAME = k.TABLE_NAME AND c.COLUMN_NAME = k.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMN_PRIVILEGES p
    ON c.TABLE_NAME = p.TABLE_NAME AND c.COLUMN_NAME = p.COLUMN_NAME
WHERE c.TABLE_NAME = 'YourTableName';

Deep dive into the system catalog with sys objects

With system catalog sys objects, such as sys.columns, you can have a deeper look at your data by utilizing OBJECT_ID:

-- You've taken the red pill, prepared for a deep dive? 🚀
SELECT 
    col.name AS ColumnName, 
    typ.name AS DataType,
    col.max_length AS MaxLength,
    col.precision,
    col.scale,
    col.is_nullable AS IsNullable,
    obj.name AS TableName
FROM 
    sys.columns col
    JOIN sys.types typ ON col.user_type_id = typ.user_type_id -- Onboard our types
    JOIN sys.objects obj ON col.object_id = obj.object_id -- Unite with objectives
WHERE 
    obj.type = 'U' -- Jump only for user tables, sorry system tables.
    AND obj.name = 'YourTableName'
ORDER BY 
    obj.name, 
    col.column_id; -- Organizing for the green Matrix code waterfall. 🟢

Traditionally with stored procedures

Detailed column information

Think of sp_columns and sp_help as the classic encyclopedia for the SQL Server, providing a wealth of information:

-- Knock, knock... Who's there? It's 'sp_help'!
EXEC sp_help 'YourTableName';

-- Wait, there's more... It's 'sp_columns'! 
EXEC sp_columns 'YourTableName';

Cutting-edge tricks

For quick access, in SQL Server Management Studio (SSMS), you have the silver bullet: Select a table, press ALT + F1, and sp_help will bring all the metadata to you.

Recognizing user-defined types and views

-- We ore not just about tables. We care about views and types too!
SELECT
    DOMAIN_NAME AS TypeName, -- The name is...
    DOMAIN_DEFAULT AS DefaultValue, -- The default is...
    DATA_TYPE AS DataType -- Oh, the type is...
FROM
    INFORMATION_SCHEMA.DOMAINS -- We have so many domains...
WHERE
    DOMAIN_NAME = 'YourTypeName'; -- Yet, we care about this one
    
SELECT
    vcu.VIEW_NAME,
    vcu.COLUMN_NAME,
    vt.VIEW_DEFINITION
FROM
    INFORMATION_SCHEMA.VIEW_COLUMN_USAGE vcu -- Top secret view!
JOIN
    INFORMATION_SCHEMA.VIEWS vt
    ON vcu.VIEW_NAME = vt.TABLE_NAME -- Our officers joining on a mission
WHERE
    vcu.VIEW_NAME = 'YourViewName'; -- This is the view we're looking for. Bring the popcorn! 🍿