asked on

Returning a list of database names in which a criteria passed as a parameter exists for a table in a database

I’m trying to return a result set that will give me all of the database names in which a particular criteria exists in a particular table for that database. Example:

If for database Products_March05 exists ‘Tupperware’ in table ‘Products’, then return Products_March05, continue checking next db Products_April05.

Is this possible?

Thanks

Racim BOUDJAKDJI

<<Is this possible?>>
Yes. Hint: use sp_msforeachdb and sp_msforeachtable to browse through objects.

Racim BOUDJAKDJI

Something like ...should do (double check quotes, not ime to test)...

exec sp_msforeachdb 'if exists(select * from Products where field='''Tupperware''') print '''Database:''' ? '

ktt2

ASKER

I reasearched those but I thought that those undoc'd sp's cannot return resultsets to send back as a datatable to bind to a user control?

ASKER CERTIFIED SOLUTION

Racim BOUDJAKDJI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

billtadpole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

billtadpole

Oh, sorry Racimo, you got there before me with the same idea.

ktt2

ASKER

DECLARE @param1 int
SET @param1 = 1000

--Drop temp table if exists
IF Object_ID('tempdb..#productDBs')IS NOT NULL
      DROP TABLE #productDBs

--Now create temp table
CREATE TABLE #productDBs (dbName varchar(60))

--Prepare the @command1 string based on input
DECLARE @cmd1 VARCHAR(1000)
SET @cmd1 = 'IF EXISTS(SELECT * FROM sysobjects WHERE name = ''products_table'')
      BEGIN
            IF EXISTS(SELECT TOP 1 (prodCode) FROM products_table WHERE prodCode= ' + CAST(@param1 as varchar(4)) + ')
                  BEGIN
                        INSERT #productDBs SELECT ''?''
                  END
      END '
print @cmd1

--EXEC procedure
EXEC sp_msforeachdb @command1 = @cmd1

--Now select the entire resultset
SELECT * FROM #productDBs

--Finally Drop table
DROP TABLE #productDBs

This still gives me all of the db's and not the ones where prodCode = 1000

ktt2

ASKER

Fixed. Just added the database alias ?.. to each table

SET @cmd1 = 'IF EXISTS(SELECT * FROM ?..sysobjects WHERE name = ''products_table'')
BEGIN
IF EXISTS(SELECT TOP 1 (prodCode) FROM ?..products_table WHERE prodCode= ' + CAST(@param1 as varchar(4)) + ')
BEGIN
INSERT #productDBs SELECT ''?''
END
END '

Thanks guys....