How to List All the Columns in a Table

How to list all columns (including schema and table) used in a SQL query

Once you have a query, you can get an execution plan in XML format, E.g. by using the following code.

SET SHOWPLAN_XML ON;  
GO

-- your query here

select ...

GO

SET SHOWPLAN_XML OFF;  
GO

The execution plan contains ColumnReference elements which hold the data that you need (database, schema, table & column), E.g.:

<ColumnReference Database="[mydb]" Schema="[sys]" Table="[sysschobjs]" Alias="[o]" Column="id">

Insert the XML that you got into a table, E.g.

create table ExecutionPlan(id int primary key,execution_plan xml)

-- cannot insert the real XML here due to SO post length limitations
insert into ExecutionPlan values (1, '<ShowPlanXML ...');
insert into ExecutionPlan values (2, '<ShowPlanXML ...');
insert into ExecutionPlan values (3, '<ShowPlanXML ...');

When done with the inserts, use the following query to extract the columns


with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns)

select      distinct

            ep.id
           ,e.val.value ('@Database' ,'sysname')    as db 
           ,e.val.value ('@Schema'   ,'sysname')    as scm 
           ,e.val.value ('@Table'    ,'sysname')    as tab 
           ,e.val.value ('@Column'   ,'sysname')    as col 
                         
from        ExecutionPlan as ep
            cross apply execution_plan.nodes ('//ns:ColumnReference') as e (val)     

where       e.val.value ('@Database' ,'sysname') is not null

order by    id, db, scm, tab, col

+----+-----------------------+-------+--------------------+----------+
| id |          db           |  scm  |        tab         |   col    |
+----+-----------------------+-------+--------------------+----------+
|  1 | [mydb]                | [dbo] | [mytable]          | i        |
|  1 | [mydb]                | [dbo] | [mytable]          | v        |
|  2 | [mydb]                | [dbo] | [mytable]          | i        |
|  2 | [mydb]                | [dbo] | [mytable]          | v        |
|  3 | [mssqlsystemresource] | [sys] | [syspalnames]      | class    |
|  3 | [mssqlsystemresource] | [sys] | [syspalnames]      | name     |
|  3 | [mssqlsystemresource] | [sys] | [syspalnames]      | value    |
|  3 | [mssqlsystemresource] | [sys] | [syspalvalues]     | class    |
|  3 | [mssqlsystemresource] | [sys] | [syspalvalues]     | name     |
|  3 | [mssqlsystemresource] | [sys] | [syspalvalues]     | value    |
|  3 | [mydb]                | [sys] | [sysidxstats]      | id       |
|  3 | [mydb]                | [sys] | [sysidxstats]      | indid    |
|  3 | [mydb]                | [sys] | [sysidxstats]      | intprop  |
|  3 | [mydb]                | [sys] | [sysmultiobjrefs]  | class    |
|  3 | [mydb]                | [sys] | [sysmultiobjrefs]  | depid    |
|  3 | [mydb]                | [sys] | [sysobjvalues]     | objid    |
|  3 | [mydb]                | [sys] | [sysobjvalues]     | valclass |
|  3 | [mydb]                | [sys] | [sysobjvalues]     | valnum   |
|  3 | [mydb]                | [sys] | [sysobjvalues]     | value    |
|  3 | [mydb]                | [sys] | [sysschobjs]       | created  |
|  3 | [mydb]                | [sys] | [sysschobjs]       | id       |
|  3 | [mydb]                | [sys] | [sysschobjs]       | intprop  |
|  3 | [mydb]                | [sys] | [sysschobjs]       | modified |
|  3 | [mydb]                | [sys] | [sysschobjs]       | name     |
|  3 | [mydb]                | [sys] | [sysschobjs]       | nsclass  |
|  3 | [mydb]                | [sys] | [sysschobjs]       | nsid     |
|  3 | [mydb]                | [sys] | [sysschobjs]       | pclass   |
|  3 | [mydb]                | [sys] | [sysschobjs]       | pid      |
|  3 | [mydb]                | [sys] | [sysschobjs]       | status   |
|  3 | [mydb]                | [sys] | [sysschobjs]       | status2  |
|  3 | [mydb]                | [sys] | [sysschobjs]       | type     |
|  3 | [mydb]                | [sys] | [syssingleobjrefs] | class    |
|  3 | [mydb]                | [sys] | [syssingleobjrefs] | depid    |
|  3 | [mydb]                | [sys] | [syssingleobjrefs] | depsubid |
|  3 | [mydb]                | [sys] | [syssingleobjrefs] | indepid  |
+----+-----------------------+-------+--------------------+----------+

Fiddle

P.S.

You would probably want to automate the process by writing a code (e.g. in Python) that connect to your database, pick a query, get its execution plan and save it into a table.

How do I get a list of columns in a table or view?

In SQL Server 2008 R2 (among other versions), there are system views provided automatically with every database. As long as you are connected to the database where your table resides, you can run a query like this:

DECLARE @TableViewName NVARCHAR(128)
SET @TableViewName=N'MyTableName'

SELECT b.name AS ColumnName, c.name AS DataType, 
b.max_length AS Length, c.Precision, c.Scale, d.value AS Description
FROM sys.all_objects a
INNER JOIN sys.all_columns b
ON a.object_id=b.object_id
INNER JOIN sys.types c
ON b.user_type_id=c.user_type_id
LEFT JOIN sys.extended_properties d
ON a.object_id=d.major_id AND b.column_id=d.minor_id AND d.name='MS_Description'
WHERE a.Name=@TableViewName
AND a.type IN ('U','V')

