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

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.

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.