Post reply

Avoiding a Cursor

  • February 29, 2012 at 1:59 am

    #257623

    I have a script which creates a temporary table #sequences.

    One of the columns in #sequences is table_name which holds the value of table names based on a query.

    There is another column, max_id, that I want to populate with the maximum id value from the corresponding table_name table in the database.

    I could write a cursor to step throught each row in #sequences passing table_name in a piece of dynamic sql but I'm trying to be a good SQL Server DBA and steer clear of RBAR.

    Is there anyone out there who can guide me back on to the path of righteousness?

  • February 29, 2012 at 2:12 am

    #1452984

    Hi

    Difficult to say without seeing much more info like DLL's and Data.

    However taking a shot in the dark would the undocumented procedure msforeachtable be of use to you

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • February 29, 2012 at 2:17 am

    #1452986

    Thanks Andy.

    That may be of use to me but I need a way of linking the value in the id column of the table to the table name. Hope this makes sense.

  • February 29, 2012 at 2:24 am

    #1452988

    Do the ID columns all have the same name or are they different?

    If so this would work I think

    CREATE TABLE #temp (TableName VARCHAR(200),TableCount BIGINT)

    INSERT #temp

    EXEC sp_MSForEachtable 'select ''?'',Max(ID) from ?'

    SELECT * FROM #temp

    DROP TABLE #temp

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • February 29, 2012 at 2:25 am

    #1452989

    are the ids identity columns , u cud try DBCC CHECKIDENT

    Jayanth Kurup[/url]

  • February 29, 2012 at 2:33 am

    #1452992

    Perfect Andy. That's exactly what I need.

    Thanks Jayeth too.

    This really is the premier SQL site!

    Just need to add a coalesce to handle tables that don't have an id column.

  • February 29, 2012 at 2:34 am

    #1452993

    Your Welcome 🙂

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • February 29, 2012 at 3:02 am

    #1453010

    Not trying to be picky, but sp_MsForEachTable uses a cursor internally.

    -- Gianluca Sartori

  • February 29, 2012 at 3:06 am

    #1453011

    Interesting...

    Does this mean I'm going to have to use a cursor for this operation, one way or another?

  • February 29, 2012 at 3:16 am

    #1453016

    Definitely yes. Cursors are not as bad as they may seem, just make sure you're using them in the right way.

    The one used in sp_MsForEachTable is declared GLOBAL, but you can make a better one using STATIC LOCAL READ_ONLY FORWARD_ONLY.

    Moreover, sp_MsForEachTable (as the name suggests) iterates through EACH table, and you want those with an "id" column.

    SELECT name

    FROM sys.tables

    WHERE object_id IN (

    SELECT object_id

    FROM sys.columns

    WHERE name = 'id'

    )

    This should be a smaller set.

    -- Gianluca Sartori

  • February 29, 2012 at 3:20 am

    #1453019

    Not trying to be picky, but sp_MsForEachTable uses a cursor internally.

    Interesting I didn't know this, but now I think about it makes sense I suppose...

    Learn something new every day! 😉

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • February 29, 2012 at 3:25 am

    #1453024

    five_ten_fiftyfold (2/29/2012)

    Interesting...

    Does this mean I'm going to have to use a cursor for this operation, one way or another?

    You could create your scripts dynamically into a variable then use sp_executesql to execute it in one batch.

    Without sample data, that's as much guidance as I can offer.

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • February 29, 2012 at 3:35 am

    #1453028

    Once again many thanks.

  • February 29, 2012 at 3:49 am

    #1453033

    duplicate posting removed

  • February 29, 2012 at 7:09 am

    #1453140

    Andy Hyslop (2/29/2012)

    Not trying to be picky, but sp_MsForEachTable uses a cursor internally.

    Interesting I didn't know this, but now I think about it makes sense I suppose...

    Learn something new every day! 😉

    Script out the proc. It's a butt ugly cursor. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply