Saturday, July 15, 2023

SQL Server - How to run a query for all the database(s) in SQL Server Instance

We will use sp_MSforeachdb procedure which is an undocumented procedure that allows you to run the same command against all databases. Below is the sample code:

DECLARE @command VARCHAR(2000)

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN USE [?] SELECT [name] FROM sys.database_principals WHERE type in (''U'',''G'') 
AND name LIKE ''%$''  END '

EXEC sp_MSforeachdb @command

No comments:

Post a Comment