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