Identify Junction tables
-
Hello All,
Is there a way to identify Junction tables in a database using T-SQL?
-Isaiah -
This was removed by the editor as SPAM
-
quote:
Junction TableBOL 2000
You create such a relationship by defining a third table, called a junction table, whose primary key consists of the foreign keys from both table A and table B.
Yes, one can start by having a look at SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-
Hopefully to to far of the mark and only a month late
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Junction]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Junction]
GO
CREATE TABLE [dbo].[Junction] (
[ColA] [int] NOT NULL ,[ColB] [int] NOT NULL ,[ColC] [int] NOT NULL )
GO
ALTER TABLE [dbo].[Junction] WITH NOCHECK ADD
CONSTRAINT [PK_Junction] PRIMARY KEY CLUSTERED
([ColA],[ColB],[ColC])
GO
ALTER TABLE [dbo].[Junction] ADD
CONSTRAINT [FK_Junction_Test_A] FOREIGN KEY
([ColA],[ColB]) REFERENCES [dbo].[Test_A] ([ColA],[ColB]) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_Junction_TestB] FOREIGN KEY
([ColC]) REFERENCES [dbo].[Test_B] ([ColC]) ON DELETE CASCADE ON UPDATE CASCADE
GO
SELECTc.Table_name
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C1
ON c.constraint_type = 'PRIMARY KEY'
AND c.Table_name = C1.Table_name
AND c.Constraint_name = C1.Constraint_name
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS cc
ON c.Table_name=cc.Table_name
AND cc.constraint_type='FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C2
ON CC.Table_name = C2.Table_name
AND C1.Column_name = C2.Column_name
AND cc.Constraint_name = C2.Constraint_name
Group by c.Table_name
Having Count(Distinct c2.Constraint_name)>1
Go
Drop table Junction
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply