Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

looping thru views Expand / Collapse
Author
Message
Posted Thursday, March 14, 2013 1:01 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 15,799, Visits: 16,163
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)
Post #1431199
Posted Thursday, March 14, 2013 1:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 15, 2016 3:29 AM
Points: 397, Visits: 773
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!
Post #1431211
Posted Thursday, March 14, 2013 1:24 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 15,799, Visits: 16,163
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)
Post #1431219
Posted Thursday, March 14, 2013 10:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 40,984, Visits: 38,281
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1431349
Posted Friday, March 15, 2013 7:45 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 15,799, Visits: 16,163
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)
Post #1431538
Posted Friday, March 15, 2013 8:01 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 40,984, Visits: 38,281
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."

Helpful Links:
How to post code problems
How to post performance problems
Post #1431547
Posted Friday, March 15, 2013 8:19 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 15,799, Visits: 16,163
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 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)
Post #1431561
Posted Friday, March 15, 2013 9:24 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 1,932, Visits: 6,444
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
Post #1431589
Posted Friday, March 15, 2013 12:00 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:00 PM
Points: 1,932, Visits: 6,444
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
Post #1431664
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse