How to use sys.columns to get all columns of only user tables - in a database

Question

I have hundreds of user tables and each tables has lots of columns. Sys.Columns is returning 16000 columns; whereas INFORMATION_SCHEMA.COLUMNS is returning about 15,000.

Question: Why the above two functions are returning such different counts? Using Sys.Columns, how can we get the columns of ONLY all user tables in a Database?

SELECT COUNT(*) FROM sys.columns --16,000
SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS --15,004

Answer 1

If you do sp_helptext 'INFORMATION_SCHEMA.COLUMNS' you will see INFORMATION_SCHEMA.COLUMNS joins sys.columns to sys.objects and only includes columns where the corresponding object is of type 'U' or 'V'.

sys.colums includes additional types with the complete list documented as.

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

So the following should return the same count

SELECT COUNT(*) 
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
where o.type IN ('U', 'V')

Use o.type = 'U' if you only want user tables