Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export Many SQL Tables to Excel

Status
Not open for further replies.

jimoo

Programmer
Jun 2, 2003
1,111
US
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
 
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.
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top