Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1316
  • Last Modified:

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?
0
Curtis Long
Asked:
Curtis Long
  • 2
  • 2
  • 2
  • +3
1 Solution
 
himanshutCommented:
to get the list of all tables in your database simply use this:

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! :)
0
 
Curtis LongAuthor Commented:
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?
0
 
DonConsolioCommented:
- "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';
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
DonConsolioCommented:
oops - forget my answer - that way mysql syntax
0
 
SharathData EngineerCommented:
>> 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.
0
 
appariCommented:
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 ?'
0
 
Eugene ZCommented:
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 ?'
0
 
appariCommented:
or using cursor and information_schema views
set nocount on 

declare @table varchar(128)
declare @cmd varchar(500) 

declare tables cursor for
select top 5 table_name from information_schema.tables
   where table_type = 'base table'
open tables
fetch next from tables into @table
while @@fetch_status = 0
begin
  set @cmd = 'select ''' + @table + ''', count(*) from [' + @table + ']'
  exec (@cmd)
  fetch next from tables into @table
end
CLOSE tables 
DEALLOCATE tables

Open in new window

0
 
Curtis LongAuthor Commented:
This worked  perfectly once i changed count(*) to just * Thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now