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.

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

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>

• Entered : 02/27/2013
• Version :