Script all Primary Keys, Foreign Keys in a SQL Server using T-SQL
Find the relationships between tables in sql server
Get Primary Key and Foreign Key details using T-SQL
How to find all related tables of a given table?
You got a new database and you started working on that. Essentially when times we have to work with a new database first we need to understand the table structure and relationships. The overall structure we can understand from schema / entity diagrams.
But when it is time to start developing sql code on new database it would always be an easier way to find dependent objects of a given table using “sp_help” or with “Alt+F1”.
Here with I am giving one more way which is handy to find out these details.
When we need to Script all Primary Keys and Foreign Keys in SQL Server:
When we need to understand a database system
Data Imports / Delete / Truncate
While moving code between environment
Now we will see the script to list out all primary and foreign keys in sql server using t-sql
Have a look at the stored procedure “[usp_get_related_Tables]” below.
To know relationship (Primary key/ Foreign Key) details for all tables
IF EXISTS (SELECT 1 FROM sys.sysobjects WHERE TYPE='P' AND NAME='usp_get_related_Tables')
BEGIN
DROP PROCEDURE [dbo].[usp_get_related_Tables];
END
GO
CREATE PROCEDURE [dbo].[usp_get_related_Tables] (
@tbl_Schema VARCHAR(50) = NULL,
@tbl_Name VARCHAR(100) = NULL)
AS
BEGIN
SELECT TC.CONSTRAINT_SCHEMA AS 'Table_Schema',
TC.TABLE_NAME AS 'Table_Name',
TC.CONSTRAINT_NAME AS 'PrimaryKey_Name',
CCU1.COLUMN_NAME AS 'PrimaryKey_Column',
COALESCE(RC.CONSTRAINT_NAME,'N/A') AS 'ForeignKey_Name',
COALESCE(CCU2.Column_Name, 'N/A') AS 'ForeignKey_Column',
CASE WHEN TC2.TABLE_NAME IS NULL THEN 'N/A'
ELSE TC.CONSTRAINT_SCHEMA + '.' + TC2.TABLE_NAME END AS'ForeignKey_Table'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU1 ON
TC.TABLE_NAME = CCU1.TABLE_NAME
AND
TC.TABLE_SCHEMA = CCU1.TABLE_SCHEMA AND
TC.CONSTRAINT_NAME = CCU1.CONSTRAINT_NAME
LEFT
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON
TC.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON
TC2.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
LEFT
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
RC.CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_SCHEMA=ISNULL(@tbl_Schema,TC.CONSTRAINT_SCHEMA) AND
TC.TABLE_NAME = ISNULL(@tbl_Name,TC.TABLE_NAME )
ORDER BY TC.TABLE_NAME,
TC.CONSTRAINT_NAME,
RC.CONSTRAINT_NAME
END
EXEC [dbo].[usp_get_related_Tables]
To know relationship (Primary key/ Foreign Key) details for all tables under a given schema “Sales”
