April 10, 2015 at 5:09 am
How can i find the primary field name and primary table name of a given foreign key.
For example
Table A:
IDa
Column1
Column2
Column3
Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa
What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?
April 10, 2015 at 5:50 am
You want to use the information_schema views to look for that type of information. Here's an article[/url] telling all about how they work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 10, 2015 at 5:58 am
Im sure you can find the info in here by removing what is not needed.
Bit incomplete as I need to join to sysobjects and then to sys.schemas to get schema info but if you have DBO then this would be fine.
SELECT'ALTER TABLE ' + OBJECT_NAME(a.parent_object_id) + ' WITH CHECK CHECK CONSTRAINT [' + a.name + ']' AS sql,
e.name FKColumnName ,f.name PKColumnNAme, OBJECT_NAME(a.parent_object_id) FKTable, b.rowcnt FKTableSize,
OBJECT_NAME(a.referenced_object_id) PKTable, c.rowcnt PKTableSize
FROMsys.foreign_keys a
INNER JOIN sysindexes b ON a.parent_object_id = b.id
INNER JOIN sysindexes c ON c.ID = a.referenced_object_id
INNER JOIN sys.foreign_key_columns d ON a.object_id = d.constraint_object_id
INNER JOIN sys.columns e ON e.object_id = d.parent_object_id AND
e.column_id = d.parent_column_id
INNER JOIN sys.columns f ON f.object_id = d.referenced_object_id AND
f.column_id = d.referenced_column_id
WHEREb.first IS NOT NULL AND
c.first IS NOT NULL AND
b.indid < 2 AND
c.indid < 2
ORDER BY 4, 6
April 10, 2015 at 6:05 am
Exactly what i was looking for!
Thank you.:-)
April 10, 2015 at 6:36 am
is there a way so that i can use it against views ?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply