Why do sys.columns and INFORMATION_SCHEMA.COLUMNS display different number of columns

Question

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?

Answer 1

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