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
INFORMATION_SCHEMA.COLUMNS
is just a view oversys.columns
thatINNER JOIN
s tosys.objects
and restricts to object types of Table or View. My guess is that one of your records insys.columns
is eliminated in this restriction.