In one of my applications we had tsql like shown below
SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in ( SELECT [name] FROM master.dbo.sysdatabases )
For one of my customers it gave an error related to collation as the database
Common (part of my application) has default collation
sql_latin1_general_cp1_ci_as whereas master db has collation
latin1_general_ci_as. I found a solution using collate and it worked. I want the solution to work on all collations.
SELECT DISTINCT [Dblist].[DbName] AS [DbName] FROM [Common].dbo.[Dblist] WHERE dbname not in ( SELECT [name] COLLATE DATABASE_DEFAULT FROM master.dbo.sysdatabases )
Now I am confused. Should I use COLLATE in all queries which involve system databases? When to use collate and when not to use?
Also, is the above way of using collate proper (like COLLATE DATABASE_DEFAULT)? Here DATABASE_DEFAULT is latin1_general_ci_as itself as it is the collation of master database. Then how did the solution work as they again don't match with Common database? I want solution which will work on all collations.