Stored Procedure - Finding Table and PK and FK relations

Last Post 02 Apr 2008 01:02 PM by SQLUSA. 10 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
ckim472
New Member
New Member

--
27 Mar 2008 09:43 AM
I work in the energy sector and we work with a software that does not provide
any ERD's, normalization charts, or any sort of table relationship documentation.
So, I wrote this little utility to get Primary Key and / or Foreign key relationships.
I was hoping one of you guys can help me find any holes in my logic or something I left out.
thanks!!
Ckim472



CREATE PROCEDURE custom_sp_get_fk_fields
@TABLE1 VARCHAR(100)

AS
BEGIN
SET NOCOUNT ON;

DECLARE @OBJ_ID1 INT

-- ====================================================
-- SEARCH FOR TABLE NAME
-- ====================================================

SELECT @OBJ_ID1 = OBJECT_ID
FROM SYS.OBJECTS
WHERE NAME = @TABLE1 AND TYPE = 'U'

-- ====================================================
-- SEARCH FOR PRIMARY KEY AND FOREIGN KEY RELATIONSHIPS
-- ====================================================

SELECT A.CONSTRAINT_OBJECT_ID,
A.CONSTRAINT_COLUMN_ID,
A.REFERENCED_OBJECT_ID AS PARENT_OBJECT_ID,
A.REFERENCED_COLUMN_ID AS PARENT_COLUMN_ID,
A.PARENT_OBJECT_ID AS REFERENCED_OBJECT_ID,
A.PARENT_COLUMN_ID AS REFERENCED_COLUMN_ID,
B.IS_DISABLED
INTO #TMP_FK
FROM SYS.FOREIGN_KEY_COLUMNS A JOIN SYS.FOREIGN_KEYS B
ON A.CONSTRAINT_OBJECT_ID = B.OBJECT_ID
WHERE A.REFERENCED_OBJECT_ID = @OBJ_ID1
UNION ALL
SELECT A.*,
B.IS_DISABLED
FROM SYS.FOREIGN_KEY_COLUMNS A JOIN SYS.FOREIGN_KEYS B
ON A.CONSTRAINT_OBJECT_ID = B.OBJECT_ID
WHERE A.PARENT_OBJECT_ID = @OBJ_ID1

-- =====================================================
-- GET RESULTS SET
-- =====================================================

SELECT NAME = @TABLE1,
P_COL.NAME AS FIELD_NAME,
P_COL.COLUMN_ID AS FIELD_POSITION,
OBJ.NAME AS FK_TABLE,
COL.NAME AS FK_FIELD,
COL.COLUMN_ID AS FK_FIELD_POSITION,
TMP.IS_DISABLED
FROM #TMP_FK TMP JOIN SYS.COLUMNS COL
ON TMP.REFERENCED_OBJECT_ID = COL.OBJECT_ID
AND TMP.REFERENCED_COLUMN_ID = COL.COLUMN_ID
JOIN SYS.OBJECTS OBJ
ON TMP.REFERENCED_OBJECT_ID = OBJ.OBJECT_ID
JOIN SYS.COLUMNS P_COL
ON TMP.PARENT_OBJECT_ID = P_COL.OBJECT_ID
AND TMP.PARENT_COLUMN_ID = P_COL.COLUMN_ID
WHERE OBJ.NAME <> @TABLE1
ORDER BY OBJ.NAME

-- ====================================================
END
GO
ckim472
New Member
New Member

--
27 Mar 2008 09:59 AM
Just send in any tablename to test and you'll see the result set.
SwePeso
New Member
New Member

--
28 Mar 2008 12:04 AM
Or try the function fnTableTree here
http://www.sqlteam.com/forums/topic...C_ID=97454
SQLUSA
New Member
New Member

--
28 Mar 2008 09:47 AM
I tried it for 'Production.Product' in AdventureWorks2008

Returned empty.

Kalman Toth
www.sqlusa.com/order2005
SQLUSA
New Member
New Member

--
28 Mar 2008 09:57 AM
This worked in AdventureWorks 2008

select * from dbo.fnPKFKTree ('Production', 'Product')

Kalman Toth
http://www.sqlusa.com/order2005grandslam
ckim472
New Member
New Member

--
31 Mar 2008 08:14 AM

KT-
Just enter the table name. Make sure your query editor is in the correct database.
ckim472
New Member
New Member

--
31 Mar 2008 08:16 AM
PESO-
Your Code is very thorough.
But, my goal was for simpler results.
If I enter a table name I just want the immediate
table relations and the fields that are keys of each other.

for example if there is a header that relates to headerdetails, then
from headerdetails there are other relations. I only want to see
1 level of relations, from header to headerdetails and what fields
would be the pk/fk.
nosepicker
New Member
New Member

--
01 Apr 2008 08:23 AM
I haven't yet modified this query to use the system views, but here is a simple query to find foreign key relationships using system tables:

DECLARE @id int
SELECT @id = [id] FROM sysobjects WHERE [name] = 'YourTable'

SELECT
obj3.[name] AS [Constraint],
obj1.[name] AS PKTable,
col1.[name] AS PKColumn,
obj2.[name] AS FKTable,
col2.[name] AS FKColumn
FROM sysforeignkeys AS fk
JOIN sysconstraints AS con ON fk.constid = con.constid
JOIN sysobjects AS obj1 ON fk.rkeyid = obj1.id
JOIN sysobjects AS obj2 ON fk.fkeyid = obj2.id
JOIN sysobjects AS obj3 ON fk.constid = obj3.id
JOIN syscolumns AS col1 ON fk.rkeyid = col1.id AND fk.rkey = col1.colid
JOIN syscolumns AS col2 ON fk.fkeyid = col2.id AND fk.fkey = col2.colid
WHERE rkeyid = @id
OR fkeyid = @id

SQLUSA
New Member
New Member

--
02 Apr 2008 09:54 AM
This below works, but missing the Schema info. What if there are 2 "product" tables in different schemas?

USE AdventureWorks2008
GO

DECLARE @id int
SELECT @id = [id] FROM sysobjects WHERE [name] = 'Product'

SELECT
obj3.[name] AS [Constraint],
obj1.[name] AS PKTable,
col1.[name] AS PKColumn,
obj2.[name] AS FKTable,
col2.[name] AS FKColumn
FROM sysforeignkeys AS fk
JOIN sysconstraints AS con ON fk.constid = con.constid
JOIN sysobjects AS obj1 ON fk.rkeyid = obj1.id
JOIN sysobjects AS obj2 ON fk.fkeyid = obj2.id
JOIN sysobjects AS obj3 ON fk.constid = obj3.id
JOIN syscolumns AS col1 ON fk.rkeyid = col1.id AND fk.rkey = col1.colid
JOIN syscolumns AS col2 ON fk.fkeyid = col2.id AND fk.fkey = col2.colid
WHERE rkeyid = @id
OR fkeyid = @id


Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/


nosepicker
New Member
New Member

--
02 Apr 2008 10:09 AM
Right - I implied this earlier but didn't explicitly state that the query is not completely SS2005 "compliant". When I get a chance, I'll adapt it for SS2005 use.
SQLUSA
New Member
New Member

--
02 Apr 2008 01:02 PM
ckim472

You are missing schema support.

A table name in SQL Server 2005 and SQL Server 2008 is like this: >Production.Product<
where Production is the schema.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSIS, SSRS - April 21-24: http://www.sqlusa.com/


Acceptable Use Policy
---