Query all schemas.

Question

Query all schemas.

SQLChao

Mr or Mrs. 500

Points: 534
More actions
February 18, 2014 at 11:01 am

#304324

Say I have 100 schemas all with the table "orders" is there an easy way to write one query to query them all?
cust1.orders
cust2.orders
cust3.orders
cust4.orders
etc...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004421 More actions · Answer 1

jiayi345 (2/18/2014)
Say I have 100 schemas all with the table "orders" is there an easy way to write one query to query them all?
cust1.orders
cust2.orders
cust3.orders
cust4.orders
etc...

Yes... write a spot of dynamic SQL to create a UNION ALL view and then query the view.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lowell SSC Guru Points: 323518 More actions · Answer 2

use the metadata to build a query;

probably featuring a UNION ALL if you want them in a single resutl set

SELECT

'SELECT ' + schema_name(schema_id)+ ' AS SchemaName,* FROM '

+ quotename(schema_name(schema_id)) + '.'

+ quotename(name) + ' UNION ALL ;' AS cmd

FROM sys.tables

WHERE name = 'Orders'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLChao Mr or Mrs. 500 Points: 534 More actions · Answer 3

Lowell (2/18/2014)
use the metadata to build a query;
probably featuring a UNION ALL if you want them in a single resutl set
SELECT
'SELECT ' + schema_name(schema_id)+ ' AS SchemaName,* FROM '
+ quotename(schema_name(schema_id)) + '.'
+ quotename(name) + ' UNION ALL ;' AS cmd
FROM sys.tables
WHERE name = 'Orders'

This is how I'm currently doing it. I didn't know if there was a better way.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You could also create a view with all the schemas.

I can't imagine why would you have all those schemas.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

SQLChao Mr or Mrs. 500 Points: 534 More actions · Answer 5

I'm not sure what the original intent was as I didn't create it. Thanks everyone for the suggestions.