Solved

Select data from all tables in a MS SQL database

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

Expert Comment

by:DonConsolio
ID: 33592661
oops - forget my answer - that way mysql syntax
0
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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…

789 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