CODEWITHSUNDEEP
sqlsql-server
fifthace
fifthace

Reputation: 546

Select SQL table names with no dependencies

I have seen scripts that can show the dependencies of an SQL table, but no script that can select the table names of tables with no dependencies.

Ideally, I would like to select all table names starting with Q (as seen below) and no dependencies in MSSQL, i.e.

SELECT t.NAME AS TableName
FROM sys.Tables t
where t.Name LIKE 'Q%' and no dependencies!

Upvotes: 0

Views: 1966

Answers (2)

Jamie Pollard
Jamie Pollard

Reputation: 1599

You should left join on sys.sql_expression_dependencies and grab the tables that aren't being referenced, something like:

SELECT t.NAME AS TableName
     FROM sys.Tables t
LEFT JOIN sys.sql_expression_dependencies d ON d.referenced_id = t.object_id
    WHERE t.Name LIKE 'Q%'
      AND d.referenced_id IS NULL

Upvotes: 2

Peter B
Peter B

Reputation: 24202

How about this:

select * from INFORMATION_SCHEMA.TABLES T
where T.TABLE_NAME like 'Q%'
and not exists (
    select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
    where TC.TABLE_NAME      = T.TABLE_NAME
    and   TC.CONSTRAINT_TYPE = 'FOREIGN KEY'   -- add conditions as needed
)
order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME

Upvotes: 1

Related Questions