Find all numeric columns in SQL Server database

Numeric in SQL Server are columns with the following data types: tinyint, smallint, int, bigint, decimal, numeric, bit, float, real, smallmoney and money. The query below lists all columns with numeric data types.


select schema_name(t.schema_id) + '.' + as [table],
       c.column_id, as column_name,
       type_name(user_type_id) as data_type,
from sys.columns c
join sys.tables t
     on t.object_id = c.object_id
where type_name(user_type_id) in ('bigint', 'int', 
      'smallint', 'tinyint', 'decimal', 'numeric',
      'smallmoney', 'money', 'bit', 'float', 'real')
order by [table],


  • table - name of the schema and name of the table
  • column_id - column position in table
  • column_name - name of the column
  • data_type - type of data
  • max_length - maximum length (in bytes)
  • precision - precision of the column
  • scale - scale of column


  • One row represents one column with a numeric data type
  • Scope of rows: all columns containing numeric data types in the database
  • Ordered by schema name and table name

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.