Posts Tagged ‘sp_MSforeachdb’

Today, I came across a situation, where I had to perform an activity similar to temporary table cleaning in more than one database. So I started thinking about the shortest possible solution. After some research, I found two possible solutions and one of them was sp_MSforeachdb. It is basically an undocumented stored procedure in SQL Server and it is very handy when you need to perform / achieve some tasks in different databases at a time. However, I need to perform the above mentioed task NOT IN all databases but few of them only. So I started doing some testing to see how to achieve database filter in the sp_MSforeachdb. After couple of testings, I finally found the way to execute a set of commands on specific databases using sp_MSforeachdb.
Given below is the script.

--This Script is compatible with SQL Server 2005 and above.
EXEC sp_MSforeachdb
@command1='IF ''?''
IN (''AdventureWorks2012'',''AdventureWorks2012_test'')
       SELECT name,object_id,modify_date
       FROM ?.sys.tables WHERE [name]=''Person''


Let me know if you came across these situations and what was the solution ?

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

Read Full Post »