×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Export Many SQL Tables to Excel

Export Many SQL Tables to Excel

Export Many SQL Tables to Excel

(OP)
I am using SQL Server 2012 and Excel 2016. I need to export all tables in a database that end with the word "type" to excel. I queried the database and got a list, and I was able t do it using the export tools, but that wasn't efficient. I then did it from excel data tab and selected the tables 20 at a time (there are almost 400 of them) but that takes a lot of time. Is there a more efficient way to do it. Here is the code for retrieving the table list.


USE MyDatabase
select schema_name(tab.schema_id) as schema_name,
tab.name as table_name,
col.column_id,
col.name as column_name,
t.name as data_type,
col.max_length,
col.precision
from sys.tables as tab
inner join sys.columns as col
on tab.object_id = col.object_id
left join sys.types as t
on col.user_type_id = t.user_type_id
where RIGHT(RTRIM(LTRIM(tab.name)),4) = 'Type'
order by schema_name,
table_name,
column_id;

Ps. I apologize for the duplicate post. I deleted the original due to a few errors in the posting.

Jim

RE: Export Many SQL Tables to Excel

Are you trying to cram all 400 of your tables into one worksheet?
Or you want to create one worksheet per table?


---- Andy

There is a great need for a sarcasm font.

RE: Export Many SQL Tables to Excel

(OP)
I am pulling 20 tables in a spreadsheet. I can do one at a time for a total of 399 files. The number of files per sheet isn't the issue. The issue is the manual process and is there a way to automate this?

Jim

RE: Export Many SQL Tables to Excel

I would use VBA in Excel to write a little Macro to get the list of the tables, and then loop thru this list to get the data for each table


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close