Solved

Select data from all tables in a MS SQL database

Posted on 2010-09-02
9
828 Views
Last Modified: 2013-11-27
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
Comment
Question by:HDM
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 10

Expert Comment

by:himanshut
ID: 33592622
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
 

Author Comment

by:HDM
ID: 33592638
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
 
LVL 14

Expert Comment

by:DonConsolio
ID: 33592659
- "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
 
LVL 14

Expert Comment

by:DonConsolio
ID: 33592661
oops - forget my answer - that way mysql syntax
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 40

Expert Comment

by:Sharath
ID: 33592767
>> 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
 
LVL 39

Expert Comment

by:appari
ID: 33592825
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
 
LVL 42

Expert Comment

by:EugeneZ
ID: 33592833
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
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 33592841
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
 

Author Closing Comment

by:HDM
ID: 33593052
This worked  perfectly once i changed count(*) to just * Thanks for your help
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now