Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


looping thru views


looping thru views

Author
Message
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
Alan.B (3/14/2013)
kevaburg (3/14/2013)
What about using:

exec sp_MSforeachview 'select top 1 from ?'


That is not a stored proc that comes from SQL Server. If you can run this:

exec sp_MSforeachview 'select top 1 from ?



Its because someone created that locally.


LOL a quick search revealed this: http://www.sqlservercentral.com/scripts/T-SQL+Aids/30373/

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
kevaburg
kevaburg
Mr or Mrs. 500
Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)Mr or Mrs. 500 (586 reputation)

Group: General Forum Members
Points: 586 Visits: 923
Oooooops! It would appear I am talking out of holes normally reserved for other purposes!

That is exactly the procedure I saw but I didn't read it properly! I take back what I said but it would be a nice procedure to have I think.....

On a serious note though, I have noticed that Intellisense finds sp_MSforeachtable and sp_MSforeachdb. Does that mean they are both documented and supported procedures. If so then my job has suddenly become a little simpler!
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
kevaburg (3/14/2013)
Oooooops! It would appear I am talking out of holes normally reserved for other purposes!

That is exactly the procedure I saw but I didn't read it properly! I take back what I said but it would be a nice procedure to have I think.....

On a serious note though, I have noticed that Intellisense finds sp_MSforeachtable and sp_MSforeachdb. Does that mean they are both documented and supported procedures. If so then my job has suddenly become a little simpler!


Both of those you mentioned are available. However they are both undocumented and unsupported. You can find lots of places online that discuss them and you can always crack them open yourself to have a look.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45390 Visits: 39941
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! ;-)

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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! ;-)


Actually Jeff he said that cursors and loops fall under the last category but that this IS one time it is acceptable.

I would of course recommend that anybody who wants to use any of the sp_MSForEach procs to crack them open and look at what they are doing.

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45390 Visits: 39941
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16667 Visits: 17030
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." :-D

_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2246 Visits: 7426
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.

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Alan.B
Alan.B
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2246 Visits: 7426
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).

-- Alan Burstein



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"I can't 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. " -- Itzek Ben-Gan 2001
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search