Curtis Long
asked on
Select data from all tables in a MS SQL database
I have a need to select all the data from all the tables in a sql query. The problem is is that at the time I select the data, I do not necessarily know the names of all the tables, since they are created programatically and named based on information read from XML files. It would be great if I could try something like "SELECT * FROM *" but that doesn't work. I know what the database will be named all the time, and I know the template that is used to create the table names. Anyone have any ideas?
ASKER
Is there a way that I could use this information in the SQL Management Studio to iterate through all the tables and select the data from all the tables?
- "show databases" to get a list of all databases
- "use databasename" to select your database
- "show tables" to get a list of all tables in the current database
or
select TABLE_NAME from information_schema.tables where TABLE_SCHEMA = 'yourdatabasename';
- "use databasename" to select your database
- "show tables" to get a list of all tables in the current database
or
select TABLE_NAME from information_schema.tables where TABLE_SCHEMA = 'yourdatabasename';
oops - forget my answer - that way mysql syntax
>> I have a need to select all the data from all the tables in a sql query.
if you have same number of columns and datatype for the columns in all of your tables, then you can write a query like below dynamically.
select * from table1 union all
select * from table2 union all
select * from table3
otherwise, you cannot simply write a query like that. If you have the tables structure similarly and looking for a dynamic sql, just let me know.
if you have same number of columns and datatype for the columns in all of your tables, then you can write a query like below dynamically.
select * from table1 union all
select * from table2 union all
select * from table3
otherwise, you cannot simply write a query like that. If you have the tables structure similarly and looking for a dynamic sql, just let me know.
you can try using the undocumented sp_MSForEachTable procedure. not sure if this serves your purpose.
the following will return tablename and number of rows in the table. replace count(*) with * to get all the data.
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
the following will return tablename and number of rows in the table. replace count(*) with * to get all the data.
EXEC sp_MSForEachTable 'SELECT ''?'', COUNT(*) FROM ?'
depends what do you plan to do with all the data:
---for example as answer for your question you can use this code:
----
use yourdatabasename
exec sp_msforeachtable 'print ''?'' Select top * from ?'
---for example as answer for your question you can use this code:
----
use yourdatabasename
exec sp_msforeachtable 'print ''?'' Select top * from ?'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked perfectly once i changed count(*) to just * Thanks for your help
SELECT *
FROM sys.Tables
NB: you need to run this query once you are connected to DB, as you mentioned you know the name of the DB already.
Hope that helps! :)