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.

Query

select schema_name(t.schema_id) + '.' + t.name as [table],
       c.column_id,
       c.name as column_name,
       type_name(user_type_id) as data_type,
       max_length,
       precision,
       scale 
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],
         c.column_id;

Columns

  • 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

Rows

  • 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.
0
There are no comments. Click here to write the first comment.