Solved

Select data from all tables in a MS SQL database

Posted on 2010-09-02
9
840 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 15

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 15

Expert Comment

by:DonConsolio
ID: 33592661
oops - forget my answer - that way mysql syntax
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

912 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

15 Experts available now in Live!

Get 1:1 Help Now