SQL Server Performance Forum – Threads Archive
Reg. foreign key tree
Hi, In one of my projects I have a requirement where I get all the dependent tables for a particular Table. For Example, say Table A has foriegn key relations to B, C,DSay Table B has foriegn key relation to E,F,G
Say Table C has Foreign key relation to H,I,G
Table E has foreign Key relation to U, V, W
Table U has foreign key relation to X, Y, Z Then, is there any way, where if I give Table A, I will get the list of all the tables referencing it (directly or indirectly). The result should retrun all the tables – B, C, D, E, F, G, H, I, G, U, V, W, X, Y, Z Any system function or piece of code would be great. Thanks
oursmp
sp_depends table_A
sp_depends lists only triggers and stored procedures depending on a table. You need to use sp_fkeys to get the first level of fk-related tables, and then repeat for each FKTABLE_NAME that you have found at the first level, and so on and so forth. If you keep track of the number of records in #TMP, you should find out easily how many levels you need to drill down. CREATE TABLE #TMP
(PKTABLE_QUALIFIER VARCHAR(100), PKTABLE_OWNER VARCHAR(100), PKTABLE_NAME VARCHAR(100),
PKCOLUMN_NAME VARCHAR(100), FKTABLE_QUALIFIER VARCHAR(100), FKTABLE_OWNER VARCHAR(100),
FKTABLE_NAME VARCHAR(100), FKCOLUMN_NAME VARCHAR(100), KEY_SEQ INT,
UPDATE_RULE INT, DELETE_RULE INT, FK_NAME VARCHAR(100), PK_NAME VARCHAR(100),
DEFERRABILITY INT) INSERT INTO #TMP
EXEC dbo.sp_fkeys ‘MyTable’ SELECT * FROM #TMP DROP TABLE #TMP
That’s right, sp_fkeys is the proc, blond moment, sorry for that.
I need to do this within an application and hence need to automate this. Is there any command code that would list me all the dependent tables (like in a tree) for a table? Thanks
oursmp
No command that I know of: you usually need to build your own procedures to create usable reports on this type of information, or buy a 3rd party tool. If you’re just looking for a way of documenting the relationships in your DB, why not create a database diagram in Enterprise Manager? You can of course always check if there are any suitable scripts in the "Submit your scripts" section. You could also check the definition of sp_fkeys system sp in the master database and create your own variation. Note that a system sp queries system tables directly, which is not recommended in your own procedures. This is mainly because the next version of SQL Server may not be using the exact same definitions for its system tables. For that reason it is recommended that you go through the INFORMATION_SCHEMA views instead. Finally, I just found another system stored procedure that may be useful:
exec sp_foreign_keys_rowset ‘MyTable’
This one also reports on cascading deletes and updates. Unlike sp_fkeys, you can’t find this one in BOL …
]]>