This week’s post will be a short one to talk about a very helpful stored procedure in SQL Server called “sp_MSForEachDB”.

I ran across this stored procedure when trying to drop a login from multiple databases.  The uses of this stored procedure can go beyond just dropping a login, it can be used to apply database changes to multiple databases very easily.

Below are a few examples of how I have used it in the past:

EXEC sp_MSforeachdb

@command1=’use ?; exec sp_changedbowner ”sa”’

EXEC master.dbo.sp_MsForEachDB ‘USE [?]; GRANT VIEW Definition TO username;’

This stored procedure is an undocumented one, which means it is unsupported and not guaranteed to be in future versions of SQL Server.  I can confirm the use of the stored procedure in SQL Server 2016.

For more about sp_MSForEachDB head over to MSSQLTIPS.

The post Helpful Undocumented Stored Procedure “sp_MsForEachDB” appeared first on VitaminDBA.