listing objets in any DB on server

[womalley]

Hello,
I posted this over at sqlserverentral.com but have not had any reply's.
If you are a member here is the link:
http://sqlservercentral.com/scripts/viewscript.asp?scriptid=1620

If you are not member here is the code:


CREATE PROCEDURE spFindObjectUsage
@ObjectToFind NVARCHAR(100) = ''
,@ResultMessage VARCHAR(200) = '' OUTPUT
AS
DECLARE
@ReturnCode INT
,@StringToExecute NVARCHAR(1500)
,@DBToProcess INT
,@ServerName VARCHAR(200)
,@TableWithServer VARCHAR(200)
,@NameOfDatabase VARCHAR(50)
--
DECLARE @ServerDatabaseTables TABLE
(TempTblID INT NOT NULL IDENTITY(1,1)
,DBName VARCHAR(100) NOT NULL DEFAULT ''
,Processed BIT NOT NULL DEFAULT 0)
--
CREATE TABLE #ServerDatabaseObjectUsage
(UsageID INT NOT NULL IDENTITY(1,1)
,DBName VARCHAR(100) NOT NULL DEFAULT ''
,ObjectUsedIn VARCHAR(200) NOT NULL DEFAULT ''
,TypeOfObject VARCHAR(50) NOT NULL DEFAULT ''
,IsColumnOfTable BIT NOT NULL DEFAULT 0)
--
-- first get all the databases on the current server
--
INSERT INTO @ServerDatabaseTables
(DBName)
SELECT
name
FROM master.dbo.sysdatabases
--
SET @TableWithServer = ''
SET @NameOfDatabase = ''
--
SET NOCOUNT ON
-- each database has it's own listing of System Objects so inorder to get
-- a correct listing we will need to go through every database.
-- the only way I know to do this is using sqlexec.
-- I know it is not the best way but we will need the ability to dynamically
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)
--
SELECT @ServerName = DBName + '.dbo.'
,@NameOfDatabase = DBName
FROM @ServerDatabaseTables
WHERE TempTblID = @DBToProcess
--
SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +
'(DBName' +
',ObjectUsedIn' +
',TypeOfObject' +
',IsColumnOfTable) ' +
'SELECT DISTINCT ' +
char(39)+@NameOfDatabase+char(39)+
',obj.NAME' +
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) +
' THEN ' + char(39) + 'PROCEDURE' + char(39) +
' WHEN ' + char(39) + 'V' + char(39) +
' THEN ' + char(39) + 'VIEW' + char(39) +
' WHEN ' + char(39) + 'U' + char(39) +
' THEN ' + char(39) + 'USER TABLE' + char(39) +
' WHEN ' + char(39) + 'D' + char(39) +
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
' WHEN ' + char(39) + 'F' + char(39) +
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
' WHEN ' + char(39) + 'IF' + char(39) +
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'FN' + char(39) +
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'TF' + char(39) +
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +
'THEN 1 ELSE 0 END)' +
'FROM ' + @ServerName + 'sysobjects as obj ' +
'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' +
'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' +
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
' OR syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
' ORDER BY obj.name '
SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))
PRINT LEN(@StringToExecute)
--
exec sp_executesql @StringToExecute
--
IF (@@ERROR != 0) BEGIN
SET @ReturnCode = 1
GOTO END_PROCEDURE
END
--
UPDATE @ServerDatabaseTables
SET Processed = 1
WHERE TempTblID = @DBToProcess
END
--
SELECT * FROM #ServerDatabaseObjectUsage
--
DROP TABLE #ServerDatabaseObjectUsage
--
SET @ReturnCode = 0

END_PROCEDURE:
IF (@ReturnCode != 0) BEGIN
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))
END ELSE BEGIN
SET @ResultMessage = 'OK'
END
RETURN @ReturnCode



I am looking for feed back on the code:
If you like it?
Is there a better way to do this?
And any other comments or suggestions are welome.

Please let me know,

William O'Malley

[Krzemo]

First of all - why don't U use cursors instead of that nasty update/select WHILE?

Why U limit output only to "dbo" schema? U can do that by cutting of "dbo" from select statement, so for example instead of
"SELECT * FROM Norhwind.dbo.MyTable" U can allways use "SELECT * FROM Norhwind..MyTable"
Of course sys.. tables are ususally in "dbo" schema but ... not allways ;-)

