SELECT from all tables in the database

Question

Post reply

SELECT from all tables in the database

vijju2k

SSCommitted

Points: 1531
More actions
October 2, 2003 at 10:33 am

#101696

Hi,
I have 20 tables in my database and all of them have the same column structure. I am trying to write a select statement, so that I can query all the tables at once and return matching records. Is it possible? If so can anyone help me out with an example.
thanks in advance,
v

Viewing 15 posts - 1 through 15 (of 29 total)

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

SQLBill SSC Guru Points: 51440 More actions · Answer 1

There's an undocumented Stored Procedure that can help:

This is from The Guru's Guide to Transact-SQL by Ken Henderson.

Procedure:

Sp_MSforeachtable

@command1

@replacechar = '?'

[,@command2]

[,@command3]

[,@whereand]

[,@precommand]

[,@postcommand]

Purpose:

Execute up to three commands for every table in a database (optionally matching the @whereand clause) @replacechar will be replaced with the name of each table. @precommand and @postcommand can be used to direct commands to a single result set.

Example:

EXEC sp_MSforeachtable @command1 = 'PRINT "Listing ?=', @command2='SELECT * FROM ?', @whereand=' AND name like "title%"'

-SQLBill

vijju2k SSCommitted Points: 1531 More actions · Answer 2

Thank you SQLBill for responding, I would have never found that "undocumented" stored procedure.

I tried executing it, the sp_MSforeachtable runs fines if I am only using select. For example:

exec sp_MSforeachtable @command1 = 'select col_name, col_date from ? "

but I get Server: Msg 207 error if I add the following line:

,@whereand = ' and datepart(year,col_date) = "2003"'

any ideas??

thank you once again,

v

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Is this how it looks when you added the whereand?

exec sp_MSforeachtable @command1 = 'select col_name, col_date from ?',@whereand = ' and datepart(year,col_date) = "2003"'

-SQLBill

vijju2k SSCommitted Points: 1531 More actions · Answer 4

thats exactly how it looks like when I type everything together. When I execute I get the following 4 lines of error messages:

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'col_date'

Server: Msg 207, Level 16, State 1, Line 1

Invalid column name '2003'

vijju2k SSCommitted Points: 1531 More actions · Answer 5

looks like I have found a solutions for this problem (or atleast a work around). I tried the following and it worked.

exec sp_MSforeachtable @command1 = ' select col_name, col_date ? where datepart(year,col_date) = 2003'

one strange phenomena though is if I typed 2003 as "2003" (enclosed in double quotes) then it gets back and gives me an error saying

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name '2003'.

Any thoughts????

thanks for your help SQLBill,

V

ksts71 Grasshopper Points: 12 More actions · Answer 6

Hi,

If you use "", [], {}... SQL sever translates it as a column. This is done because if you have a more than one word field name, or the field name containts characters other than _, 0 to 9, A to Z then you have to access it by enclosing it in a "", [] and so on pair, for example

SELECT [my Result]

FROM Tests

The only way to write a valid SQL string literal is by using single quotes or N and simple quotes if you want to to handle nchar, nvarvar or ntext columns

SQLBill SSC Guru Points: 51440 More actions · Answer 7

I can't figure out what could be wrong. That's directly from the book. You can even find the actual sp in Enterprise Manager (go to Master database, expand it, click on Stored Procedures and find MSforeachtable).

You might try 'toggling' SET QUOTED_IDENTIFIER on and off. Try it each way and see if it makes a difference.

-SQLBill

sdinning Old Hand Points: 363 More actions · Answer 8

Seems like you should just use a "union" query ...

Select * From table1

Union

Select * From table2

Union

...

Select * From table20

spongemagnet SSCrazy Points: 2812 More actions · Answer 9

Yea, what dinner said. A union query is the way to go for this. Using sp_msforeachtable will return multiple recordsets - using a union query will return only one recordset (making it look like you queried one table).. you could also create a view from a generic select * from tablea union select * from tableb and use that as a table.

-Ken

Aaron Dutton SSC-Addicted Points: 481 More actions · Answer 10

If I recall correctly, you can use UNION ALL instead of UNION and it should increase your performance slightly.

-Aaron

paulv Valued Member Points: 59 More actions · Answer 11

quote:
If I recall correctly, you can use UNION ALL instead of UNION and it should increase your performance slightly.
-Aaron

paulv Valued Member Points: 59 More actions · Answer 12

Is there a way, in this process, to add a column which inserts in each record the name of the table from which it came?

--Paul

Aaron Dutton SSC-Addicted Points: 481 More actions · Answer 13

A cheap way to do it would be in the union statement:

SELECT Table = "Table1", * FROM Table1

UNION

SELECT Table = "Table2", * FROM Table2

UNION

SELECT Table = "Table3", * FROM Table3

Not sure it that was what you were looking for.

paulv Valued Member Points: 59 More actions · Answer 14

Thanks, that would work (with ' instead of "), but actually, with many hundreds of identically structured tables, I was hoping for a way to insert each source table name automatically. I see that when I run just plain...

SELECT * FROM table1

UNION ALL

SELECT * FROM table2

UNION ALL

SELECT * FROM table3

... the result includes a column "tablename", but all the values I get in this column are NULL. Is there something I can do to get the table names (table1, table2, table3, etc.) to populate automatically in this column?

Thanks, Paul