SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Find Table Reference Levels
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 10/05/2006 :  05:30:46  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Here are some sample outputs from my code above

Level	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME		DATA_TYPE
-----	-------------	------------	----------	-----------		---------
0	Alsis		dbo		Dimensions	DimensionID		int
1	Alsis		dbo		Models		DimensionID		int
2	Alsis		dbo		Rules		ModelID			int
3	Alsis		dbo		Translations	RuleID			int
2	Alsis		dbo		Translations	ModelID			int
2	Alsis		dbo		Trees		ModelID			int
2	Alsis		dbo		Trees		ParentModelID		int
1	Alsis		dbo		Prioritygroups	DimensionID		int
2	Alsis		dbo		Rules		PrioritygroupID		int
3	Alsis		dbo		Translations	RuleID			int
0	Alsis		dbo		Members		MemberID		int
1	Alsis		dbo		Models		MemberID		int
2	Alsis		dbo		Rules		ModelID			int
3	Alsis		dbo		Translations	RuleID			int
2	Alsis		dbo		Translations	ModelID			int
2	Alsis		dbo		Trees		ModelID			int
2	Alsis		dbo		Trees		ParentModelID		int
0	Alsis		dbo		Owners		OwnerID			int
1	Alsis		dbo		Models		OwnerID			int
2	Alsis		dbo		Rules		ModelID			int
3	Alsis		dbo		Translations	RuleID			int
2	Alsis		dbo		Translations	ModelID			int
2	Alsis		dbo		Trees		ModelID			int
2	Alsis		dbo		Trees		ParentModelID		int
0	Alsis		dbo		TEST		NULL			NULL
0	Alsis		dbo		Versions	VersionID		int
1	Alsis		dbo		Models		VersionID		int
2	Alsis		dbo		Rules		ModelID			int
3	Alsis		dbo		Translations	RuleID			int
2	Alsis		dbo		Translations	ModelID			int
2	Alsis		dbo		Trees		ModelID			int
2	Alsis		dbo		Trees		ParentModelID		int
1	Alsis		dbo		Prioritygroups	VersionID		int
2	Alsis		dbo		Rules		PrioritygroupID		int
3	Alsis		dbo		Translations	RuleID			int
1	Alsis		dbo		Versions	PreviousVersionID	int

Tree				COLUMN_NAME		DATA_TYPE
----				-----------		---------
Dimensions			DimensionID		int
   Models			DimensionID		int
      Rules			ModelID			int
         Translations		RuleID			int
      Translations		ModelID			int
      Trees			ModelID			int
      Trees			ParentModelID		int
   Prioritygroups		DimensionID		int
      Rules			PrioritygroupID		int
         Translations		RuleID			int
Members				MemberID		int
   Models			MemberID		int
      Rules			ModelID			int
         Translations		RuleID			int
      Translations		ModelID			int
      Trees			ModelID			int
      Trees			ParentModelID		int
Owners				OwnerID			int
   Models			OwnerID			int
      Rules			ModelID			int
         Translations		RuleID			int
      Translations		ModelID			int
      Trees			ModelID			int
      Trees			ParentModelID		int
TEST				NULL			NULL
Versions			VersionID		int
   Models			VersionID		int
      Rules			ModelID			int
         Translations		RuleID			int
      Translations		ModelID			int
      Trees			ModelID			int
      Trees			ParentModelID		int
   Prioritygroups		VersionID		int
      Rules			PrioritygroupID		int
         Translations		RuleID			int
   Versions			PreviousVersionID	int

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/05/2006 05:39:30
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 10/05/2006 :  06:10:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Hrrrm... Something went wrong with the script above. I didn't take into account that PK-FK relation can have other columns for other levels.
So, here is a revised "light" version meanwhile, until I fix the other code.
SET NOCOUNT ON

DECLARE	@Constraints TABLE
	(
		ConstraintID SMALLINT IDENTITY(0, 1),
		UNIQUE_CONSTRAINT_CATALOG NVARCHAR(128),
		UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),
		UNIQUE_CONSTRAINT_NAME NVARCHAR(128),
		CONSTRAINT_CATALOG NVARCHAR(128),
		CONSTRAINT_SCHEMA NVARCHAR(128),
		CONSTRAINT_NAME NVARCHAR(128),
		TABLE_CATALOG NVARCHAR(128),
		TABLE_SCHEMA NVARCHAR(128),
		TABLE_NAME NVARCHAR(128)
	)

INSERT		@Constraints
		(
			UNIQUE_CONSTRAINT_CATALOG,
			UNIQUE_CONSTRAINT_SCHEMA,
			UNIQUE_CONSTRAINT_NAME,
			CONSTRAINT_CATALOG,
			CONSTRAINT_SCHEMA,
			CONSTRAINT_NAME,
			TABLE_CATALOG,
			TABLE_SCHEMA,
			TABLE_NAME
		)
SELECT		rc.UNIQUE_CONSTRAINT_CATALOG,
		rc.UNIQUE_CONSTRAINT_SCHEMA,
		rc.UNIQUE_CONSTRAINT_NAME,
		tc.CONSTRAINT_CATALOG,
		tc.CONSTRAINT_SCHEMA,
		tc.CONSTRAINT_NAME,
		kcu.TABLE_CATALOG,
		kcu.TABLE_SCHEMA,
		kcu.TABLE_NAME
FROM		INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN	INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON kcu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
			AND kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
			AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
LEFT JOIN	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
			AND rc.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
			AND rc.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
WHERE		OBJECTPROPERTY(OBJECT_ID(tc.TABLE_NAME), 'IsMSShipped') = 0
		AND tc.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')

DECLARE	@Tables TABLE
	(
		UNIQUE_CONSTRAINT_CATALOG NVARCHAR(128),
		UNIQUE_CONSTRAINT_SCHEMA NVARCHAR(128),
		UNIQUE_CONSTRAINT_NAME NVARCHAR(128),
		CONSTRAINT_CATALOG NVARCHAR(128),
		CONSTRAINT_SCHEMA NVARCHAR(128),
		CONSTRAINT_NAME NVARCHAR(128),
		TABLE_CATALOG NVARCHAR(128),
		TABLE_SCHEMA NVARCHAR(128),
		TABLE_NAME NVARCHAR(128)
	)

INSERT		@Tables
		(
			UNIQUE_CONSTRAINT_CATALOG,
			UNIQUE_CONSTRAINT_SCHEMA,
			UNIQUE_CONSTRAINT_NAME,
			CONSTRAINT_CATALOG,
			CONSTRAINT_SCHEMA,
			CONSTRAINT_NAME,
			TABLE_CATALOG,
			TABLE_SCHEMA,
			TABLE_NAME
		)
SELECT		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		NULL,
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME
FROM		INFORMATION_SCHEMA.TABLES
WHERE		OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
		AND TABLE_TYPE = 'BASE TABLE'

DELETE		t
FROM		@Tables t
INNER JOIN	@Constraints c ON t.TABLE_CATALOG = c.TABLE_CATALOG
			AND t.TABLE_SCHEMA = c.TABLE_SCHEMA
			AND t.TABLE_NAME = c.TABLE_NAME

DECLARE	@Tree TABLE
	(
		RowID SMALLINT IDENTITY(0, 1),
		RowKey VARBINARY(6478),
		Generation SMALLINT,
		ConstraintID SMALLINT,
		CONSTRAINT_CATALOG NVARCHAR(128),
		CONSTRAINT_SCHEMA NVARCHAR(128),
		CONSTRAINT_NAME NVARCHAR(128),
		TABLE_CATALOG NVARCHAR(128),
		TABLE_SCHEMA NVARCHAR(128),
		TABLE_NAME NVARCHAR(128)
	)

INSERT		@Tree
		(
			Generation,
			ConstraintID,
			CONSTRAINT_CATALOG,
			CONSTRAINT_SCHEMA,
			CONSTRAINT_NAME,
			TABLE_CATALOG,
			TABLE_SCHEMA,
			TABLE_NAME
		)
