Labels

Wednesday, May 4, 2011

STORED PROCEDURES performing DELETE operation on TABLES having column as its name

-- To find out the STORED PROCEDURES performing DELETE operation on TABLES having <Column Name> column as its name
DECLARE @temp TABLE
(ID INT IDENTITY(1,1),
Txt VARCHAR(1000))
CREATE TABLE #Name (name VARCHAR(100))
INSERT INTO @temp
SELECT o.Name txt FROM Sys.objects o inner join Sys.columns c
ON o.object_id = c.object_id
WHERE TYPE ='U' AND
C.NAME LIKE '%Delete%'

DECLARE @Count INT = 0
DECLARE @tabname VARCHAR(100)
SELECT @Count = COUNT(*) FROM @temp
WHILE (@count <> 0)
BEGIN
SELECT @tabname = Txt FROM @temp WHERE ID = @Count
INSERT INTO #Name
SELECT DISTINCT o.name name
FROM sys.syscomments c
JOIN sys.objects o ON o.object_id = c.id
WHERE
c.text LIKE '%DELETE FROM ' + @tabname +'%'
AND o.TYPE ='P'
DELETE FROM @temp WHERE ID = @Count
SET @Count = @Count - 1
END
SELECT * FROM #Name
DROP TABLE #Name

No comments:

Post a Comment