Find large object (LOB) data type columns in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    Large objects in Azure SQL Database are columns with the following data types: varchar(max), nvarchar(max), text, ntext, image, varbinary(max), and xml.

    The query below lists all columns with LOB data types.

    Query

    select t.table_schema as schema_name,
        t.table_name,
        c.column_name,
        c.data_type
    from information_schema.columns as c
        inner join information_schema.tables as t
            on c.table_schema = t.table_schema
            and c.table_name = t.table_name
    where t.table_type = 'BASE TABLE' 
    and ((c.data_type in ('VARCHAR', 'NVARCHAR') and character_maximum_length = -1)
    or c.data_type in ('TEXT', 'NTEXT', 'IMAGE', 'VARBINARY', 'XML', 'FILESTREAM'))
    order by t.table_schema, 
        t.table_name,
        c.column_name
    

    Columns

    • schema_name - name of the schema
    • table_name - name of the table
    • column_name - name of the column
    • data_type - type of data

    Rows

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

    Sample results

    List of LOB columns in the AdventureWorksLT database:

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept