11

I am trying two methods to display the columns with a particular name:

  1. INFORMATION_SCHEMA.COLUMNS

    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE COLUMN_NAME='SUPPLIER_NAME';
    
  2. SYS.COLUMNS

    SELECT *
    FROM SYS.COLUMNS
    WHERE NAME='SUPPLIER_NAME'
    

Why do the queries display different output?

SSMS screen grab

14

The difference between INFORMATION_SCHEMA.COLUMNS and sys.columns is the object types they cover. INFORMATION_SCHEMA.COLUMNS is restricted to tables and views. You can take a look at the code behind it by doing this:

EXEC sp_helptext [INFORMATION_SCHEMA.COLUMNS]

If you look at the bottom in the where clause you will see

o.type IN ('U', 'V')

This is where it is restricted to the tables and views only.

If you then go look at the definition of sys.columns you will see that it includes columns for a number of other objects:

  • Table-valued assembly functions (FT)
  • Inline table-valued SQL functions (IF)
  • Internal tables (IT)
  • System tables (S)
  • Table-valued SQL functions (TF)

Run this and you will be able to see what the extra object is and what type it is.

SELECT OBJECT_SCHEMA_NAME(object_id), OBJECT_NAME(object_id), 
    OBJECTPROPERTYEX(object_id, 'BASETYPE'), *
FROM sys.columns

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.