sys.foreign_keys CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sys.foreign_keys CTE

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-11 : 14:47:09

Someone must have written this already...trying to find a CTE that uses recursion to produce a list of FK's in hierarchal order



WITH FK_TREE(TABLE_ID, TABLE_FK, PARENT_ID, PARENT_FK, FK_LEVEL)
AS (

SELECT			   [parent_object_id]		 AS TABLE_ID
	 , OBJECT_NAME([parent_object_id])		 AS TABLE_FK
	 ,			   [referenced_object_id]	 AS PARENT_ID
	 , OBJECT_NAME([referenced_object_id])	 AS PARENT_FK
	 , 0 AS FK_LEVEL
  FROM sys.foreign_keys
 WHERE [type] = 'F'
   AND [parent_object_id] NOT IN (SELECT [referenced_object_id] FROM sys.foreign_keys)
UNION ALL
SELECT			   l.[parent_object_id]		 AS TABLE_ID
	 , OBJECT_NAME(l.[parent_object_id])	 AS TABLE_FK
	 ,			   l.[referenced_object_id]	 AS PARENT_ID
	 , OBJECT_NAME(l.[referenced_object_id]) AS PARENT_FK
	 , r.FK_LEVEL + 1
  FROM  sys.foreign_keys l INNER JOIN FK_TREE r 
    ON  l.[referenced_object_id]  = r.PARENT_ID
 WHERE l.[type] = 'F'
 )

SELECT * FROM  FK_TREE
ORDER BY FK_LEVEL, TABLE_FK

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-11 : 15:05:04

OK,

Found this

http://stackoverflow.com/questions/352176/sqlserver-how-to-sort-table-names-ordered-by-their-foreign-key-dependency

But I thought it would be much simpler...plus it doesn''t show all Tables like I wanted

I modified it, and it works for me, but I still think this should be pretty simple


WITH  
 AllTables(TableName) AS 
 ( 
 SELECT OBJECT_SCHEMA_NAME(so.id) +'.'+ OBJECT_NAME(so.id)
 FROM dbo.sysobjects so  
 INNER JOIN sys.all_columns ac ON  
  so.ID = ac.object_id 
 WHERE 
  so.type = 'U' 
 AND 
  ac.is_rowguidcol = 1 
 ), 
 
  Relationships(ReferenceTableName, ReferenceColumnName, TableName, ColumnName)  AS 
  ( 
  SELECT   
    OBJECT_SCHEMA_NAME (fkey.referenced_object_id) + '.' +   
    OBJECT_NAME (fkey.referenced_object_id) AS ReferenceTableName  
    ,COL_NAME(fcol.referenced_object_id,  
              fcol.referenced_column_id) AS ReferenceColumnName  
    ,OBJECT_SCHEMA_NAME (fkey.parent_object_id) + '.' +   
    OBJECT_NAME(fkey.parent_object_id) AS TableName  
    ,COL_NAME(fcol.parent_object_id, fcol.parent_column_id) AS ColumnName  
  FROM sys.foreign_keys AS fkey  
    INNER JOIN sys.foreign_key_columns AS fcol ON  
               fkey.OBJECT_ID = fcol.constraint_object_id  
  ), 
 
 NotReferencedOrReferencing(TableName) AS 
 ( 
 SELECT TableName FROM AllTables 
 EXCEPT 
 SELECT TableName FROM Relationships 
 EXCEPT 
 SELECT ReferenceTableName FROM Relationships 
 ), 
 
 OnlyReferenced(Tablename) AS 
 ( 
 SELECT ReferenceTableName FROM Relationships 
 EXCEPT 
 SELECT TableName FROM Relationships 
 ), 
-- These need to be sorted based on theire internal relationships 
 ReferencedAndReferencing(TableName, ReferenceTableName) AS 
 ( 
 SELECT r1.Tablename, r2.ReferenceTableName FROM Relationships r1  
 INNER JOIN Relationships r2 
 ON r1.TableName = r2.ReferenceTableName    
 ), 
 
 OnlyReferencing(TableName) AS 
 ( 
 SELECT Tablename FROM Relationships 
 EXCEPT 
 SELECT ReferenceTablename FROM Relationships 
 ) 
 
SELECT TableName, SORTING FROM (
SELECT TableName, 1000 AS Sorting FROM NotReferencedOrReferencing 
UNION 
SELECT TableName, 2000 AS Sorting FROM OnlyReferenced  
UNION 
SELECT TableName, 3000 AS Sorting FROM ReferencedAndReferencing 
UNION 
SELECT TableName, 4000 AS Sorting FROM OnlyReferencing) AS XXX
UNION
SELECT TABLE_NAME, 9999 AS SORTING
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME NOT IN (
SELECT TableName FROM NotReferencedOrReferencing 
UNION 
SELECT TableName FROM OnlyReferenced  
UNION 
SELECT TableName FROM ReferencedAndReferencing 
UNION 
SELECT TableName FROM OnlyReferencing)
ORDER BY Sorting DESC , TABLE_NAME

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-11 : 15:16:45

and it seems like NotReferencedOrReferencing is not working, and that the code I added does what I THINK it's suppose to be doing

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2010-10-11 : 15:18:49

Some enhancements..should trun this into a sproc or udf


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FK_TREE]') AND type in (N'U'))
DROP TABLE [dbo].[FK_TREE]
GO

