CountRowsForWildcard.sql

-- Get row counts for tables by wildcard

-- 2 stages: 1) execute the metadata query, 2) plug those results into the query proper, and optionally
-- copy one of the [Select All] result columns to drill more into a found table

-- Stage 1
-- Into SSMS, paste this SQL, adjusting for database name and wildcards...

use AdventureWorks2016
go
select 'select ''['+s.name+'].['+t.name+']'' [TableName], count(1) [CountOfRows] ' +
',''select * from ['+s.name+'].['+t.name+']'' [Select All]' +
' from ['+s.name+'].['+t.name+'] union all '
 from sys.tables t join sys.Schemas s 
	on t.schema_id = s.schema_id  
 where (
 -- filter below
 t.name like '%person%' or
 t.name like '%phone%' or
 -- filter above
 1 = 0)

-- Stage 2
-- Then select the result from that, and open a new window
-- First past in this boilerplate:

;with result_tables as (
-- metadata below

-- metadata above
select '1','1','1'
)
select * from result_tables r
order by r.CountOfRows desc

select * from [Person].[Person]