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.
-- Itzik Ben-Gan 2001