Una de las razones por la cual decidí escribir este artículo es porque alguien me sugirió utilizar el procedimiento de sistema SP_DEPENDS para encontrar las tablas que se usan en un SP (procedimiento almacenado). Al iniciar mis pruebas con el SP_DEPENDS utilizando la base de datos AdventureWorks todo parecía andar muy bien, pero cuando lo utilicé en una base de datos que ha estado funcionando por varios años, descubrí algunas fallas en él.

La idea principal del procedimiento SP_DEPENDS y la tabla de sistema SYSDEPENDS es la de tener un mecanismo que permita guardar información de la dependencia entre los objetos de la base de datos. Por ejemplo, si tenemos TABLA A, TABLA B y un SP C que haga referencia a ambas tablas mediante una consulta SELECT, podríamos ejecutar el SP_DEPENDS pasando el nombre de cualquiera de los tres objetos y veríamos que A esta relacionado a C, B esta relacionado a C y que C esta relacionado a A y B. Ahora bien, si ejecutamos el comando DROP TABLE A y luego recreamos la TABLA A, lo lógico seria que si ejecutamos el SP_DEPENDS nuevamente deberíamos obtener el mismo resultado que vimos anteriormente –pero no es así.

La falla esta en que cuando ejecutamos el comando DROP , todos los registros relacionados con el objeto que estamos eliminando se borran del SYSDEPENDS y aunque volvamos a crear el mismo objeto, las relaciones no se crean nuevamente. Volvamos al ejemplo anterior para explicarlo más detalladamente. Si se ejecuta un DROP TABLE A, entonces todos los registros en la tabla SYSDEPENDS de la TABLA A se borran y la dependencia A > C se desaparece. Al ejecutar el SP_DEPENDS con el objeto C como parámetro podremos observar que la unica relacion que existe es con la TABLA B aunque se aya creado la TABLA A nuevamente.

Así que bien, manténganse alejados del uso del SP_DEPENDS y SYSDEPENDS por ahora. Yo estaré haciendo pruebas con el servidor SQL 2005 a ver si Microsoft se tomó el tiempo y arregló este problema.

Puedes descargar el código aquí y no te olvides de añadir tus comentarios.

RECURSOS: –The Guru’s Guide to Transact-SQL
–Transact-SQL Programming

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)