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.
/* CHANGE THE FOLLOWING: SERVER NAME: ServerName DATABASE NAME: DatabaseName TABLE NAME: TableName */ USE DatabaseName; GO SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE '%TableName%'
To expand on this logic to return every table referenced in every stored procedure in a database run the code below:
/* CHANGE THE FOLLOWING: SERVER NAME: ServerName DATABASE NAME: DatabaseName */ USE DatabaseName; GO --DROP TEMP TABLES IF OBJECT_ID('tempdb..#TablesAndSPs') IS NOT NULL DROP TABLE #TablesAndSPs IF OBJECT_ID('tempdb..#Tables') IS NOT NULL DROP TABLE #Tables --DECLARE VARIABLES DECLARE @tableID AS INT DECLARE @tableName AS VARCHAR(255) --CREATE TEMP TABLE TO HOLD THE NAMES OF THE TABLES AND SPs CREATE TABLE #TablesAndSPs ( TABLE_ID INT ,TABLE_NAME VARCHAR(255) ,SP VARCHAR(255) ) --GET A LIST OF THE TABLES IN DATABASE SELECT row_number() OVER ( ORDER BY TABLE_NAME ) AS TABLE_ID ,TABLE_NAME INTO #Tables FROM INFORMATION_SCHEMA.TABLES SET @tableID = 1 --LOOP AND POPULATE #TablesAndSPs WHILE @tableID <= ( SELECT MAX(TABLE_ID) FROM #Tables ) BEGIN SET @tableName = ( SELECT TABLE_NAME FROM #Tables WHERE TABLE_ID = @tableID ) INSERT INTO #TablesAndSPs SELECT DISTINCT @tableID ,@tableName ,so.NAME FROM syscomments sc INNER JOIN sysobjects so ON sc.id = so.id WHERE sc.TEXT LIKE '%' + @tableName + '%' SET @tableID = @tableID + 1 END SELECT * FROM #TablesAndSPs