February 21, 2017 at 8:14 am
Hi All,
I'm now the new accidental DBA. We have some non-prod DBs, but most of the DBs are production DBs that have the same exact tables and structure (there may be some minute differences, but not really relevant). Very often, we will have to update something across all of the Prod DBs, and there are about 125 of them. The previous DBA had created a view on a non-Prod DB that was basically the name of every DB and it's DB ID.
So, the way he set up a bunch of Sql Server Agent tasks (for fixing known data issues at night, querying stats from each DB etc) was using this view to insert the DB name in front of the table(s) in a dynamic SQL query, and then have a cursor run each of the dynamic statements. See below for an example:
DECLARE @cmd NVARCHAR(400)
DECLARE A CURSOR FOR
SELECT '
SELECT
id
FROM ['+name+']..ACCOUNTS
'
FROM MONITORING..VW_PROD_DBs
OPEN A
FETCH NEXT FROM A INTO @CMD
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXECUTE sp_executesql @cmd
FETCH NEXT FROM A INTO @cmd
END
CLOSE A
DEALLOCATE A
Clearly, there are a few issues with this, but some of my main concerns are: A) Performance issues could come from this. B) I'd like to avoid using extended stored procedures, if possible. C) This strategy does not seem very flexible.
There are quite a lot of tasks set up with this dynamic sql/cursor strategy, and I'm weary of cursors.
Is there a way to achieve the same thing, but without the use of said strategy and without the use of sp_MSforEachDB (which I am also not a fan of)?
February 21, 2017 at 8:58 am
There is no compelling reason to avoid cursors and dynamic sql when you're doing something like coding an administrative script that is occasionally used to iterate across databases or servers. Whatever overhead the cursor or sql compilation adds to this process is negligible.
Where you do want to avoid cursors and dynamic sql is in your line of business stored procedures that are called routinely by the application.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 21, 2017 at 9:08 am
Fair enough. Just out of curiosity, anyway: is there a way to do what I'm talking about?
February 21, 2017 at 9:17 am
scarr030 - Tuesday, February 21, 2017 9:08 AMFair enough. Just out of curiosity, anyway: is there a way to do what I'm talking about?
You could write a while loop to loop over the databases without a cursor, you could generate the SELECTs based off the databases as one piece of dynamic SQL with all the selects and run that.
This is a process that has to be done per-database, so you're going to be looping in some way or other, or otherwise processing one database at a time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2017 at 9:44 am
scarr030 - Tuesday, February 21, 2017 8:14 AMHi All,I'm now the new accidental DBA. We have some non-prod DBs, but most of the DBs are production DBs that have the same exact tables and structure (there may be some minute differences, but not really relevant). Very often, we will have to update something across all of the Prod DBs, and there are about 125 of them. The previous DBA had created a view on a non-Prod DB that was basically the name of every DB and it's DB ID.
So, the way he set up a bunch of Sql Server Agent tasks (for fixing known data issues at night, querying stats from each DB etc) was using this view to insert the DB name in front of the table(s) in a dynamic SQL query, and then have a cursor run each of the dynamic statements. See below for an example:
DECLARE @cmd NVARCHAR(400)
DECLARE A CURSOR FORSELECT '
SELECT
id
FROM ['+name+']..ACCOUNTS
'
FROM MONITORING..VW_PROD_DBsOPEN A
FETCH NEXT FROM A INTO @CMD
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXECUTE sp_executesql @cmd
FETCH NEXT FROM A INTO @cmd
ENDCLOSE A
DEALLOCATE AClearly, there are a few issues with this, but some of my main concerns are: A) Performance issues could come from this. B) I'd like to avoid using extended stored procedures, if possible. C) This strategy does not seem very flexible.
There are quite a lot of tasks set up with this dynamic sql/cursor strategy, and I'm weary of cursors.
Is there a way to achieve the same thing, but without the use of said strategy and without the use of sp_MSforEachDB (which I am also not a fan of)?
This is a perfectly acceptable way to do this.
I have couple of minor improvements to the code though:
1) always prefix all objects by their schema. so exec dbo.sp_executesql ..., from monitoring.adminschema.vw_prod_dbs, etc.
2) Add FAST_FORWARD to the cursor definition. It is optimized and you never need to skip around or go backwards in the set.
3) There is no extended stored procedure in that code. sp_executesql is a regular system sproc.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 21, 2017 at 9:58 am
There's another way, but as mentioned before, it's not completely necessary to avoid a cursor in this case.
Here's an example and a link on how to do it.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/DECLARE @sql nvarchar(MAX);
SELECT @sql = ( SELECT 'SELECT '''+name+''', object_id '
+ 'FROM ['+name+'].sys.tables;' + CHAR(13)
FROM sys.databases
FOR XML PATH(''), TYPE).value('./text()[1]', 'varchar(max)')
EXECUTE sp_executesql @sql
February 21, 2017 at 10:07 am
I will add that doing your activities one-at-a-time gives you control over error handling too, which other mechanisms that may put all of your code in one statement may not be able to do. You may wish to just log the error and continue on with other databases for example (assuming the particular error caught allows continuation).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply