Geeks With Blogs

News


Rodney Vinyard - .NET & SQL Developer When all is said and done, more will be said than done

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')

 

Posted on Thursday, December 15, 2011 7:04 AM SQL Server 2005 Tricks , SQL Server 2000 Tricks | Back to top


Comments on this post: Display Foreign Key Relationships and Name of the Constraint for Each Table in Database

# re: Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
Requesting Gravatar...
When you decide to buy medicines from online pharmacies, it is important to purchase only from licensed pharmacies in your state as well as take time to look for adequate company information.

Left by replace ignition on Jan 31, 2012 4:44 PM

# re: Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
Requesting Gravatar...
thanks for this very nice
Left by SHANKAR on Jun 03, 2012 5:58 AM

# seo company Singapore
Requesting Gravatar...
I really enjoy your blog. Aside from the attractive page, the article that can be read into this page is impressing. I get some important topics in here which cannot be seen in any websites. Thanks for sharing this blog. I love it.
Left by vickyxiang11 on Aug 20, 2012 10:33 PM

# nice comment................
Requesting Gravatar...
Hey everyone, I really liked this site and I'm interested in some things about it, anyways I'll see what's up later.
Left by Holiday Villas France on Oct 22, 2012 1:01 AM

# re: Display Foreign Key Relationships and Name of the Constraint for Each Table in Database
Requesting Gravatar...
Really i appreciate the effort you made to share the knowledge.The topic here i found was really effective to the topic which i was researching for a long time
Left by Link Building Agency on Nov 19, 2012 3:57 AM

Your comment:
 (will show your gravatar)


Copyright © Rodney Vinyard | Powered by: GeeksWithBlogs.net