Of course, this is just a starting point. There are many other system views and columns available in every database. You can find them through SQL Server Management Studio under Views > "System Views

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

You can obtain this information and much, much more by querying the Information Schema views.

This sample query: 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

  • CHECK_CONSTRAINTS
  • COLUMN_DOMAIN_USAGE
  • COLUMN_PRIVILEGES
  • COLUMNS
  • CONSTRAINT_COLUMN_USAGE
  • CONSTRAINT_TABLE_USAGE
  • DOMAIN_CONSTRAINTS
  • DOMAINS
  • KEY_COLUMN_USAGE
  • PARAMETERS
  • REFERENTIAL_CONSTRAINTS
  • ROUTINES
  • ROUTINE_COLUMNS
  • SCHEMATA
  • TABLE_CONSTRAINTS
  • TABLE_PRIVILEGES
  • TABLES
  • VIEW_COLUMN_USAGE
  • VIEW_TABLE_USAGE
  • VIEWS

T-SQL List Tables, Columns

Please check out the information schema.

select *
from MyDatabaseName.information_schema.columns
order by table_name, ordinal_position

T-SQL :: List all tables, columns and pivot content

Updated to support 2016

Sample Image

DROP TABLE IF EXISTS #ColumnsToDisplay

SELECT    ROW_NUMBER () OVER (ORDER BY tab.name) AS Iteration,
          s.name AS SchemaName,
          tab.name AS table_name,
          col.column_id,
          col.name AS column_name,
          t.name AS data_type,
          col.max_length,
          col.precision AS PrecisionNumber,
          CAST(NULL AS VARCHAR(MAX)) AS DataSample
INTO      #ColumnsToDisplay
FROM      sys.tables AS tab
JOIN      sys.schemas AS s
    ON    s.schema_id = tab.schema_id
JOIN      sys.columns AS col
    ON    col.object_id = tab.object_id
LEFT JOIN sys.types AS t
    ON    col.user_type_id = t.user_type_id
     
DECLARE @Iterations       INT = 0,
        @CurrentIteration INT = 1;

SELECT @Iterations = MAX (Iteration)
FROM   #ColumnsToDisplay

WHILE @CurrentIteration <= @Iterations
BEGIN
    DECLARE @CurrentTableName  VARCHAR(100)   = '',
            @CurrentColumnName VARCHAR(100)   = '',
            @DynamicQuery      NVARCHAR(1000) = N''
    DECLARE @Sample VARCHAR(MAX)

    SET @CurrentTableName = '';
    SET @DynamicQuery = N'';
    SELECT @CurrentTableName = CONCAT (ttq.SchemaName, '.', ttq.table_name),
           @CurrentColumnName = ttq.column_name
    FROM   #ColumnsToDisplay AS ttq
    WHERE  ttq.Iteration = @CurrentIteration

    IF (@CurrentTableName = '')
    BEGIN
        SET @CurrentIteration += 1

        CONTINUE
    END

 -- SQL Server 2019
 -- SET @DynamicQuery = CONCAT (N'
 --       SELECT @Sample = STRING_AGG(t.ColumnData,'', '') 
 --       FROM (
 --               SELECT TOP 5  CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
 --               FROM ', @CurrentTableName, ' AS x 
 --               WHERE x.[', @CurrentColumnName, '] IS NOT NULL
 --       )t')

 -- SQL Server 2016 and lower where Stuff is supported   
    SET @DynamicQuery = CONCAT (N'
    SELECT @Sample =  STUFF((SELECT '', ''+ t.ColumnData  
    FROM (
            SELECT TOP 5 CAST(x.[', @CurrentColumnName, '] AS VARCHAR(MAX)) AS ColumnData 
            FROM ', @CurrentTableName, ' AS x 
            WHERE x.[', @CurrentColumnName, '] IS NOT NULL 
    ) AS t
       FOR XML PATH('''')),1,1,'''')')
        
    EXECUTE sys.sp_executesql @DynamicQuery,
                              N'@Sample VARCHAR(MAX) OUTPUT',
                              @Sample = @Sample OUTPUT

    UPDATE #ColumnsToDisplay
    SET    DataSample = @Sample
    WHERE  Iteration = @CurrentIteration

    SET @CurrentIteration += 1
END

SELECT ctd.Iteration,
       ctd.SchemaName,
       ctd.table_name,
       ctd.column_id,
       ctd.column_name,
       ctd.data_type,
       ctd.max_length,
       ctd.PrecisionNumber,
       ctd.DataSample
FROM   #ColumnsToDisplay AS ctd

Related Topics

Function with SQL Query Has No Destination for Result Data

Difference Between "On .. And" and "On .. Where" in SQL Left Join

SQL Bulk Import from CSV

What Is the Purpose for Using Option(Maxdop 1) in SQL Server

When Would You Use a Table-Valued Function

Prevent Duplicate Values in Left Join

Designing SQL Database to Represent Oo Class Hierarchy

Select a Column If Other Column Is Null

SQL Constraint Minvalue/Maxvalue

Max VS Top 1 - Which Is Better

How to Retrieve Decimals When Rounding an Average in SQL

SQL Error: Misuse of Aggregate

Conditional Aggregation Performance

How to Aggregate Over Rolling Time Window with Groups in Spark

"This SQLtransaction Has Completed; It Is No Longer Usable."... Configuration Error

SQL Order Chars Numerically

Hql Query to Check If Size of Collection Is 0 or Empty

Difference Between View and Table in SQL


Leave a reply



Submit