Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 7, 2013 2:29 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:43 AM
Points: 121, Visits: 386
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
Post #1428269
Posted Thursday, March 7, 2013 3:18 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:41 PM
Points: 544, Visits: 1,053
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

Post #1428289
Posted Thursday, March 7, 2013 3:23 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 581, Visits: 2,712
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
( SELECT ROW_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;



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1428292
Posted Monday, March 11, 2013 8:40 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:43 AM
Points: 121, Visits: 386
Erin,

Thank you very much for the information!

Charlie
Post #1429306
Posted Monday, March 11, 2013 8:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 11:43 AM
Points: 121, Visits: 386
Alan,

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

Charlie
Post #1429308
Posted Monday, March 11, 2013 9:04 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 581, Visits: 2,712
rummings (3/11/2013)
Alan,

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

Charlie


Any time.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1429327
Posted Thursday, March 14, 2013 8:07 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
What about using sp_MSforeachview?
Post #1430988
Posted Thursday, March 14, 2013 8:11 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 15, 2014 12:19 AM
Points: 315, Visits: 510
What about using:

exec sp_MSforeachview 'select top 1 from ?'
Post #1430991
Posted Thursday, March 14, 2013 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:49 PM
Points: 12,946, Visits: 12,355
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 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 #1431079
Posted Thursday, March 14, 2013 11:05 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 6:32 AM
Points: 581, Visits: 2,712
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1431135
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse