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'') BEGIN SELECT name,object_id,modify_date FROM ?.sys.tables WHERE [name]=''Person'' END' --OUTPUT
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.
Leave a Reply