Export Many SQL Tables to Excel

[jimoo]

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

 

[Andrzejek]

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.

 

[jimoo]

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

 

[Andrzejek]

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.