InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 18:07:38 GMT20http://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]Jeff Moden (3/15/2013)[/b][hr]I suppose you could take what was said two ways. I may have taken it the wrong way.[/quote]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).Fri, 15 Mar 2013 12:00:14 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]Jeff Moden (3/14/2013)[/b][hr][quote][b]Alan.B (3/7/2013)[/b][hr]Cursors, loops and dSQL fall under the last choice column but this is one of those cases... For tables you would do this: [code="sql"]EXEC sp_MSforeachtable'SELECT TOP 1 * FROM ?'[/code][/quote]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! ;-)[/quote]Ok, ok... This is as set-based as I can get.... [code="sql"]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) GOEXEC top1FromSomething 'VIEW';[/code]Edit: tiny code change.Fri, 15 Mar 2013 09:24:03 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]Jeff Moden (3/15/2013)[/b][hr]I suppose you could take what was said two ways. I may have taken it the wrong way.[/quote]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." :-DFri, 15 Mar 2013 08:19:37 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxI suppose you could take what was said two ways. I may have taken it the wrong way.Fri, 15 Mar 2013 08:01:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]Jeff Moden (3/14/2013)[/b][hr][quote][b]Alan.B (3/7/2013)[/b][hr]Cursors, loops and dSQL fall under the last choice column but this is one of those cases... For tables you would do this: [code="sql"]EXEC sp_MSforeachtable'SELECT TOP 1 * FROM ?'[/code][/quote]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! ;-)[/quote]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.Fri, 15 Mar 2013 07:45:00 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]Alan.B (3/7/2013)[/b][hr]Cursors, loops and dSQL fall under the last choice column but this is one of those cases... For tables you would do this: [code="sql"]EXEC sp_MSforeachtable'SELECT TOP 1 * FROM ?'[/code][/quote]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! ;-)Thu, 14 Mar 2013 22:17:15 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]kevaburg (3/14/2013)[/b][hr]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![/quote]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.Thu, 14 Mar 2013 13:24:06 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxOooooops! 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!Thu, 14 Mar 2013 13:16:18 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]Alan.B (3/14/2013)[/b][hr][quote][b]kevaburg (3/14/2013)[/b][hr]What about using:exec sp_MSforeachview 'select top 1 from ?'[/quote]That is not a stored proc that comes from SQL Server. If you can run this:[code="sql"]exec sp_MSforeachview 'select top 1 from ?[/code]Its because someone created that locally.[/quote]LOL a quick search revealed this: [url=http://www.sqlservercentral.com/scripts/T-SQL+Aids/30373/]http://www.sqlservercentral.com/scripts/T-SQL+Aids/30373/[/url]Thu, 14 Mar 2013 13:01:49 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]kevaburg (3/14/2013)[/b][hr]What about using:exec sp_MSforeachview 'select top 1 from ?'[/quote]That is not a stored proc that comes from SQL Server. If you can run this:[code="sql"]exec sp_MSforeachview 'select top 1 from ?[/code]Its because someone created that locally.Thu, 14 Mar 2013 11:05:43 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxKeep 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.Thu, 14 Mar 2013 09:50:35 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxWhat about using:exec sp_MSforeachview 'select top 1 from ?'Thu, 14 Mar 2013 08:11:17 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxWhat about using sp_MSforeachview?Thu, 14 Mar 2013 08:07:45 GMTkevaburghttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspx[quote][b]rummings (3/11/2013)[/b][hr]Alan, Thank you for the code.... it worked great!Charlie[/quote]Any time.Mon, 11 Mar 2013 09:04:35 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxAlan, Thank you for the code.... it worked great!CharlieMon, 11 Mar 2013 08:41:01 GMTrummingshttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxErin, Thank you very much for the information!CharlieMon, 11 Mar 2013 08:40:20 GMTrummingshttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxCursors, loops and dSQL fall under the last choice column but this is one of those cases... For tables you would do this: [code="sql"]EXEC sp_MSforeachtable'SELECT TOP 1 * FROM ?'[/code]For views: [code="sql"]SET NOCOUNT ON;GOIF 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_nameINTO #viewsFROM views;GO--SELECT * FROM #viewsDECLARE @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+1ENDDROP TABLE #views;[/code]Thu, 07 Mar 2013 15:23:57 GMTAlan.Bhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxThis should get you started..[code="SQL"]declare @strSQL varchar(max)declare @viewName varchar(255)declare listViews cursor forselect name from sys.viewsopen listViewsfetch next from listViews into @viewNamewhile @@fetch_status = 0 begin set @strSQL = 'select top 1 * from ' + @viewName exec (@strSQL) fetch next from listViews into @viewName end close listViewsdeallocate listViews[/code]Thu, 07 Mar 2013 15:18:54 GMTErin Ramsayhttp://www.sqlservercentral.com/Forums/Topic1428269-1292-1.aspxI 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.CharlieThu, 07 Mar 2013 14:29:05 GMTrummings