dinsdag 6 mei 2008

sp_MSforeachtable and sp_MSforeachdb

How simple can things be?

I just discovered these "undocumented" sp's. No more cursor creations for these kind of queries ;-). In combination with all those dbcc and dm_... statements and views (i.e. DBCC CLEANTABLE), a good thing to know. They both work in 2000 and 2005 (in 2005 there is no extra filtering on schema level :-s )


USE AdventureWorks

EXEC sp_MSforeachtable 'select ''?'',count(*) from ?'

DECLARE @command varchar(1000)
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name'

EXEC sp_MSforeachdb @command

Geen opmerkingen: