find foreign key colum using the primary key value

Question

find foreign key colum using the primary key value

kavali.ongole

Old Hand

Points: 320
More actions
April 12, 2010 at 1:06 am

#223145

Hi All
I want to find the foreign key columns using the primary key value.
for example my requirement is i will pass the primary key value and the script finds the referencing tables where the passing the value is available and count of passing value.
Please help me on this topic.
Thanks in advance for your help.
Thanks
Kavali

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Will you be passing the column name or the value for a column? it will be terribly to find every relationship with just a single interger value...

please throw more light on your requirement mate 🙂

kavali.ongole Old Hand Points: 320 More actions · Answer 2

Hi

I am passing only the column value.

for example the table CUSTOMER is primary key with PK_CUSTOMER on CUST_KEY column and referencing the X number of tables as foreign key.

My customer want to pass the CUST_KEY column value and the script needs to search all the reference columns and the the same value exists on reference columns he need to get the reference column table name and count of exists values.

Please let me know if you want more information on this.

Thanks in advance for your information.

Thanks

Kavali

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Kavali, will your customer be providing the table name for which they will be giving the column value ???

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 4

kavali.ongole (4/12/2010)
Please let me know if you want more information on this.

Yes!!

Buddy,please go through this following article and helping us help you?? 🙂

FORUM POSTING ETIQUETTES - JEFF MODEN[/url]

When u do so, i am sure a lot of us will help u instantly...

So please post

1. Table Structures - CREATE TABLE scripts

2. Constraints or Indexes in the table, - CREATE SCRIPTS

3. SAMPLE DATA - INSERT INTO TABLE scripts

4. Desired output - some visual representation of this.

We could guess very little information from your posts mate 🙁 !!

kavali.ongole Old Hand Points: 320 More actions · Answer 5

Yes, he wants to pass the table name, column name & column value.

Thanks

Surendra

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

This should help..

DECLARE@strColumnNameVARCHAR(100)

DECLARE@strTableNameVARCHAR(100)

DECLARE@strColumnValueVARCHAR(100)

DECLARE@strSQLVARCHAR(MAX)

SET@strColumnName = 'EmployeeID'

SET@strTableName = 'mstEmployees'

SET@strColumnValue= 6

SELECT@strSQL = COALESCE( @strSQL + ' UNION ALL ' + CHAR(10), '' )

+ ' SELECT''' + OBJECT_NAME( FK.fkeyid ) + ''' TableName, COUNT(*) Count '

+ ' FROM' + OBJECT_NAME( FK.fkeyid )

+ ' WHERE' + FK_C.[name]

+ CASE

-- Add datatypes that need double qoutes inside dynamic SQL here

WHEN Types.[name] IN ( 'smalldatetime', 'datetime', 'char', 'varchar' )

THEN ' = ''' + @strColumnValue + ''' '

ELSE ' = ' + @strColumnValue

END

FROMsys.sysforeignkeys FK

INNER JOINsys.Columns FK_C ON FK.fkeyid = FK_C.[object_id] AND FK.fkey = FK_C.column_id

INNER JOINsys.Columns RK_C ON FK.rkeyid = RK_C.[object_id] AND FK.rkey = RK_C.column_id

INNER JOINsys.types Types ON FK_C.system_type_id = Types.system_type_id

WHEREOBJECT_NAME( rkeyid ) = @strTableName

ANDRK_C.[name] = @strColumnName

EXECUTE( @strSQL )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kavali.ongole Old Hand Points: 320 More actions · Answer 7

Hi

Thanks a lot for your Help..!!

One small clarification, here i want to select the records only the count value greater than zero.

Please let me know what are the things i need to change in script.

Thanks

Surendra

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 8

Add this block above the EXECUTE( @strSQL ) statement

SET@strSQL = ' SELECT * '

+ ' FROM ( ' + @strSQL + ' ) T '

+ ' WHERE Count > 0 '

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kavali.ongole Old Hand Points: 320 More actions · Answer 9

Hi

Thanks a lot for your help..!!

i am getting the following error message after adding the above specified statements.

Msg 207, Level 16, State 1, Line 1

Invalid column name 'Count'.

Please help me on this error message.

Thanks

kavali

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 10

This does work in my machine. Check the alias name given to COUNT(*) field.

DECLARE @strColumnName VARCHAR(100)

DECLARE @strTableName VARCHAR(100)

DECLARE @strColumnValue VARCHAR(100)

DECLARE @strSQL VARCHAR(MAX)

SET @strColumnName = 'EmployeeID'

SET @strTableName = 'mstEmployees'

SET @strColumnValue = 6

SELECT @strSQL = COALESCE( @strSQL + ' UNION ALL ' + CHAR(10), '' )

+ ' SELECT ''' + OBJECT_NAME( FK.fkeyid ) + ''' TableName, COUNT(*) Count '

+ ' FROM ' + OBJECT_NAME( FK.fkeyid )

+ ' WHERE ' + FK_C.[name]

+ CASE

-- Add datatypes that need double qoutes inside dynamic SQL here

WHEN Types.[name] IN ( 'smalldatetime', 'datetime', 'char', 'varchar' )

THEN ' = ''' + @strColumnValue + ''' '

ELSE ' = ' + @strColumnValue

END

FROM sys.sysforeignkeys FK

INNER JOIN sys.Columns FK_C ON FK.fkeyid = FK_C.[object_id] AND FK.fkey = FK_C.column_id

INNER JOIN sys.Columns RK_C ON FK.rkeyid = RK_C.[object_id] AND FK.rkey = RK_C.column_id

INNER JOIN sys.types Types ON FK_C.system_type_id = Types.system_type_id

WHERE OBJECT_NAME( rkeyid ) = @strTableName

AND RK_C.[name] = @strColumnName

SET @strSQL = ' SELECT * '

+ ' FROM ( ' + @strSQL + ' ) T '

+ ' WHERE Count > 0 '

EXECUTE( @strSQL )

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kavali.ongole Old Hand Points: 320 More actions · Answer 11

kavali.ongole

Old Hand

Points: 320

April 12, 2010 at 8:05 am

#1150066

Thanks a lot..!!