How to determine what stored procedures relate to what tables

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
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s