Menu

Friday 18 January 2013

Search for a table in all stored procedures of a database


BEGIN
SET NOCOUNT ON;


      --fetch all stored procedures in a cursor

      DECLARE CUR CURSOR FOR  SELECT NAME
FROM SYS.PROCEDURES
WHERE TYPE='P'
ORDER BY NAME 

      DECLARE @VSTR_SP_NAME         VARCHAR(MAX);
      DECLARE @VTAB_TABLE           TABLE (SP_CODE VARCHAR(MAX));
      DECLARE @VSTR_SEARCH_TABLE    VARCHAR(MAX);

      --your table to be searched
      SET @VSTR_SEARCH_TABLE='Type_Table_To_Be_Searched'

      OPEN CUR --open cursor
      FETCH NEXT FROM CUR INTO @VSTR_SP_NAME  --fetch record
      WHILE @@FETCH_STATUS=0
      BEGIN
            --get stored procedure code
            INSERT INTO @VTAB_TABLE EXEC SP_HELPTEXT @VSTR_SP_NAME

            --search for the table
            IF EXISTS(SELECT *
                      FROM @VTAB_TABLE
                      WHERE SP_CODE LIKE '%'+@VSTR_SEARCH_TABLE+'%')
               PRINT(@VSTR_SP_NAME)

            --delete data from table
            DELETE FROM @VTAB_TABLE;
            FETCH NEXT FROM CUR INTO @VSTR_SP_NAME
      END
      CLOSE CUR;
      DEALLOCATE CUR;
END

No comments:

Post a Comment