"GOTO END_PROCEDURE" - brrr!
U should call RAISERROR and leave procedure using RETURN statement.

IMHO your code for finding dependencies is very ugly. U make unnecesery joins to few (usually large) tables just to use DISTINCT to cut it off. Instead of that U can use "EXISTS" or "IN (SELECT..) " keywords
Main loop IMHO should look like this:


DECLARE @Name VARCHAR(255)
DECLARE crsr CURSOR LOCAL FAST_FORWARD
FOR SELECT name FROM master..sysdatabases

OPEN crsr
FETCH NEXT FROM crsr INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
SET @StringToExecute = 'INSERT INTO ...SELECT ... FROM '+@Name+'..sysobjects'
..... your code here!
FETCH NEXT FROM crsr INTO @Name
END
CLOSE crsr
DEALLOCATE crsr


Best regards,
Krzemo.

[womalley]

First of all - why don't U use cursors instead of that nasty update/select WHILE?

Best regards,
Krzemo.
Cursors? ARE U SERIOUS?

First they are slower and second....Aah..never mind

thanks for the ...... suggestions

[Krzemo]

Cursors? ARE U SERIOUS?

First they are slower and second....
Slower than UPDATE/ SELECT MIN()/ in the loop WHILE EXISTS(SELECT) ?
Really ?:rolleyes:

thanks for the ...... suggestions

I'm glad that I .... helped.
;)

And any other comments or suggestions are welome.
Hope that it is true
:wave:

[womalley]

Maybe if U had made YOUR suggestions more constructive and less critical then I would have received them better.

And yes cursors are slow...thats a fact.

I have an idea...rather then US act like a bunch of babies trying to see who can cry louder..post some code, make some mods MAKE THE CODE better!

Wow what an Idea...phew...that hurt..

All I wanted was some feed back a little developer to developer talk and maybe just maybe some code posted back with a "Hay nice try but check this out"

Oh well so much for the developer community being about helping eachother out...

EDIT--->
Awsome Article:
SQL Server Performance Tuning for SQL Server Developers
(http://www.databasejournal.com/features/mssql/article.php/1466951)

Thanks again,
Will

[Krzemo]

And yes cursors are slow...thats a fact.

Lets see....

Testing SQL Server 2000 SP3 on Windows XP Prof.
Exaple 1 - cursor

DECLARE @Name VARCHAR(255),@i INT
SET @i=1
WHILE @i<100000
BEGIN
DECLARE crsr CURSOR LOCAL FAST_FORWARD
FOR SELECT name FROM master..sysdatabases
OPEN crsr
FETCH NEXT FROM crsr INTO @Name
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM crsr INTO @Name
END
CLOSE crsr
DEALLOCATE crsr
SET @i=@i+1
END

Execution time 0:00:25

Example 2 -"faster" solution :rolleyes:


DECLARE
@ReturnCode INT
,@StringToExecute NVARCHAR(1500)
,@DBToProcess INT
,@ServerName VARCHAR(200)
,@TableWithServer VARCHAR(200)
,@NameOfDatabase VARCHAR(50)
,@i INT
SET @i=1
WHILE @i<1000 -- I set it 100 times lower than exaple 1 because I havn't so much time to waste;-)
BEGIN
DECLARE @ServerDatabaseTables TABLE
(TempTblID INT NOT NULL IDENTITY(1,1)
,DBName VARCHAR(100) NOT NULL DEFAULT ''
,Processed BIT NOT NULL DEFAULT 0)
INSERT INTO @ServerDatabaseTables
(DBName)
SELECT
name
FROM master.dbo.sysdatabases
SET @TableWithServer = ''
SET @NameOfDatabase = ''
SET NOCOUNT ON
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)
SELECT @NameOfDatabase = DBName
FROM @ServerDatabaseTables
WHERE TempTblID = @DBToProcess
UPDATE @ServerDatabaseTables
SET Processed = 1
WHERE TempTblID = @DBToProcess
END
SET @i=@i+1
END

Execution time 0:00:43 (to compare U need to multiply by 100 because of 100 times shorter loop)

Maybe if U had made YOUR suggestions more constructive and less critical then I would have received them better.

My suggestion IMHO was constructive since I pointed it out what and how should be changed (with example). Rewriting YOUR code is YOUR job since YOU are the author of that script. And U have been posted it to some places making other believe that what U have done is a good programmer practice. Maybe it wasn't too nice to drop some cold water right to your head but it was stronger than me and I couldn't keep from it. I just see too many bad style programming over and over.

And few words about squizing max of performance. Sometimes it is better to have slower nice code than super fast ugly and hard to maintain code. Especialy when U operate on small tables and/or use script not often. U are a kind of programmer which tends to make his program a bunch of tricks. That is like shooting to small birds with cannon. And since your trick code is much much slower than nice cursor solution than there is no excause for using it.

All I wanted was some feed back a little developer to developer talk and maybe just maybe some code posted back with a "Hay nice try ...."
So U shouldn't ask for any comments - U should ask for praises :rolleyes:

And about article ...
I have impression that U didn't red it carefully enough...
Your solution is processing databases one by one (row by row) - it is ideal place for LOCAL FAST_FORWARD cursor.
I trully agree with article that if U can write 1 select that do it all than it will be the best ultimate solution - but U can't - can U ?
And U should make no asumption about performance until U test it yourself, since there is allways posibility that some trick is no longer valid or U just make wrong assumption.

Krzemo.

[ITGURU]

Dear womalley,

Below is your modified code which is almost 10 times faster than existing one. In your code i have made small changes (find changes under comment start with Modified by Guru on 7 Feb 2006 Start and End with Modified by Guru on 7 Feb 2006 End):

If you find it perfect please rate this reply.


CREATE PROCEDURE spFindObjectUsage
@ObjectToFind NVARCHAR(100) = ''
,@ResultMessage VARCHAR(200) = '' OUTPUT
AS
DECLARE
@ReturnCode INT
,@StringToExecute NVARCHAR(1500)
,@DBToProcess INT
,@ServerName VARCHAR(200)
,@TableWithServer VARCHAR(200)
,@NameOfDatabase VARCHAR(50)

--
DECLARE @ServerDatabaseTables TABLE
-- Modified by Guru on 7 Feb 2006 Start
--(TempTblID INT NOT NULL IDENTITY(1,1)
(TempTblID INT NOT NULL IDENTITY(1,1) primary key
-- Modified by Guru on 7 Feb 2006 End
,DBName VARCHAR(100) NOT NULL DEFAULT ''
,Processed BIT NOT NULL DEFAULT 0)

--
CREATE TABLE #ServerDatabaseObjectUsage
(UsageID INT NOT NULL IDENTITY(1,1)
,DBName VARCHAR(100) NOT NULL DEFAULT ''
,ObjectUsedIn VARCHAR(200) NOT NULL DEFAULT ''
,TypeOfObject VARCHAR(50) NOT NULL DEFAULT ''
,IsColumnOfTable BIT NOT NULL DEFAULT 0)
--
-- first get all the databases on the current server
--
INSERT INTO @ServerDatabaseTables
(DBName)
SELECT
name
FROM master.dbo.sysdatabases
--
SET @TableWithServer = ''
SET @NameOfDatabase = ''
--
SET NOCOUNT ON
-- each database has it's own listing of System Objects so inorder to get
-- a correct listing we will need to go through every database.
-- the only way I know to do this is using sqlexec.
-- I know it is not the best way but we will need the ability to dynamically
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)
--
SELECT @ServerName = DBName + '.dbo.'
,@NameOfDatabase = DBName
FROM @ServerDatabaseTables
WHERE TempTblID = @DBToProcess
--
-- Modified by Guru on 7 Feb 2006 Start
-- SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +
-- '(DBName' +
-- ',ObjectUsedIn' +
-- ',TypeOfObject' +
-- ',IsColumnOfTable) ' +
-- 'SELECT DISTINCT ' +
-- char(39)+@NameOfDatabase+char(39)+
-- ',obj.NAME' +
-- ',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) +
-- ' THEN ' + char(39) + 'PROCEDURE' + char(39) +
-- ' WHEN ' + char(39) + 'V' + char(39) +
-- ' THEN ' + char(39) + 'VIEW' + char(39) +
-- ' WHEN ' + char(39) + 'U' + char(39) +
-- ' THEN ' + char(39) + 'USER TABLE' + char(39) +
-- ' WHEN ' + char(39) + 'D' + char(39) +
-- ' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
-- ' WHEN ' + char(39) + 'F' + char(39) +
-- ' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
-- ' WHEN ' + char(39) + 'IF' + char(39) +
-- ' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
-- ' WHEN ' + char(39) + 'FN' + char(39) +
-- ' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
-- ' WHEN ' + char(39) + 'TF' + char(39) +
-- ' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
-- ' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
-- ',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +
-- 'THEN 1 ELSE 0 END)' +
-- 'FROM ' + @ServerName + 'sysobjects as obj ' +
-- 'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' +
-- 'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' +
-- 'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
-- ' OR syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
-- ' ORDER BY obj.name '
SET @StringToExecute = 'INSERT INTO #ServerDatabaseObjectUsage ' +
'(DBName' +
',ObjectUsedIn' +
',TypeOfObject' +
',IsColumnOfTable) ' +
'SELECT DISTINCT ' +
char(39)+@NameOfDatabase+char(39)+
',obj.NAME' +
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) +
' THEN ' + char(39) + 'PROCEDURE' + char(39) +
' WHEN ' + char(39) + 'V' + char(39) +
' THEN ' + char(39) + 'VIEW' + char(39) +
' WHEN ' + char(39) + 'U' + char(39) +
' THEN ' + char(39) + 'USER TABLE' + char(39) +
' WHEN ' + char(39) + 'D' + char(39) +
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
' WHEN ' + char(39) + 'F' + char(39) +
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
' WHEN ' + char(39) + 'IF' + char(39) +
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'FN' + char(39) +
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'TF' + char(39) +
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
',0 ' +
'FROM ' + @ServerName + 'sysobjects as obj ' +
'INNER JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' +
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39)

SET @StringToExecute = @StringToExecute + ' UNION ALL '

SET @StringToExecute = @StringToExecute + ' SELECT DISTINCT ' +
char(39)+@NameOfDatabase+char(39)+
',obj.NAME' +
',(CASE obj.XTYPE WHEN ' + char(39) + 'P' + char(39) +
' THEN ' + char(39) + 'PROCEDURE' + char(39) +
' WHEN ' + char(39) + 'V' + char(39) +
' THEN ' + char(39) + 'VIEW' + char(39) +
' WHEN ' + char(39) + 'U' + char(39) +
' THEN ' + char(39) + 'USER TABLE' + char(39) +
' WHEN ' + char(39) + 'D' + char(39) +
' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
' WHEN ' + char(39) + 'F' + char(39) +
' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
' WHEN ' + char(39) + 'IF' + char(39) +
' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'FN' + char(39) +
' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
' WHEN ' + char(39) + 'TF' + char(39) +
' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
',(CASE WHEN (syscol.NAME='+char(39)+@ObjectToFind+char(39) + ') THEN 1 ELSE 0 END)' +
'FROM ' + @ServerName + 'sysobjects as obj ' +
'INNER JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' +
'WHERE syscol.name LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) +
' ORDER BY obj.name '
-- Modified by Guru on 7 Feb 2006 End

SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))
PRINT LEN(@StringToExecute)
--
exec sp_executesql @StringToExecute
--
IF (@@ERROR != 0) BEGIN
SET @ReturnCode = 1
GOTO END_PROCEDURE
END
--
UPDATE @ServerDatabaseTables
SET Processed = 1
WHERE TempTblID = @DBToProcess
END
--
SELECT * FROM #ServerDatabaseObjectUsage
--
DROP TABLE #ServerDatabaseObjectUsage
--
SET @ReturnCode = 0

END_PROCEDURE:
IF (@ReturnCode != 0) BEGIN
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))
END ELSE BEGIN
SET @ResultMessage = 'OK'
END
RETURN @ReturnCode


If you need any help further in this regard feel free to contact me.

[womalley]

Very nice mod!

1) I really like that you added the Primary Key to the Temp Table
I did append to that and add NONCLUSTERED otherwise it will create a clustered key and why have a unique key be clustered... =)

2) I like that you got rid of the 2 left joins
Creating to different selects using inner joins which reduced the result set being returned

Awsome replay and great fixes that I will be sure to include.
This routine use to take upwards of 1.5 minutes now it returns results in seconds!

Thanks,
Will

[ITGURU]

Dear womalley,

Please rate my suggestion and also send me a copy if you made any more changes in the script.

[womalley]

ITGuru,
I rated your post after I read it.

When I click on it now I get:

You cannot give Reputation to the same post twice.

I will copy you...

Thank you again,
Will

