I'm trying to adapt an SQL query to check the value present in a certain field that is present in every database on my server.
There are 100 individual databases, and I would like to check a specific record of each one.
The answer is probably to use a command like the one below, but I'm having difficulty adapting it.
'USE ?; SELECT DB_NAME()AS DBName,
COUNT(1)AS [Count] FROM CUSTOMERS'
SELECT [SettingName],[SettingValue] FROM [HostSettings] Where [SettingName] = 'SMTPServer'
DBName | SettingName | SettingValue
Database1 | SMTPServer | smtp.gmail.com
Database2 | SMTPServer | smtp.gmail.com
Database3 | SMTPServer | smtp.yahoo.com
DECLARE @T TABLE (DbName SYSNAME, [SettingName] VARCHAR(255), [SettingValue] VARCHAR(255)); INSERT INTO @T EXEC sp_MSForEachDB 'SELECT ''?'', [SettingName], [SettingValue] FROM ?..[HostSettings] WHERE [SettingName] = ''SMTPServer'''; SELECT * FROM @T;