How to write a CURSOR loop over multiple databases in MSSQL 2000
September 27th, 2007
No comments
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
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
Sphere: Related Content
Categories: Technical Database Cursor, Looping Query, MSSQL Server 2000, Multiple Select, Multiple Update