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
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
