Solved

Select data from all tables in a MS SQL database

Posted on 2010-09-02
9
866 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 15

Expert Comment

by:DonConsolio
ID: 33592661
oops - forget my answer - that way mysql syntax
0
 
LVL 41

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:Eugene Z
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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Job - date manual 1 41
GeoClustering  and AOG 25 42
Disable TLS1.0 on Win 2012 server 7 57
Display SQL 2008 last modified/update Database 11 47
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

763 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