Schema + Table name as a header

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

  • NewBornDBA2017 - Tuesday, March 6, 2018 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'

    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/

  • NewBornDBA2017 - Tuesday, March 6, 2018 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'

    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