Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-04-27
8
Medium Priority
?
250 Views
Last Modified: 2006-11-18
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
0
Comment
Question by:ktt2
  • 3
  • 3
  • 2
8 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16556654
<<Is this possible?>>
Yes.  Hint: use sp_msforeachdb and sp_msforeachtable to browse through objects.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 16556674
Something like ...should do (double check quotes, not ime to test)...

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

Author Comment

by:ktt2
ID: 16556711
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 1800 total points
ID: 16557044
Sometining like (Sorry no time to debug...)...Hope this helps...

create procedure spr_return_dbs
as
begin
create table AdventureWorks..table1(db_list varchar(30))
exec sp_msforeachdb 'if exists(select * from sysobjects where name = ''Products'') begin if exists(select * from Products where ltrim(field) = ''Tupperware'') begin insert AdventureWorks..table1 select ''?'' end end'
select * from AdventureWorks..table1
drop table AdventureWorks..table1
end
0
 
LVL 1

Assisted Solution

by:billtadpole
billtadpole earned 200 total points
ID: 16557083
If you can figure out how to use Racimo's idea, you could store each result in a temporary table and return the rows from that as follows

at the top of your stored proc add this:

create table #DBs (name sysname)

replace Racimo's print '''Database:'''
with

insert #DBs select '''Database:'''

at the bottom of the stored proc, add this:

select name from #DBs

But this is only a tweak to Racimo's solution.
0
 
LVL 1

Expert Comment

by:billtadpole
ID: 16557096
Oh, sorry Racimo, you got there before me with the same idea.
0
 

Author Comment

by:ktt2
ID: 16562772
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
0
 

Author Comment

by:ktt2
ID: 16563246
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....
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question