Sql Query for Creating Temp Table

-- create temp table
CREATE TABLE #temp
(
Proc_id INT,
Proc_Name SYSNAME,
Definition NTEXT
)

-- get the names of the procedures that meet our criteria
INSERT #temp(Proc_id, Proc_Name)
SELECT id, OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsProcedure') = 1
GROUP BY id, OBJECT_NAME(id)
HAVING COUNT(*) > 1

-- initialize the NTEXT column so there is a pointer
UPDATE #temp SET Definition = ''

-- declare local variables
DECLARE
@txtPval binary(16),
@txtPidx INT,
@curName SYSNAME,
@curtext NVARCHAR(4000)

-- set up a cursor, we need to be sure this is in the correct order
-- from syscomments (which orders the 8KB chunks by colid)

DECLARE c CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT OBJECT_NAME(id), text
FROM syscomments s
INNER JOIN #temp t
ON s.id = t.Proc_id
ORDER BY id, colid
OPEN c
FETCH NEXT FROM c INTO @curName, @curtext

-- start the loop
WHILE (@@FETCH_STATUS = 0)
BEGIN

-- get the pointer for the current procedure name / colid
SELECT @txtPval = TEXTPTR(Definition)
FROM #temp
WHERE Proc_Name = @curName

-- find out where to append the #temp table's value
SELECT @txtPidx = DATALENGTH(Definition)/2
FROM #temp
WHERE Proc_Name = @curName

-- apply the append of the current 8KB chunk
UPDATETEXT #temp.definition @txtPval @txtPidx 0 @curtext

FETCH NEXT FROM c INTO @curName, @curtext
END

-- check what was produced
SELECT Proc_Name, Definition, DATALENGTH(Definition)/2
FROM #temp

-- check our filter
SELECT Proc_Name, Definition
FROM #temp
WHERE definition LIKE '%foobar%'

-- clean up
DROP TABLE #temp
CLOSE c
DEALLOCATE c