Table of Contents

Explanation of SQL

This query will list all of the tables in a SQL Server database and list the Primary Keys on each table. If a table does not have any primary keys, it will appear at the top of the list with NULL in the column list.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL

code format="tsql"

DECLARE @PrimaryKeyItems TABLE(TableName VARCHAR(255), PrimaryKeyColumn VARCHAR(255), ColumnPosition INT);

INSERT INTO @PrimaryKeyItems

SELECT tc.TABLE_NAME TableName, c.COLUMN_NAME ColumnName, c.ORDINAL_POSITION ColumnPosition

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 

       JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_NAME = ccu.Constraint_name
	 JOIN INFORMATION_SCHEMA.COLUMNS c ON c.TABLE_NAME = tc.TABLE_NAME AND c.COLUMN_NAME = ccu.COLUMN_NAME

WHERE tc.CONSTRAINT_TYPE = 'Primary Key'

;

DECLARE @PrimaryKeys TABLE(TableName VARCHAR(255), PrimaryKeyColumns VARCHAR(MAX));

INSERT INTO @PrimaryKeys

SELECT PKI.TableName, 

     STUFF( (SELECT   ', ' + T.PrimaryKeyColumn
           FROM     @PrimaryKeyItems T
             WHERE    PKI.TableName = T.TableName
             ORDER BY T.ColumnPosition, T.PrimaryKeyColumn
             FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS ColumnList

FROM @PrimaryKeyItems PKI

GROUP BY PKI.TableName

SELECT T.name AS TableName, PK.PrimaryKeyColumns

FROM SYS.TABLES T 

     LEFT JOIN @PrimaryKeys PK ON PK.TableName = T.name

ORDER BY CASE WHEN PK.PrimaryKeyColumns IS NULL THEN 0 ELSE 1 END, T.name

<code>

Version Information