I am wondering if someone can help me out. I have tons of tables in my DBs and I am trying to find out the count where the column state is either NULL or has values like ESX which is in UK and not in United Sates and Canada.
select 'select count(*) from '+s.name+'.'+t.name+' where '+c.name+ ' not in (''AB'',''AL'',''AK'',''AZ'',''AR'',''BC'',''CA'',''CO'',''CT'',''DC'',''DE'',''FL'',''GA'',''HI'',''ID'',''IL'',''IN'',''IA'',''KS'',''KY'',''LA'',''MB'',''ME'',''MD'',''MA'',''MI'',''MN'',''MS'',''MO'',''MT'',''NE'',''NL'',''NT'',''NS'',''NV'',''NH'',
''NJ'',''NM'',''NY'',''NC'',''ND'',''OH'',''OK'',''ON'',''OR'',''PA'',''PE'',''QC'',''RI'',''SC'',''SD'',''SK'',''TN'',''TX'',''UT'',''VT'',''VA'',''WA'',''WV'',''WI'',''WY'',''GU'',''PR'',''VI'',''YT'')'
from sys.schemas s
INNER JOIN sys.tables t on s.schema_id=t.schema_id
INNER JOIN sys.columns c on t.object_id=c.object_id
where c.name = 'State'
and t.name like '%Contact'So when I run this above command, it generates a query for me but when I run it, I get "No column name" but what I want is the schema+table as a header so I know the total count from each table but I can't seem to figure that out. This is what I did but of course it didn't work.
select 'select count(*) as '+s.name+'.'+t.name+' from '+s.name+'.'+t.name+' where '+c.name+ ' not in (''AB'',''AL'',''AK'',''AZ'',''AR'',''BC'',''CA'',''CO'',''CT'',''DC'',''DE'',''FL'',''GA'',''HI'',''ID'',''IL'',''IN'',''IA'',''KS'',''KY'',''LA'',''MB'',''ME'',''MD'',''MA'',''MI'',''MN'',''MS'',''MO'',''MT'',''NE'',''NL'',''NT'',''NS'',''NV'',''NH'',
''NJ'',''NM'',''NY'',''NC'',''ND'',''OH'',''OK'',''ON'',''OR'',''PA'',''PE'',''QC'',''RI'',''SC'',''SD'',''SK'',''TN'',''TX'',''UT'',''VT'',''VA'',''WA'',''WV'',''WI'',''WY'',''GU'',''PR'',''VI'',''YT'')'
from sys.schemas s
INNER JOIN sys.tables t on s.schema_id=t.schema_id
INNER JOIN sys.columns c on t.object_id=c.object_id
where c.name = 'State'
and t.name like '%Contact'