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)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: