Avoiding a Cursor

Question

Post reply

Avoiding a Cursor

five_ten_fiftyfold

Ten Centuries

Points: 1164
More actions
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?
This is a high-class Bureau-de-Change.

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

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

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

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

five_ten_fiftyfold Ten Centuries Points: 1164 More actions · Answer 2

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.

This is a high-class Bureau-de-Change.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

are the ids identity columns , u cud try DBCC CHECKIDENT

Jayanth Kurup[/url]

five_ten_fiftyfold Ten Centuries Points: 1164 More actions · Answer 5

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.

This is a high-class Bureau-de-Change.

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 6

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

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

-- Gianluca Sartori

five_ten_fiftyfold Ten Centuries Points: 1164 More actions · Answer 8

Interesting...

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

This is a high-class Bureau-de-Change.

spaghettidba SSC Guru Points: 105732 More actions · Answer 9

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

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 10

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 11

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/

five_ten_fiftyfold Ten Centuries Points: 1164 More actions · Answer 12

Once again many thanks.

This is a high-class Bureau-de-Change.

five_ten_fiftyfold Ten Centuries Points: 1164 More actions · Answer 13

duplicate posting removed

This is a high-class Bureau-de-Change.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 14

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)