How to write a CURSOR loop over multiple databases in MSSQL 2000

I sure lot of people might want to use the script where they would want to loop over multiple databases sitting on a Single server. In my case, I had to check for  a Login Record  in all the databases since the person left the company and we wanted to disable his/her access. SO I wrote simple CURSOR script in MSSQL 2000. Hope this help to someone. Â

[sql]USE master

DECLARE db_names CURSOR FOR
SELECT name from sysdatabases
— Where Clause, Enable if you want to filter out some databases.
–where name like ‘databaseIwantName_%’ and name not like ‘databaseIdontwantName’

DECLARE @db_name varchar(100)
DECLARE @QueryString NVARCHAR(500)
DECLARE @UpdateString NVARCHAR(500)

OPEN db_names
FETCH NEXT FROM db_names INTO
@db_name
WHILE @@FETCH_STATUS = 0
BEGIN
–PRINT @db_nam
–In My case I used tbl_logins to do multiple select .
SET @QueryString = ‘Select * from ‘ + @db_name + ‘..logins where login like ”%loginname%” ‘
EXEC sp_executesql @QueryString

IF @@RowCount > 0
BEGIN
— This gives the database name if there are any record found with the select state ran above
PRINT @db_name
END

–Similarly you can also have any Insert/Update Query IN a Loop.See example below
–In My case I used tbl_logins to do multiple Update At Once.
–SET @UpdateString = ‘update ‘ + @db_name + ‘..tbllogins set password = ”account_blocked_out”, security_level = 0, approved = 0 where login like ”%loginname%”’
–PRINT @UpdateString
–EXEC sp_executesql @UpdateString

FETCH NEXT FROM db_names INTO
@db_name
END

CLOSE db_names
DEALLOCATE db_names

[/sql]

 This is something written quick. Here is the Code for your Download. I am sure you can tune it better and if you do, please let me know . I would love to see your Ideas.

 Thanks