CREATE TABLE [dbo].[FK_TREE](
	[TABLE_NAME] [varchar](255)
	, [FK_LEVEL] [int])
GO

WITH  
 AllTables(TableName) AS 
 ( 
 SELECT OBJECT_SCHEMA_NAME(so.id) +'.'+ OBJECT_NAME(so.id)
 FROM dbo.sysobjects so  
 INNER JOIN sys.all_columns ac ON  
  so.ID = ac.object_id 
 WHERE 
  so.type = 'U' 
 AND 
  ac.is_rowguidcol = 1 
 ), 
 
  Relationships(ReferenceTableName, ReferenceColumnName, TableName, ColumnName)  AS 
  ( 
  SELECT   
    OBJECT_SCHEMA_NAME (fkey.referenced_object_id) + '.' +   
    OBJECT_NAME (fkey.referenced_object_id) AS ReferenceTableName  
    ,COL_NAME(fcol.referenced_object_id,  
              fcol.referenced_column_id) AS ReferenceColumnName  
    ,OBJECT_SCHEMA_NAME (fkey.parent_object_id) + '.' +   
    OBJECT_NAME(fkey.parent_object_id) AS TableName  
    ,COL_NAME(fcol.parent_object_id, fcol.parent_column_id) AS ColumnName  
  FROM sys.foreign_keys AS fkey  
    INNER JOIN sys.foreign_key_columns AS fcol ON  
               fkey.OBJECT_ID = fcol.constraint_object_id  
  ), 
 
 NotReferencedOrReferencing(TableName) AS 
 ( 
 SELECT TableName FROM AllTables 
 EXCEPT 
 SELECT TableName FROM Relationships 
 EXCEPT 
 SELECT ReferenceTableName FROM Relationships 
 ), 
 
 OnlyReferenced(Tablename) AS 
 ( 
 SELECT ReferenceTableName FROM Relationships 
 EXCEPT 
 SELECT TableName FROM Relationships 
 ), 
-- These need to be sorted based on theire internal relationships 
 ReferencedAndReferencing(TableName, ReferenceTableName) AS 
 ( 
 SELECT r1.Tablename, r2.ReferenceTableName FROM Relationships r1  
 INNER JOIN Relationships r2 
 ON r1.TableName = r2.ReferenceTableName    
 ), 
 
 OnlyReferencing(TableName) AS 
 ( 
 SELECT Tablename FROM Relationships 
 EXCEPT 
 SELECT ReferenceTablename FROM Relationships 
 ) 

INSERT INTO FK_TREE (TABLE_NAME, FK_LEVEL)

SELECT TableName, SORTING FROM (
SELECT TableName, 1000 AS Sorting FROM NotReferencedOrReferencing 
UNION 
SELECT TableName, 2000 AS Sorting FROM OnlyReferenced  
UNION 
SELECT TableName, 3000 AS Sorting FROM ReferencedAndReferencing 
UNION 
SELECT TableName, 4000 AS Sorting FROM OnlyReferencing) AS XXX
UNION
SELECT TABLE_SCHEMA + '.' + TABLE_NAME, 9999 AS SORTING
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA + '.' + TABLE_NAME NOT IN (
SELECT TableName FROM NotReferencedOrReferencing 
UNION 
SELECT TableName FROM OnlyReferenced  
UNION 
SELECT TableName FROM ReferencedAndReferencing 
UNION 
SELECT TableName FROM OnlyReferencing)
ORDER BY Sorting DESC , 1


SELECT * FROM FK_TREE

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-11 : 15:26:17

Not a CTE, but I think this does what you want:
Find Table Reference Levels
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

CODO ERGO SUM

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-10-11 : 17:55:13

That's a nice script.

This may be what Brett is after. Note, Had to use temp table, because outer joins and table variables aren't valid inside of CTEs for the recursive part


Create Table #t (
	fk_object_id int,
	pObjId int,
	parentTable sysname,
	cObjId int,
	childTable sysname,
	fk2_object_id int
)	
INSERT	#t
SELECT	fk1.object_id,
	fk1.referenced_object_id pObjId, object_name(fk1.referenced_object_id) parentTable,
	fk1.parent_object_id cObjId, object_name(fk1.parent_object_id) childTable,
	fk2.object_id
FROM	SYS.FOREIGN_KEYS fk1
LEFT JOIN
	SYS.FOREIGN_KEYS fk2
On	fk1.referenced_object_id = fk2.parent_object_id;

WITH	FKs (fk_object_id, pObjId, parentTable, cObjId, childTable, level, sort)
AS (
	SELECT	t.fk_object_id, t.pObjId, t.parentTable, t.cObjId, t.childTable, 1, t.parentTable
	FROM	#t t
	WHERE	fk2_object_id IS NULL
	UNION ALL
	SELECT	t.fk_object_id, t.pObjId, t.parentTable, t.cObjId, t.childTable, level + 1, t.parentTable
	FROM	#t t
	JOIN	FKs
	On	FKs.cObjId = t.pObjId
)
SELECT	distinct pObjId, parentTable, cObjId, childTable, level
FROM	FKs
ORDER BY parentTable

DROP TABLE #t

If you want it printable, you can replicate pipes and tabs like here: http://msdn.microsoft.com/en-us/library/ms175972.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources