May 10, 2011 at 2:10 pm
Hi,
Before I go into what I want to ask here's some sample SQL that will give a base for my question:
CREATE TABLE Table1 (ID INTEGER PRIMARY KEY, SomeData NVARCHAR(50))
CREATE TABLE Table2 (ID INTEGER PRIMARY KEY, LinkID INTEGER CONSTRAINT FK1 REFERENCES Table1 (ID), SomeMoreData NVARCHAR(50))
CREATE TABLE Table3 (ID INTEGER PRIMARY KEY, LinkID INTEGER CONSTRAINT FK2 REFERENCES Table1 (ID), SomeMoreData NVARCHAR(50))
CREATE TABLE Table4 (ID INTEGER PRIMARY KEY , LinkID INTEGER CONSTRAINT FK3 REFERENCES Table1 (ID), SomeMoreData NVARCHAR(50))
INSERT INTO Table1 (ID, SomeData) VALUES (1,'Texas'),(2,'Oklahoma'),(3,'New York')
INSERT INTO Table2 (ID, LinkID, SomeMoreData) VALUES (100,1,'Austin')
INSERT INTO Table3 (ID, LinkID, SomeMoreData) VALUES (200,2,'Norman')
INSERT INTO Table4 (ID, LinkID, SomeMoreData) VALUES (300,3,'Rochester')
What I need is some way to use the PK in Table1 to get the PK for the row of the FK dependent. Here's the output I'm looking for from the above query:
--PKFK_TablePK_for_FK_Table
--1Table2100
--2Table3200
--3Table4300
Is this possible? I can set it up using static code with unions, but I'd much rather find some automated process in doing this.
The reason is we have several Global tables containing canned fields that are used by many elements. Address is one where lots of elements can have an address, so instead of having lots of address tables that would be virtually identical we're creating the Address in a Global.Address table with an AddressID as PK, then we have for example an Account table with just the AddressID as FK and a PK of AccountID. In this example for each Address I'd like to see which table is using the PK as a FK then return the PK of that dependent table which will tell me the Address Type of all Addresses without having to do lots of funky unions.
Thanks --
Sam
May 10, 2011 at 3:21 pm
Since you do have the FK already established, you know that whatever is in the child table has to be in the parent table. So, how about something like:
SELECT PK=1, FK_Table = 'Table2', PK_For_FK_Table = ID
FROM Table2
UNION ALL
SELECT PK=2, FK_Table = 'Table3', PK_For_FK_Table = ID
FROM Table3
UNION ALL
SELECT PK=3, FK_Table = 'Table4', PK_For_FK_Table = ID
FROM Table4;
To have some code to work with multiple parent tables, you'll need dynamic sql, and something like this to get the child tables:
SELECT ParentSchema = ssp.name,
ParentTable = sop.name,
ReferencedSchema = ssr.name,
ReferencedTable = sor.name,
FKName = sfk.name
FROM sys.foreign_keys sfk
JOIN sys.objects sop
ON sfk.parent_object_id = sop.object_id
JOIN sys.schemas ssp
ON sop.schema_id = ssp.schema_id
JOIN sys.objects sor
ON sfk.referenced_object_id = sor.object_id
JOIN sys.schemas ssr
ON sor.schema_id = ssr.schema_id
WHERE sop.name in ('Table1')
This will get the names of the child tables of the specified parent table(s).
You can query sys.foreign_key_columns to get the names of the column in the child table, and sys.index_columns to get the PK columns.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply