Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_sql_server_primary_keys [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== 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 :