SELECT		0,
		ConstraintID,
		CONSTRAINT_CATALOG,
		CONSTRAINT_SCHEMA,
		CONSTRAINT_NAME,
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME
FROM		@Constraints
WHERE		UNIQUE_CONSTRAINT_CATALOG IS NULL
		AND UNIQUE_CONSTRAINT_SCHEMA IS NULL
		AND UNIQUE_CONSTRAINT_NAME IS NULL
UNION
SELECT		0,
		NULL,
		CONSTRAINT_CATALOG,
		CONSTRAINT_SCHEMA,
		CONSTRAINT_NAME,
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME
FROM		@Tables
ORDER BY	TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME

DELETE		t
FROM		@Tree t
INNER JOIN	@Constraints c ON c.TABLE_CATALOG = t.TABLE_CATALOG
			AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
			AND c.TABLE_NAME = t.TABLE_NAME
			and c.UNIQUE_CONSTRAINT_CATALOG IS NOT NULL
			AND c.UNIQUE_CONSTRAINT_SCHEMA IS NOT NULL
			AND c.UNIQUE_CONSTRAINT_NAME IS NOT NULL
INNER JOIN	@Tree x ON x.CONSTRAINT_CATALOG = c.UNIQUE_CONSTRAINT_CATALOG
			AND x.CONSTRAINT_SCHEMA = c.UNIQUE_CONSTRAINT_SCHEMA
			AND x.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
			AND x.TABLE_CATALOG = t.TABLE_CATALOG
			AND x.TABLE_SCHEMA = t.TABLE_SCHEMA
			AND x.TABLE_NAME <> t.TABLE_NAME

DELETE		c
FROM		@Constraints c
INNER JOIN	@Tree t ON t.ConstraintID = c.ConstraintID

UPDATE	@Tree
SET	RowKey = CAST(RowID AS VARBINARY)

DECLARE	@Generation SMALLINT

SELECT	@Generation = 0

WHILE @@ROWCOUNT > 0
	BEGIN
		SELECT	@Generation = @Generation + 1		

		INSERT		@Tree
				(
					RowKey,
					Generation,
					ConstraintID,
					CONSTRAINT_CATALOG,
					CONSTRAINT_SCHEMA,
					CONSTRAINT_NAME,
					TABLE_CATALOG,
					TABLE_SCHEMA,
					TABLE_NAME
				)
		SELECT		t.RowKey,
				@Generation,
				c.ConstraintID,
				c.CONSTRAINT_CATALOG,
				c.CONSTRAINT_SCHEMA,
				c.CONSTRAINT_NAME,
				c.TABLE_CATALOG,
				c.TABLE_SCHEMA,
				c.TABLE_NAME
		FROM		@Constraints c
		INNER JOIN	(
					SELECT	RowKey,
						CONSTRAINT_CATALOG,
						CONSTRAINT_SCHEMA,
						CONSTRAINT_NAME
					FROM	@Tree
					WHERE	Generation = @Generation - 1
				) t ON t.CONSTRAINT_CATALOG = c.UNIQUE_CONSTRAINT_CATALOG
					AND t.CONSTRAINT_SCHEMA = c.UNIQUE_CONSTRAINT_SCHEMA
					AND t.CONSTRAINT_NAME = c.UNIQUE_CONSTRAINT_NAME
		ORDER BY	c.TABLE_CATALOG,
				c.TABLE_SCHEMA,
				c.TABLE_NAME

		UPDATE	@Tree
		SET	RowKey = RowKey + CAST(RowID AS VARBINARY)
		WHERE	Generation = @Generation

		UPDATE		t
		SET		t.ConstraintID = c.ConstraintID,
				t.CONSTRAINT_CATALOG = c.CONSTRAINT_CATALOG,
				t.CONSTRAINT_SCHEMA = c.CONSTRAINT_SCHEMA,
				t.CONSTRAINT_NAME = c.CONSTRAINT_NAME
		FROM		@Tree t
		INNER JOIN	@Constraints c ON c.TABLE_CATALOG = t.TABLE_CATALOG
					AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
					AND c.TABLE_NAME = t.TABLE_NAME
		WHERE		t.Generation = @Generation
				AND c.UNIQUE_CONSTRAINT_CATALOG IS NULL
				AND c.UNIQUE_CONSTRAINT_SCHEMA IS NULL
				AND c.UNIQUE_CONSTRAINT_NAME IS NULL

		DELETE		c
		FROM		@Constraints c
		INNER JOIN	@Tree t ON t.ConstraintID = c.ConstraintID
	END

