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
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
- Entered : 02/27/2013
- Version :