Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to extract the contents of the same TBL name from all databases?

Posted on 2009-04-26
7
Medium Priority
?
200 Views
Last Modified: 2012-05-06
i have a TBL name called "vendor_list"
the same table exists in almost 120 databases.
i need to extract the content for "vendor_list" from all 120 databases.

is there a single SQL that can do this?
i can be logged in as a windows authenticated user.

thank you.
0
Comment
Question by:rastafaray
  • 3
  • 3
7 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 24237430
It depends on your definition of a "single SQL":

sp_msforeachdb
'USE [?]
IF EXISTS(SELECT 1 FROM sysobjects WHERE xtype = ''U'' and name=''Vendor_List'')
      SELECT      *
      FROM      Vendor_List'


0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24237496
Hello rastafaray,

If what you meant was, return the contents of all tables in a single query, then assuming all the DBs are on
the same server...

SELECT * FROM Database1.dbo.Vendor_List
UNION ALL
SELECT * FROM Database2.dbo.Vendor_List
UNION ALL
SELECT * FROM Database3.dbo.Vendor_List
UNION ALL

SELECT * FROM DatabaseN.dbo.Vendor_List

Regards,

Patrick
0
 

Author Comment

by:rastafaray
ID: 24237497
this helps.  i think i do need the dbase name before Vendor_List in "FROM      Vendor_List'"

can this work?

FROM      [?].Vendor_List

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24237521
rastafaray,

The example I provided shows how to qualify the table name with the database name first (e.g., Database1)
and the table owner (assumed dbo) before the table name.  SInce you said the table name was always the
same, I held the Vendor_List table name constant.

Regards,

Patrick
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24237530
BTW, my example is also assuming that Vendor_List has the same columns, in the same order and
with the same data types, across all the databases you are using...
0
 

Author Closing Comment

by:rastafaray
ID: 31574739
this "FROM [?].Vendor_List" part worked too :)  
thank you
0
 

Author Comment

by:rastafaray
ID: 24237535
thanks again matthewspatrick

acperkins's suggestions seems to have worked.

best,

r

0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

571 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