This post is part of the series on SQL Snippets.
The following SQL snippet will select all primary keys for all tables in the database in which the script is run.
/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
SELECT
['Table'].TABLE_NAME
,['Column'].COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS ['Table']
INNER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ['Column']
ON
['Column'].CONSTRAINT_NAME = ['Table'].CONSTRAINT_NAME
WHERE
['Table'].CONSTRAINT_TYPE = 'PRIMARY KEY'
I used this script recently so that I could add primary keys to the Dymamics 365 BC Table Reference website.
Click to show/hide the SQL Snippets Series Index
| SQL Snippets |
|---|
| Manage Data Containing an Apostrophe |
| SELECT INTO |
| Create and Use Database Role to Restrict Access |
| Select Primary Keys for All Tables in Database |
| Select All Fields for All Tables In Database |