Foreign keys are usually a good indicator of which tables connect to each other however you may be working in an environment that does not always follow best practices. As well as that you may not know what store procedures relate to what tables.
Sometimes databases need to move from one server to another or are depreciated. Before this can happened it is a very good idea to do a compressive assessment to see what interacts with what.
As once a thing gets moved everything that queries against it needs to point to it its new location.
A good starting point of this assessment would be to use the query below to determine what stored procedures reference the table specified in the WHERE clause.
- NOTES: This query only pulls the tabled referenced in the database, it does not pull tables that are referenced from other databases.
- Although the table maybe reference it may not actually be interacted with by the query, the table name may be comment in the code for example. However for the most part it is more likely the table has some action performed against it, be it a SELECT, INSERT, UPDATE etc.
The query works by using the sysobjects and syscomments tables.
Sysobjects: a system table that contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.
Syscomments: a system table that contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure within the database. The text column contains the original SQL definition statements.
Or to dumb it down it joins, in our case, the stored procedures name to the query and scans this query for references to the specified table.
To expand on this logic to return every table referenced in every stored procedure in a database run the code below: