SQL Server Portal

Today, I was working on customer’s test database and I wanted to delete all the tables having “_temp” in the names. Even though I could manually write the script by using a table at a time, I was looking for a shortcut. I have been using sp_MSforeachtable on test databases for multiple purposes. But as far as I know, it performs the specified operation in all tables in the particular database. So I started doing my research. First of all, I searched if there was any parameter available in the sp_MSforeachtable that we can use to filter tables objects and I found one parameter namely @whereand. Given below is the script to view the available parameters in sp_MSforeachtable.

EXEC sp_help sp_msforeachtable
--OUTPUT

SOLUTION :
You need to use the @whereand parameter to filter any table object in sp_MSforeachtable (Undocumented system stored procedure).
Given below is a script, where I will select table contains “_temp” in the name.

USE tempdb
GO
EXEC sp_msforeachtable
@command1 ='SELECT * FROM ?'
,@whereand = ' And Object_id In (Select Object_id From sys.objects
Where name like ''%_temp%'')'

Note : It is not recommended to use sp_MSforeachtable on production databases.