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!

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