Displaying the name of the table when using sp_MSforeachtable

sp_MSforeachtable is very handy in displaying the contents of tables.

Besides displaying the contents, also displaying the name of the table can be done with the ? variable surrounded by ”.  Notice that for each single ‘, two must be used:

declare @q1 varchar(1000) =
‘
exec(”select ””?”” as [table_name]”)
declare @q varchar(1000)
set @q=”select top 10 * from ?”
exec(@q)’

EXEC sp_MSforeachtable @command1=@q1

Notes:

– If something doesn’t work, troubleshooting the contents of @q1 can be done with:

select @q1

-A filter can be added to the sp:

EXEC sp_MSforeachtable @command1=@q1,@whereand=’and object_id in (your_list_here) ‘

–the @whereand comes from:
select * from sys.objects where object_id in (your_list_here)