Differences
This shows you the differences between two versions of the page.
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. | ||
+ | |||
+ | |||
+ | |||
+ | ===== Risk of Data Corruption if Run Improperly ===== | ||
+ | |||
+ | **None**. | ||
+ | |||
+ | |||
+ | |||
+ | ===== SQL ===== | ||
+ | |||
+ | |||
+ | |||
+ | [[code_formattsql|code format=" | ||
+ | |||
+ | |||
+ | |||
+ | DECLARE @PrimaryKeyItems TABLE(TableName VARCHAR(255), | ||
+ | |||
+ | |||
+ | |||
+ | INSERT INTO @PrimaryKeyItems | ||
+ | |||
+ | |||
+ | |||
+ | SELECT | ||
+ | |||
+ | |||
+ | |||
+ | FROM | ||
+ | |||
+ | |||
+ | |||
+ | 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 = ' | ||
+ | |||
+ | |||
+ | |||
+ | ; | ||
+ | |||
+ | |||
+ | |||
+ | DECLARE @PrimaryKeys TABLE(TableName VARCHAR(255), | ||
+ | |||
+ | |||
+ | |||
+ | INSERT INTO @PrimaryKeys | ||
+ | |||
+ | |||
+ | |||
+ | SELECT PKI.TableName, | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | | ||
+ | |||
+ | |||
+ | |||
+ | ORDER BY T.ColumnPosition, | ||
+ | |||
+ | |||
+ | |||
+ | FOR XML PATH('' | ||
+ | |||
+ | |||
+ | |||
+ | FROM | ||
+ | |||
+ | |||
+ | |||
+ | GROUP BY PKI.TableName | ||
+ | |||
+ | |||
+ | |||
+ | SELECT T.name AS TableName, PK.PrimaryKeyColumns | ||
+ | |||
+ | |||
+ | |||
+ | FROM | ||
+ | |||
+ | |||
+ | |||
+ | LEFT JOIN @PrimaryKeys PK ON PK.TableName = T.name | ||
+ | |||
+ | |||
+ | |||
+ | ORDER BY CASE WHEN PK.PrimaryKeyColumns IS NULL THEN 0 ELSE 1 END, T.name | ||
+ | |||
+ | |||
+ | |||
+ | < | ||
+ | ===== Version Information ===== | ||
+ | * Entered : 02/27/2013 | ||
+ | * Version : | ||