CODEWITHSUNDEEP
sql-server
Boomer
Boomer

Reputation: 1478

SQL column exists in table but not in INFORMATION_SCHEMA

I have a table created in sql. but in my application I am using the INFORMATION_SCHEMA to perform some tasks, I was surprised it wasn't synchronized with my table definition, that is

Code:

SELECT  SatComRatingTableID         -- This is working
FROM    dbo.tblSatComRatingTable


SELECT  f.field ,
        c.COLUMN_NAME
FROM    dbo.wiztbl_Fields f
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON c.COLUMN_NAME = f.Field
                                                  AND c.TABLE_NAME = 'tblSatComBillingPackage'
WHERE   f.DataObjectID = 2717
        AND COLUMN_NAME IS NULL

the second query returns: 

SatComRatingTableID NULL

What is happening and how to fix it? Thanks in advance

EDIT:

NB: the tblSatComBillingPackage contains a FK field SatComRatingTableID. I am using that field to get information from INFORMATION_SCHEMA...

Upvotes: 0

Views: 735

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

You're querying the wrong table, and seem to be filtering the results to preclude success:

SELECT  SatComRatingTableID
FROM    dbo.tblSatComRatingTable --<--This


SELECT  f.field ,
        c.COLUMN_NAME
FROM    dbo.wiztbl_Fields f
        LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON c.COLUMN_NAME = f.Field
             AND c.TABLE_NAME = 'tblSatComBillingPackage'  --<-- Does't match this
WHERE   f.DataObjectID = 2717
        AND COLUMN_NAME IS NULL --<-- And this seems wrong also

Upvotes: 2

Related Questions