Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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]
Add Comment
Please, Sign In to add comment