We help IT Professionals succeed at work.

code of blocks repeated in SPs

If there a bunch of code (100s of lines) repeated in several procs, is it possible to find which ones have a particular set of code, if we can pass it?

example: how to find all the procs with the following block of code?
CREATE TABLE #tempSnapshot (SnapshotDataID uniqueidentifier)
    INSERT INTO #tempSnapshot SELECT TOP 1 SnapshotDataID
    FROM SnapshotData  WITH (NOLOCK)
    where SnapshotData.PermanentRefcount = 0
    AND SnapshotData.TransientRefcount = 0
    SET @SnapshotsCleaned = @@ROWCOUNT
    DELETE ChunkData FROM ChunkData INNER JOIN #tempSnapshot
    ON ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID
    SET @ChunksCleaned = @@ROWCOUNT
    DELETE SnapshotData FROM SnapshotData INNER JOIN #tempSnapshot
    ON SnapshotData.SnapshotDataID = #tempSnapshot.SnapshotDataID
   
    TRUNCATE TABLE #tempSnapshot
    INSERT INTO #tempSnapshot SELECT TOP 1 SnapshotDataID
    FROM [ReportServerTempDB].dbo.SnapshotData  WITH (NOLOCK)
    where [ReportServerTempDB].dbo.SnapshotData.PermanentRefcount = 0
    AND [ReportServerTempDB].dbo.SnapshotData.TransientRefcount = 0
    AND [ReportServerTempDB].dbo.SnapshotData.Machine = @Machine
    SET @SnapshotsCleaned = @SnapshotsCleaned + @@ROWCOUNT
    SELECT @TempSnapshotID = (SELECT SnapshotDataID FROM #tempSnapshot)
    DELETE [ReportServerTempDB].dbo.ChunkData FROM [ReportServerTempDB].dbo.ChunkData INNER JOIN #tempSnapshot
    ON [ReportServerTempDB].dbo.ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID
    SET @ChunksCleaned = @ChunksCleaned + @@ROWCOUNT
    DELETE [ReportServerTempDB].dbo.SnapshotData FROM [ReportServerTempDB].dbo.SnapshotData INNER JOIN #tempSnapshot
    ON [ReportServerTempDB].dbo.SnapshotData.SnapshotDataID = #tempSnapshot.SnapshotDataID
Comment
Watch Question

Awarded 2008
Awarded 2008
Commented:
sure, you can parse what is in sys.sql_modules

select object_name(object_id) from sys.sql_modules
where definition like '%    INSERT INTO #tempSnapshot SELECT TOP 1 SnapshotDataID
    FROM [ReportServerTempDB].dbo.SnapshotData  WITH (NOLOCK)
    where [ReportServerTempDB].dbo.SnapshotData.PermanentRefcount = 0
    AND [ReportServerTempDB].dbo.SnapshotData.TransientRefcount = 0
    AND [ReportServerTempDB].dbo.SnapshotData.Machine = @Machine
    SET @SnapshotsCleaned = @SnapshotsCleaned + @@ROWCOUNT
    SELECT @TempSnapshotID = (SELECT SnapshotDataID FROM #tempSnapshot)
    DELETE [ReportServerTempDB].dbo.ChunkData FROM [ReportServerTempDB].dbo.ChunkData INNER JOIN #tempSnapshot
    ON [ReportServerTempDB].dbo.ChunkData.SnapshotDataID = #tempSnapshot.SnapshotDataID
    SET @ChunksCleaned = @ChunksCleaned + @@ROWCOUNT
    DELETE [ReportServerTempDB].dbo.SnapshotData FROM [ReportServerTempDB].dbo.SnapshotData INNER JOIN #tempSnapshot
    ON [ReportServerTempDB].dbo.SnapshotData.SnapshotDataID = #tempSnapshot.SnapshotDataID%'

Author

Commented:
Good deal. Thanks.