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

1 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

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

  • @MatinSmith Good explanation (+1).
    – nam
    Jul 5 at 22:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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