Find all string and binary columns in SQL Server database

In SQL Server string and binary columns are with the following data types: text, ntext, varchar, nvarchar, char, nchar, binary, varbinary, image.

The query below lists all columns with string and binary 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 ('text', 'ntext',
      'varchar', 'nvarchar', 'char', 'nchar',
      'binary', 'varbinary', 'image')
order by [table],


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


  • One row represents one column with a string or binary data type
  • Scope of rows: all columns containing string and binary data types in the database (schema)
  • Ordered by schema name, table name and position in table

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.