March 6, 2018 at 11:51 am
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'
March 6, 2018 at 12:00 pm
NewBornDBA2017 - Tuesday, March 6, 2018 11:51 AMI 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'
Change this:
'+s.name+'.'+t.name+'
To this:
['+s.name+'.'+t.name+']
Your alias is being interpreted as a column
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2018 at 12:02 pm
NewBornDBA2017 - Tuesday, March 6, 2018 11:51 AMI 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'
Have you tried this:
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'
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply