Searching Databases

Question

Hi. I am using SQL server 2000 and want to write a simple queary 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.

Answer 1

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

Answer 2

This query should do what you need, if the tool does all of the searching for you; it will return the database name if the MAX propertyid <=905.

SELECT DB_NAME() as DatabaseName
FROM dbo.defs_equipmentpropertytable
HAVING MAX(equipmentpropertyid) <= 905

Hopefully, that gets what you need. If your database crawling tool doesn't work with this, you can certainly use the method Steve provided.