How to select all the tables

Question

Post reply

How to select all the tables

ash0550

Mr or Mrs. 500

Points: 517
More actions
June 27, 2011 at 10:21 am

#240482

In the Adventure works DB If i want select all the tables under Production at once without mentioning in particular
How to do it. Is it possible

Viewing 12 posts - 1 through 11 (of 11 total)

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

Is this what you want?

USE AdventureWorks

GO

SELECT *

FROM sys.Tables

GO

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ninja's_RGR'us

SSC Guru

Points: 294069

June 27, 2011 at 10:27 am

#1344653

What do you mean by select?

GSquared SSC Guru Points: 260824 More actions · Answer 3

If what you're looking for is a Select statement that will pull data from all of the tables in that schema, you'll either need to build that statement yourself, or use dynamic SQL to build it based on querying sys.tables and sys.columns.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

ash0550 (6/27/2011)
In the Adventure works DB If i want select all the tables under Production at once without mentioning in particular
How to do it. Is it possible

Yes, it is possible. Production is a schema, and you can select the names of all tables in a schema pretty easily. I won't mention how to do it, since you asked me not too 🙂

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 5

Please post how to select the tables belonging to a certain schema 🙂

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

stormsentinelcammy (6/27/2011)
Please post how to select the tables belonging to a certain schema 🙂

Please start a new thread 🙂 The OP wanted to know if it was possible, not how to do it. Let them learn it on their own.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

stormsentinelcammy SSCrazy Points: 2493 More actions · Answer 7

HAHA, you can just google the answer, I was just asking because you could have easily posted the answer...good point though 🙂

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 8

stormsentinelcammy (6/27/2011)
HAHA, you can just google the answer, I was just asking because you could have easily posted the answer...good point though 🙂

Say again? 😀

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

opc.three (6/27/2011)
stormsentinelcammy (6/27/2011)
Please post how to select the tables belonging to a certain schema 🙂
Please start a new thread 🙂 The OP wanted to know if it was possible, not how to do it. Let them learn it on their own.

Really? You must be having a really bad day, Orlando. 😉 From the original post...

ash0550 (6/27/2011)
In the Adventure works DB If i want select all the tables under Production at once without mentioning in particular
[font="Arial Black"]How to do it. [/font]Is it possible

--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)

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 10

Jeff Moden (6/27/2011)
opc.three (6/27/2011)
stormsentinelcammy (6/27/2011)
Please post how to select the tables belonging to a certain schema 🙂
Please start a new thread 🙂 The OP wanted to know if it was possible, not how to do it. Let them learn it on their own.
Really? You must be having a really bad day, Orlando. 😉 From the original post...
ash0550 (6/27/2011)
In the Adventure works DB If i want select [font="Arial Black"]all the tables under Production at once without mentioning in particular
How to do it. [/font]Is it possible

Geez, well I thought was having a great day yesterday but maybe not, at least not on this one 🙂 I guess I read it as a disjointed sentence given some of the wording in the post so I was just trying to honor the request not to give the entire answer. I looked at some of the OPs other posts and could tell they are just starting out.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 11

Heh... it's one of those "language barrier" things that I've seen so often and have had to interpret so often at some companies I've worked for. Unless I'm seriously mistaken the following...

...all the tables under Production at once without mentioning in particular

... actually means...

...all the tables under Production at once without hardcoding the names

--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)