How to avoid cursors

  • 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)?

  • 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

  • Fair enough. Just out of curiosity, anyway: is there a way to do what I'm talking about?

  • scarr030 - Tuesday, February 21, 2017 9:08 AM

    Fair 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • scarr030 - Tuesday, February 21, 2017 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)?

    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

  • 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

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • 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