looping thru views

Question

looping thru views

Viewing 4 posts - 16 through 18 (of 18 total)

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

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 1

I suppose you could take what was said two ways. I may have taken it the wrong way.

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Jeff Moden (3/15/2013)
I suppose you could take what was said two ways. I may have taken it the wrong way.

It is like I always tell me wife...

"If there are two ways to take something and one of them makes you mad, I meant it the other way." 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Jeff Moden (3/14/2013)
Alan.B (3/7/2013)
Cursors, loops and dSQL fall under the last choice column but this is one of those cases...
For tables you would do this:
EXEC sp_MSforeachtable'SELECT TOP 1 * FROM ?'
BWAA-HAAA!!!! Oh, be careful now, Alan. You've just stated perhaps one of the greatest oxymorons of them all. Have you ever looked under the hood of sp_MSForEachTable? It's a monstorous LOOP! 😉

Ok, ok... This is as set-based as I can get....

CREATE PROC dbo.top1FromSomething (@obj varchar(12)='BASE TABLE') --Options: 'BASE TABLE' OR 'VIEW'

AS

SET NOCOUNT ON;

DECLARE @sql VARCHAR(8000)

SELECT @sql=

COALESCE(@sql,'')+

CAST('SELECT TOP 1 * FROM ['+

TABLE_CATALOG+'].['+TABLE_SCHEMA+'].['+TABLE_NAME+']'+CHAR(13) AS VARCHAR(8000))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE=@obj

PRINT 'These queries were executed:'+CHAR(13)+REPLICATE('-',40)+CHAR(13)+@sql

EXEC(@sql)

GO

EXEC top1FromSomething 'VIEW';

Edit: tiny code change.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Alan Burstein SSC Guru Points: 61141 More actions · Answer 4

Jeff Moden (3/15/2013)
I suppose you could take what was said two ways. I may have taken it the wrong way.

I was not 100% clear in what I said. I was implying that sp_msforeach(table | Db) falls under the category of dsql and is, therefore not the best option. The newer code I posted still uses dsql but is free of any loops, cursors, RBAR (Hoo-uh).

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001