Sep
27
2007
How to write a CURSOR loop over multiple databases in MSSQL 2000
Posted by: Rahmansaher in Technical, tags: Database Cursor, Looping Query, MSSQL Server 2000, Multiple Select, Multiple UpdateI 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
Entries (RSS)