SELECT		Generation [Level],
		TABLE_CATALOG,
		TABLE_SCHEMA,
		TABLE_NAME
FROM		@Tree
ORDER BY	RowKey

Level	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME
-----	-------------	------------	----------
0	Alsis		dbo		Dimensions
1	Alsis		dbo		Models
2	Alsis		dbo		Rules
3	Alsis		dbo		Translations
2	Alsis		dbo		Translations
2	Alsis		dbo		Trees
2	Alsis		dbo		Trees
1	Alsis		dbo		Prioritygroups
2	Alsis		dbo		Rules
3	Alsis		dbo		Translations
0	Alsis		dbo		Members
1	Alsis		dbo		Models
2	Alsis		dbo		Rules
3	Alsis		dbo		Translations
2	Alsis		dbo		Translations
2	Alsis		dbo		Trees
2	Alsis		dbo		Trees
0	Alsis		dbo		Owners
1	Alsis		dbo		Models
2	Alsis		dbo		Rules
3	Alsis		dbo		Translations
2	Alsis		dbo		Translations
2	Alsis		dbo		Trees
2	Alsis		dbo		Trees
0	Alsis		dbo		TEST
0	Alsis		dbo		Versions
1	Alsis		dbo		Models
2	Alsis		dbo		Rules
3	Alsis		dbo		Translations
2	Alsis		dbo		Translations
2	Alsis		dbo		Trees
2	Alsis		dbo		Trees
1	Alsis		dbo		Prioritygroups
2	Alsis		dbo		Rules
3	Alsis		dbo		Translations
1	Alsis		dbo		Versions

Tree
---------------------------
Dimensions
   Models
      Rules
         Translations
      Translations
      Trees
      Trees
   Prioritygroups
      Rules
         Translations
Members
   Models
      Rules
         Translations
      Translations
      Trees
      Trees
Owners
   Models
      Rules
         Translations
      Translations
      Trees
      Trees
TEST
Versions
   Models
      Rules
         Translations
      Translations
      Trees
      Trees
   Prioritygroups
      Rules
         Translations
   Versions


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/05/2006 06:10:40
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/05/2006 :  20:31:33  Show Profile  Reply with Quote
Peter,

Where does it create the DTS package?




CODO ERGO SUM
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/06/2006 :  00:32:01  Show Profile  Reply with Quote
On your server, MVJ.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15654 Posts

Posted - 10/06/2006 :  07:37:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
bcp...bcp...bcp

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 10/10/2006 :  07:21:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And this is the visual diagram for the database outlined above, for comparison.


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 10/10/2006 07:27:22
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 02/15/2008 :  17:42:45  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Long overdue

Code posted here (2nd post)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97454

and a "sister" function to create the necessary delete statements.

E 12°55'05.25"
N 56°04'39.16"

Edited by - SwePeso on 02/16/2008 13:28:00
Go to Top of Page

kevinwoodward
Starting Member

USA
2 Posts

Posted - 06/03/2010 :  08:46:21  Show Profile  Reply with Quote
Hi Guys,

I know this is an old post.

I have been looking for a solution for 6 months to find the hierarchy of my tables. I tried every solution on here and none of them produce correct output. I am using SQL 2005 SP3. Am I missing something.

I have 1100 or so FK's on 300 tables with about 5 self referencing FK's.

thanks...

Kevin
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30098 Posts

Posted - 06/03/2010 :  09:01:07  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Did you try the function in the link posted above yours?


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

Seregwethrin
Starting Member

6 Posts

Posted - 06/28/2010 :  10:43:04  Show Profile  Reply with Quote
Thanks Michael, it'll be helpful to me.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000