Find all string (character) columns in Azure SQL database

In Azure SQL character are columns 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.

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 
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],
         c.column_id;

Columns

  • 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

Rows

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

Sample results

0
There are no comments. Click here to write the first comment.