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]
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 :