Q:

Searching Databases

I am using SQL Server 2000 and want to write a simple query to return the name of all databases not containing equipmentpropertyid number above 905 in the defs_equipmentpropertytable.  We have a hundred or more active databases all containing the same tables.  Unfortunately, the defs_equipmentpropertytable is up-to-date in most of the databases, but not all.  I need to figure out which databases need to have that particular table updated.  We have a tool that will search all the databases, I just need the correct syntax for the query.   
Can anyone help? 
Thanks. 

A:

Someone may mention the undocumented stored procedure sp_msforeachdb, which can do this.  However, I suggest you write a query to generate the SQL yourself, then run it.  Something like this should work:
declare @query nvarchar(400);
set @query = N'
select top 1 ''Found in '' + %%% as r
from $$$.dbo.defs_equipmentpropertytable
where equipmentpropertyid > 905
go
';
select replace(replace(@query,'$$$',quotename(name)),'%%%',quotename(name,''''))
from master..sysdatabases

Run this, then copy the result back to a query window and execute the result.  You can also filter out any databases you don't want. 
I strongly suggest this instead of sp_MSforeachdb, because this gives you the chance to inspect your SQL before you run it, and that's a good habit for avoiding SQL injection, not to mention bad mistakes. 
I had it backwards.  Does this work? 
declare @query nvarchar(400);
set @query = N'
if not exists (
  select *
  from $$$.dbo.defs_equipmentpropertytable
  where equipmentpropertyid > 905
) select %%% + 'is not up to date' as r
go
';
select replace(replace(@query,'$$$',quotename(name)),'%%%',quotename(name,''''))
from master..sysdatabases

