@Andy.carter 89422: although I'm not a fan of cursors at all and will always try to avoid them in favour of a SET-based solution, I sincerely think changing a cursor into a WHILE loop to be a bad idea. The while-loop approach (sometimes called 'a poor man's cursor') hardly ever brings benefits but potentially can cause the performance to drop noticeable. It all depends a bit on the amount of data to be fetched, as well as the layout and search-ability of said data. Hugo Kornelis did some research on this subject here.
In my real-world tests I've never had a situation where the WHILE solution was faster than the CURSOR solution. I know cursors are considered 'evil', but in fact it's not the cursors that are evil, it's their usage (line by line processing) that is the root of the problem. "Hiding" the word 'cursor' using a WHILE loop does not fix this but rather makes things more complicated and prone to bugs over time.
BTW, totally off-topic but I also think it's a good idea to have the INSERT () INSIDE the @SQL variable instead of before the EXEC() statement. This will allow the optimizer to see both the INSERT and the SELECT as one big operation, while otherwise it would see them as sequential operations; doing whatever is in the EXEC first and taking those results to the INSERT in a separate operation. As an added bonus it avoids the dreaded "Unable to do nested INSERT EXEC" (or something along those lines) error when you (accidentally) try to nest this kind of statements, e.g. when calling a stored procedure that makes use of this and whose output you're trying to catch.
Sadly the 'Show Execution Plan' does not show the INSERT () EXEC() approach, for reasons I don't know, the entire statement seems to be omitted from the query plan completely ???? (SQL2008 here). You can show it by analysing the results from STATISTICS CPU & TIME, but it takes a bit of puzzling to see the operations are split, and (as expected) sum up to taking more cpu-time, more elapsed-time & more IO
Sample code (EDIT : BTW: yes, I know, the code is not 100% correct in relation to the article, it only finds the "lines" of code that have an m in it, not the entire code of the object. (in fact I started off from sql_modules, realised people might want to run this on older versions too and then forgot to adapt, sorry):
SET STATISTICS IO ON
SET STATISTICS TIME ON
DECLARE @SQL nvarchar(max)
DECLARE @DB sysname
SELECT @DB = 'msdb'
SELECT @SQL =
'INSERT INTO Identity_SProcs(dbName,id,colid,spText)
SELECT ' + QUOTENAME(@DB,'''') + ', c.id, c.colid, c.ctext
FROM ' + QUOTENAME(@DB) + '..syscomments c
JOIN ' + QUOTENAME(@DB) + '..sysobjects o
ON o.id = c.id
AND o.[name] NOT LIKE ''sp_MSsync%''
AND o.xtype = ''P''
WHERE c.[text] LIKE ''%m%'''
EXEC (@SQL)
TRUNCATE TABLE Identity_SProcs
SELECT @SQL = '
SELECT ' + QUOTENAME(@DB,'''') + ', c.id, c.colid, c.ctext
FROM ' + QUOTENAME(@DB) + '..syscomments c
JOIN ' + QUOTENAME(@DB) + '..sysobjects o
ON o.id = c.id
AND o.xtype = ''P''
AND o.[name] NOT LIKE ''sp_MSsync%''
WHERE c.[text] LIKE ''%m%'''
INSERT INTO Identity_SProcs(dbName,id,colid,spText)
EXEC (@SQL)
which then results in
Table 'Identity_SProcs'. Scan count 0, logical reads 925, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 3268, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1, logical reads 293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 109 ms, elapsed time = 137 ms.
vs
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysobjvalues'. Scan count 1, logical reads 293, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'syscolpars'. Scan count 1, logical reads 87, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 2, logical reads 48, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 47 ms, elapsed time = 55 ms.
Table 'Identity_SProcs'. Scan count 0, logical reads 919, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 5146, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 78 ms, elapsed time = 98 ms.