Generate JOIN statement automatically

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 2005 Forums

Transact-SQL (2005)

Generate JOIN statement automatically

Author

Topic

kumar1248
Starting Member

20 Posts

Posted - 2009-08-20 : 18:16:10

Hi,
I am writing JOINs for lot of tables and spending lot of time to identify the join column and then writing. So i come with an idea that if we can develope some STORED PROCEDURE/ Script that accepts two table names as paramenters and generate join statement. For example:

For a table dbo.TBL_PLB that has a FOREIGN KEY Pol_ID that REFERENCES dbo.TBL_POL (Object_ID0)

then it shold generate:


   dbo.TBL_PLB join dbo.TBL_POL 
      on dbo.TBL_PLB.Pol_ID = dbo.TBL_POL.Object_ID0

This also eliminates manual typo errors in JOINing wrong columns (because my tables has appx 50 to 100 columns sometimes)

Please help.

Thanks,
Kumar.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-20 : 19:00:43

Make use of INFORMATION_SCHEMA views.

N 56°04'39.26"
E 12°55'05.63"

kumar1248
Starting Member

20 Posts

Posted - 2009-08-20 : 19:17:42

Hi Peso,
I wrote something like :


SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS a 
   JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b 
      ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
         AND a.TABLE_NAME = 'TBL_POL'
         AND b.TABLE_NAME = 'TBL_PLB'

Where its not returning any results. I dont find the way to get the join relation between two tables. Please write an example code and help me.

Thanks,
Kumar Playtex.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-08-20 : 21:30:23

Add the INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view, as well as INFORMATION_SCHEMA.KEY_COLUMN_USAGE.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources