sql-serverleft-joininformation-schema

Join INFORMATION_SCHEMA.TABLES with another table with more TABLE_NAMES and result would be different if table name is in the first table or not


we have a Table with a list of table names we want to be created. They don't have an ID column or anything, it's just a few rows of data with 2 columns. Thing is we want to merge that table with Information_schema.table to check which of the tables we have already created and which we have not, so we wrote the query below as a temp to achieve such:

with cte1 as (
select d.TABNAME, d.CLASS from dbo.table_list as d
left join INFORMATION_SCHEMA.TABLES as t on t.TABLE_NAME = d.TABNAME
where d.CLASS in ('INIT','STERN') and table_schema = 'dbo'),

cte2 as (select d.TABNAME, d.CLASS
from dbo.table_list as d
where d.CLASS in ('INIT','TERN') and d.TABNAME not in (select [TABLE NAME] from cte1))


select *, 'Active' as [Status] from cte1 union all
select * , 'Inactive' from cte2 

This is what table_list looks like:

TABNAME CLASS
TABLE1 INIT
TABLE2 STERN
TABLE3 STERN
TABLE4 STERN
TABLE5 INIT

We already have TABLE1 and TABLE2 created so the result of the query looks like this:

TABNAME CLASS STATUS
TABLE1 INIT Active
TABLE2 STERN Active
TABLE3 STERN Inactive
TABLE4 STERN Inactive
TABLE5 INIT Inactive

It works well enough like this but we were wondering if we could make it shorter.


Solution

  • This can be way shorter, yes. You could just reference the table dbo.table_list and see if you get a valid OBJECT_ID:

    SELECT tl.TABNAME,
           tl.CLASS,
           CASE WHEN OBJECT_ID(N'dbo.' + QUOTENAME(tl.TABNAME)) IS NULL THEN 'Inactive' ELSE 'Active' END AS Status
    FROM dbo.table_list tl --"d" for "table_list" doesn't make a lot of sense.
    WHERE tl.CLASS IN ('INIT','STERN');
    

    If you wanted to use the catalog views, you could use CROSS APPLY to join to the table while supplying a value for both the schema and table name, or just JOIN to sys.schemas based on a literal and then LEFT JOIN to sys.tables:

    SELECT tl.TABNAME,
           tl.CLASS,
           CASE WHEN st.[name] IS NULL THEN 'Inactive' ELSE 'Active' END AS Status
    FROM dbo.table_list tl --"d" for "table_list" doesn't make a lot of sense.
         CROSS APPLY (SELECT t.[name]
                      FROM sys.schemas s
                           JOIN sys.tables t ON s.schema_id = t.schema_id
                      WHERE s.[name] = N'dbo'
                        AND t.[name] = tl.TABNAME) st
    WHERE tl.CLASS IN ('INIT','STERN');
    
    SELECT tl.TABNAME,
           tl.CLASS,
           CASE WHEN t.[name] IS NULL THEN 'Inactive' ELSE 'Active' END AS Status
    FROM dbo.table_list tl --"d" for "table_list" doesn't make a lot of sense.
         JOIN sys.schemas s ON s.[name] = N'dbo'
         LEFT JOIN sys.tables t ON s.schema_id = t.schema_id
                               AND tl.TABNAME = t.[name]
    WHERE tl.CLASS IN ('INIT','STERN');