Generate create script for all Foreign Keys
Original article: http://connectsql.blogspot.com/2009/07/script-to-create-all-primary-and.html
SELECT
'ALTER TABLE '+OBJECT_NAME(F.PARENT_OBJECT_ID)+ ' ADD CONSTRAINT'
+
F.NAME + ' FOREIGN KEY'+'('+COL_NAME(FC.PARENT_OBJECT_ID,FC.PARENT_COLUMN_ID)+
')'
+'REFRENCES '+OBJECT_NAME (F.REFERENCED_OBJECT_ID)+'('
+
COL_NAME(FC.REFERENCED_OBJECT_ID,FC.REFERENCED_COLUMN_ID)+')'
FROM
SYS.FOREIGN_KEYS AS F
INNER
JOIN SYS.FOREIGN_KEY_COLUMNS AS FC
ON
F.OBJECT_ID = FC.CONSTRAINT_OBJECT_IDGet the Foreign Key Hierarchy
original article: http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30445/
/******************************************************************************
This script will run through the foreign keys on tables to produce a hierarchy
of the tables in a database.
The heirarchy produced will be :
0 Tables that have no FK relationships at all, as either as 'parents' or
'children'
1 Tables which are at the top of the tree, and have no 'parents', only
'children'
2 ...you can figure it out from here...
If you need to repopulate the database your table order would be 0,1,2...
To delete from tables you need to start at the highest number ...3,2,1,0
*******************************************************************************/
SET
NOCOUNT ON
DECLARE
@intCounter
INT,
@intRowCount
INT
CREATE
TABLE #Hierarchy
(Hierarchy INT,
Child
VARCHAR(100),
Parent
VARCHAR(100))
-- Set the variables
SELECT
@intCounter = 1
SELECT
@intRowCount = 1
-- Populate the table
INSERT
INTO #Hierarchy
SELECT
DISTINCT 1 AS 'Hierarchy', S1.name AS 'Child', SO.Name AS 'Parent'
FROM
dbo.sysforeignkeys FK
INNER
JOIN dbo.sysobjects SO
ON
FK.rkeyID = SO.id
INNER
JOIN dbo.sysobjects S1
ON
FK.fkeyID = S1.id
WHILE
@intRowCount <> 0
BEGIN
UPDATE #Hierarchy
SET Hierarchy = Hierarchy + 1
WHERE Hierarchy = @intCounter
AND Parent IN (SELECT DISTINCT Child
FROM #Hierarchy
WHERE Hierarchy = @intCounter)
SET @intRowCount = @@Rowcount
SELECT @intCounter = @intCounter + 1
END
-- Add the tables that have no Foriegn Key relationships...
INSERT
INTO #Hierarchy
SELECT
-1, [name], ' - '
FROM
dbo.sysobjects
WHERE
[name] NOT IN (SELECT DISTINCT Parent FROM #Hierarchy)
AND
[Name] NOT IN (SELECT DISTINCT Child FROM #Hierarchy)
AND
xtype = 'U'
-- Add the tables that are Parents only
INSERT
INTO #Hierarchy
SELECT
DISTINCT 0, Parent, ' - '
From
#Hierarchy
WHERE
Parent NOT IN (SELECT Child FROM #Hierarchy)
AND
Hierarchy <> -1
-- Add 1 to adjust the hierarchies to start at 0
UPDATE
#Hierarchy
SET
Hierarchy = Hierarchy + 1
-- Display the results
SELECT
DISTINCT Hierarchy, Child, Parent
FROM
#Hierarchy
ORDER
BY Hierarchy, Child, Parent
-- Clean up
DROP
TABLE #Hierarchy
Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
Original Article: http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/
I changed to version below to
1) show PK to the left
2) trim the result columns
SELECT
PK_Table =Left(PK.TABLE_NAME, 35),
PK_Column =Left(PT.COLUMN_NAME, 12),
FK_Table =Left(FK.TABLE_NAME, 25),
FK_Column =Left(CU.COLUMN_NAME, 20),
Constraint_Name = C.CONSTRAINT_NAME
FROM INFORMATION_SCHEMA . REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA . TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA . TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA . KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA . TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA . KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE ='PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
---- optional:
ORDER BY
--1,2,3,4
PK_Table, FK_Table
--WHERE PK.TABLE_NAME='something'WHERE FK.TABLE_NAME='something'
--WHERE PK.TABLE_NAME IN ('one_thing', 'another')
--WHERE FK.TABLE_NAME IN ('one_thing', 'another')