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

Published by

Rahmansaher

Dynamic technology strategist and thought leader with 18 years’ achievement rolling-out business solutions and overseeing program/project management and product development in progressively responsible operational leadership roles. Experienced cross-functional and collaborative leader with a demonstrated track record of success at the helm of large (40+) multinational teams. Skilled in diverse software development methodologies, adept at driving seamless integration across technology platforms, effectively managing and coordinating large-scale projects across all phases. Creative thinker who intelligently manages resources, identifies potential partners, and fosters communication to improve the bottom-line.

2 thoughts on “How to write a CURSOR loop over multiple databases in MSSQL 2000”

  1. Thanks! I’m an Oracle person myself but need to write a loop for SQL; this gave me a good head start!

Leave a Reply

Your email address will not be published. Required fields are marked *