[ITGURU]

Dear womalley,

Thanks for giving Rating for my suggestion and sorry for repeatedly asked for this actually I have not seen that you already given the rating for my posting.

Could you please tell me how can I see the Rating given by user for my posting without going to User Control Panel Page.

[cherish]

Hello ITGURU,

You can see the Rating given to you by a user by clicking "Rate This Post" on the post you want to check. But it won't show you exactly how many points you received, nor will it show who rated you on that specific post. You will only find how your post fared like "Somewhat Positive", "Very Positive", etc.

For more information as to how these ratings work, etc.. you might to check the Feedback forum. This has been asked countless times. :)

Regards,
Cherish

[womalley]

Thank you Cherish


Will

[cherish]

Thank you Cherish


Will
You're welcome. And sorry for butting in like that... I just can't help but post a reply after reading ITGURU's post. :D

[exterminator]

Krezmo... what you have shown is not a way to compare the efficiency of a block of code implemented in two different ways.

My objection is the fact that both of codes do not do exactly the same thing. One is just opening a cursor and looping in some 'n' number of time while the other is even updating records.

You can only compare those when the things achieved by them meet the exact same objective!

Having said that, there is no denying the fact that Cursors are slow. But sometimes become a necessity.. However, with proper use of joins and other SQL constructs you can usually avoid them. Regards.

//I am feeling too lazy to have a look at OP's code - may be tomorrow I will.

[Krzemo]

My objection is the fact that both of codes do not do exactly the same thing. One is just opening a cursor and looping in some 'n' number of time while the other is even updating records.

You can only compare those when the things achieved by them meet the exact same objective!


I have to disagree with U.
Those 2 examples do exactly the same - only making empty loop in 'n' number of times ....

Yes - the second one makes some changes, but those changes (updates) are only for proper loop control. Without them the loop is endless. So from algorithm point of view, both examples do the same = NOTHING ;-)

I wrote those examples just to fight with very controversial opinion that "cursors have to be avoided at all costs". That hint (not understood properly by unexpirienced developers) can make more damage than good things. And what it this means "at all costs" - is it that cursors shoud be avoided even in situations where they are faster? I don't think that author of that words really means that, but as U see many developers tends to interpret it that way. IMHO the hint should sounds something like that:
"It is strongly recomended that developer should avoid any iterative processing since it in most cases significantly degradate performance".
So better is to write one update, for example, instead of loop with many small updates in it (but from many years of expirience I know that it is not allways true too ;) )

What was proposed in the second script was to replace nice cursor loop with "user made cursor". So the second script is iterative too, but with that nasty CREATE TABLE/INSERT INTO/WHILE EXISTS/SELECT MIN/UPDATE/DROP TABLE patern. The cursor seams to do the same (but not exactly the same).
Cursor reads table the same way as select do. Than it fetches row which is the same as SELECT MIN but .. SELECT MIN with worse performance since SELECT MIN has to be evaluated at each step while FETCH just gets another row from user buffer. The controll loop makes the greatest perf difference since in CURSOR solution server has to compare 1 scalar value and (at the end of the loop) get next record. As an oposite, 2nd script have to make EXISTS/SELECT MIN/UPDATE to achieve the same as cursor. Another question is why developer choose UPDATE patern instead of WHERE clause in EXISTS/SELECT MIN, and why SELECT MIN and not SELECT TOP 1 - but this is another story (maybe that way is faster :ehh: )

There arrives a question why iterative solutions should be avoided?

Answer is very simple - because they are iterative :eek:
There is something like SP interpreter that reads command by command and executes them. Unfortunately all interpreters are much slower then native code, so the goal is to make as litle statements (which makes as many as possible) as possible - so better is to make them larger than to have more of them. When U use WHILE loop than everything inside that loop is interpreted n-times .... so thats the reason why it should be avoided -not because cursors are slow. It should be avoided because cursors means loop, and that means n-steps of interpreter. So from that point of view both scripts uses extremly unoptimal solution - WHILE loop.

Funny that author of that script also pointed out THIS (http://www.sqlteam.com/item.asp?ItemID=3856) article in other THREAD (http://www.codeguru.com/forum/showthread.php?t=374837).

Best regards,
Krzemo.

PS: Another my tip: IMHO if two solutions produse the same results with camparable time, than the best solution (in long term) is that which is simpler ;)