BACKGROUND:
I know how to run query against all databases on an instance, but I find that it returns correct results for some of the databases and incorrect ones for the other databases. All my databases have identical schema so there is no reason for the query to work agains some databases correctly and produce fake results for other databases. Basicaly query should show if record XYZ exists in Users table. If record exists query should return relevant record. Otherwise it should print NULL.
This is my query:
CREATE TABLE ##tempUsers(DB_Name VARCHAR(MAX), userID VARCHAR(MAX), role VARCHAR(MAX));
GO
DECLARE @command varchar(1000)
SELECT @command = 'Use ? INSERT INTO ##tempUsers select ''?'', userID, role from users where userID = ''XYZ'';'
EXEC sp_MSforeachdb @command
GO
SELECT Name, UserID, Role
FROM ##tempUsers i RIGHT JOIN sys.databases o
ON o.name = i.db_name
GO
DROP TABLE ##tempUsers;
PROBLEM:
Query correctly identifies some of the databases containing given record in given table. There are also some databases that query shows as NULL even though they have the record that is needed. For such databases NULL is returned even though they contain record in question. Schema is identical across all databases and the databases are not case sensitive.
Through elimination I was able to find that the following part of the query is only inserting results from some databases to the temporary table.
SELECT @command = 'Use ? INSERT INTO ##tempUsers select ''?'', userID, role from users where userID like ''%tempUsers%'';'
EXEC sp_MSforeachdb @command
WHAT I TRIED
1) Running simple SELECT column1, Column2 against each of the databases that was mistakingly identified as not containing record produces correct results.
2) I tried to replace = '' with LIKE %%, but it made no difference.
QUESTION:
How can I alter my query so that it returns corrent results for all databases and not just for some of them?
