looping thru views

Question

Post reply

looping thru views

rummings

SSCarpal Tunnel

Points: 4575
More actions
March 7, 2013 at 2:29 pm

#272080

I have a database with a large number of views. I would like to to be able to loop through the views and run a select on first record in each table from each of the views. Any advice or suggestions would be greatly appreciated.
Charlie

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

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

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

This should get you started..

declare @strSQL varchar(max)

declare @viewName varchar(255)

declare listViews cursor for

select name from sys.views

open listViews

fetch next from listViews into @viewName

while @@fetch_status = 0

begin

set @strSQL = 'select top 1 * from ' + @viewName

exec (@strSQL)

fetch next from listViews into @viewName

end

close listViews

deallocate listViews

Alan Burstein SSC Guru Points: 61136 More actions · Answer 2

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 ?'

For views:

SET NOCOUNT ON;

GO

IF OBJECT_ID('tempdb..#views') IS NOT NULL

DROP TABLE #views;

;WITH views AS

(SELECTROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n,

TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS)

SELECT n, table_name

INTO #views

FROM views;

GO

--SELECT * FROM #views

DECLARE @n int=1,

@tbl varchar(100),

@sql varchar(400)='SELECT TOP 1 * FROM ';

WHILE @n<=(SELECT MAX(n) FROM #views)

BEGIN

SELECT @tbl=(SELECT TABLE_NAME FROM #views WHERE n=@n);

EXEC('SELECT '''+@tbl+''' AS [THE TABLE]');

EXEC(@sql+@tbl);

SELECT @n=@n+1

END

DROP TABLE #views;

"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

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 3

Erin,

Thank you very much for the information!

Charlie

rummings SSCarpal Tunnel Points: 4575 More actions · Answer 4

Alan,

Thank you for the code.... it worked great!

Charlie

Alan Burstein SSC Guru Points: 61136 More actions · Answer 5

rummings (3/11/2013)
Alan,
Thank you for the code.... it worked great!
Charlie

Any time.

"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

kevaburg SSCoach Points: 18131 More actions · Answer 6

kevaburg

SSCoach

Points: 18131

March 14, 2013 at 8:07 am

#1597017

What about using sp_MSforeachview?

kevaburg SSCoach Points: 18131 More actions · Answer 7

What about using:

exec sp_MSforeachview 'select top 1 from ?'

Sean Lange SSC Guru Points: 286573 More actions · Answer 8

Keep in mind that all of the code samples are using top 1 with no order by. If you require the "first" record to be meaningful you will need to add this order by. If on the other hand you just want any given row from the view this will work.

_______________________________________________________________

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: 61136 More actions · Answer 9

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.

"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

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

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/[/url]

_______________________________________________________________

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/

kevaburg SSCoach Points: 18131 More actions · Answer 11

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 SSC Guru Points: 286573 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 13

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.

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: 286573 More actions · Answer 14

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