HOW TO programatically find which tables are dependent on a specified table in SQL Server 2005
This was my take on the question that came up on a forum -
SELECT so.name
FROM sysobjects so INNER JOIN
sysreferences sr ON so.id = sr.rkeyid
WHERE (so.type = 'U') AND (sr.fkeyid = OBJECT_ID('employee'))
Replace the table name used in the OBJECT_ID function with the appropriate name
OBJECT_ID is a metadata (scalar) function that returns the database object identification number.
fkeyid column of sysreferences returns ID of the referencing table
rkeyid column of sysreferences returns ID of the referenced table
sysreferences system table contains mappings of FOREIGN KEY constraint definitions to the referenced columns.
sysobjects system table contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. The type column specifies the Object type. We use 'U' to refer to a User table
The simpler way of course is to right-click the table in SQL Server 2005 Management Studio and click on View Dependencies. By default it shows, database objects that depend on the specified table and we can switch to the radio button option "Objects on which [table] depends" to view the dependent tables.
In SQL Server 2000, this can be accomplished by right-clicking a table in Enterprise Manager & then selecting "All Tasks" > "Diplay Dependencies...".
SELECT so.name
FROM sysobjects so INNER JOIN
sysreferences sr ON so.id = sr.rkeyid
WHERE (so.type = 'U') AND (sr.fkeyid = OBJECT_ID('employee'))
Replace the table name used in the OBJECT_ID function with the appropriate name
OBJECT_ID is a metadata (scalar) function that returns the database object identification number.
fkeyid column of sysreferences returns ID of the referencing table
rkeyid column of sysreferences returns ID of the referenced table
sysreferences system table contains mappings of FOREIGN KEY constraint definitions to the referenced columns.
sysobjects system table contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. The type column specifies the Object type. We use 'U' to refer to a User table
The simpler way of course is to right-click the table in SQL Server 2005 Management Studio and click on View Dependencies. By default it shows, database objects that depend on the specified table and we can switch to the radio button option "Objects on which [table] depends" to view the dependent tables.
In SQL Server 2000, this can be accomplished by right-clicking a table in Enterprise Manager & then selecting "All Tasks" > "Diplay Dependencies...".
Comments
Post a Comment