Find tables that DON'T have a column with a specific name in Azure SQL Database

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

Table of Contents:


    Databases often have standard columns. Examples of such standard columns can be id, modified_date, created_by or row_version.

    The query below finds all the tables that do not have the 'ModifiedDate' column.

    Query

    select schema_name(t.schema_id) as schema_name,
        t.name as table_name
    from sys.tables as t
    where t.object_id not in
        (select c.object_id
          from sys.columns as c
         where c.name = 'ModifiedDate')
    order by schema_name,
        table_name;
    

    Columns

    • schema_name - schema name of the table found
    • table_name - name of the table found

    Rows

    • One row: represents a table
    • Scope of rows: all found tables
    • Ordered by schema name, table name

    Sample results

    These tables do not have the 'ModifiedDate' column.

    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