Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ===== 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_formattsql|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 ===== * Entered : 02/27/2013 * Version :