Finding tables referenced in a stored procedure
Recently, I was asked for an easy way to list the tables used in a stored procedure. I looked in my list of previously used scripts and found one that did not fully satisfy the requirements of the task. So I decided to come up with a solution that will accurately list all tables referenced in a stored procedure. The solution is pretty straight forward and I’ll describe some of the 12 steps in the code that helped me find all referenced tables within it.
Overview of the problem
Sometimes it is necessary to know which tables are referenced or used in a stored procedure. If the stored procedure you’re working with, is one of those mega procedures of thousands of lines of code, it will be very tedious to go through line by line to find all tables referenced within the procedure.
While developing this solution, I encountered some issues rooted in the fact that stored procedures may be written in many different ways, especially the areas that make reference to tables. For example, take a look at the code in List 1 and see three ways you may write the same query.
There were so many variables that made the development of this solution very interesting. The sample code in List 1 reveals only the SELECT statement with three possible ways I would find reference to tables in stored procedures. Take into consideration that we need to account for the UPDATE, INSERT, and DELETE statements and all the different ways we may find them in the body of stored procedures.
SELECT a.Col1
b.Col2
FROM Table1 a,
Table2 b
WHERE a.ID = b.ID
SELECT a.Col1,
b.Col2
FROM Table1 a, Table2 b
WHERE a.ID = b.ID
SELECT a.Col1,
b.Col2
FROM Table1 a
INNER JOIN Table2 b ON a.ID = b.ID
First, I created two temporary tables. Table one (dbo.#Tables) will hold the name of tables and views from the database and table two (dbo.#Procedure) will hold the text from the stored procedure. Later in the script, I’ll verify the list of possible table names and view names found in the stored procedure (dbo.#Procedure) against the values in the first table (dbo.#Tables)to validate possible matches with the real object names.
Table one is populated with data from the SYSOBJECTS table WHERE type IN(‘U’,’V’). Notice in List 2 how I convert the data extracted from sysname data type to varchar to avoid any data comparison problems later in the code. I also made sure to TRIM the values from the SYSOBJECTS table.
INSERT dbo.#Tables(TableName)
SELECT CONVERT(VARCHAR(35), LTRIM(RTRIM(Name)))
FROM dbo.SYSOBJECTS
WHERE Type IN ('U','V')
You may need to check the maximum object name length in your SYSOBECTS table and update the table definition for dbo.#Tables and the CONVERT statement in List 2 in case the value of 35 is not long enough.
I used the SP_HELPTEXT extended procedure to populate table two. This process will insert one record per line of code returned by the extended procedure.
The following steps will modify the values stored in table two. I replaced any tabs, line feeds, and carriage returns with two spaces which will enable me to use the CHARINDEX() function later on in the solution. I also removed any rows with comments, temporary objects and the CREATE PROCEDURE statement since none of these rows should have the table names I’m looking for. I then looked for the first reference to a real table and removed all rows previous to this row.
The code in List 3 shows how I marked any rows with possible table names by setting the "PossibleTable" column to 1 and then removed all rows with NULL value in the same column.
SELECT @PossibleTable = Texto
FROM dbo.Procedure
WHERE Texto LIKE '%,%' AND
Texto NOT LIKE '%(%';
IF @PossibleTable IS NOT NULL
BEGIN
INSERT dbo.#Procedure (PossibleTable, Texto)
SELECT 1 AS PossibleTable,
TableNames
FROM dbo.fnParseCommaDelimitedList (@PossibleTable);
END
The UDF (User Defined Function) in List 4 is used on those rows that may have multiple table names separated by commas. The UDF takes one single parameter of up to 255 characters long and returns a table. It basically inserts one row in the return table for each value separated by a comma from the input parameter.
CREATE FUNCTION dbo.fnParseCommaDelimitedList (
@myString VARCHAR (255))
RETURNS
@TableNames TABLE (
TableNames VARCHAR (255))
AS
BEGIN
DECLARE @myPosition AS INT,
@myLen AS INT,
@myStart AS INT;
SELECT @myPosition = -1,
@myString = @myString + ',',
@myLen = LEN(@myString),
@myStart = 1;
WHILE @myPosition < @myLen
BEGIN
INSERT @TableNames
SELECT SUBSTRING(@myString, @myStart, CHARINDEX(',', @myString,
@myPosition + 1) - @myStart);
SELECT @myPosition = CHARINDEX(',', @myString, @myPosition + 1);
SELECT @myStart = @myPosition;
END
RETURN;
END
Conclusion
I have tested this solution against many stored procedures written in many different ways and I have found one condition that will cause some problems. It is on how you write your stored procedures and the width of your lines of code. You’ll need to increase the length of the "Texto" column in the dbo.#Procedure table If there are lines of code in your procedure longer than 255 characters. Make sure you have the necessary user rights to create the objects for this solution.
Download the code 01042006.sql and feel free to post your comments about this solution.
RESOURCES: BOL (MS SQL Books Online)
