Join INFORMATION_SCHEMA.TABLES with another table with more TABLE_NAMES and result would be differen-CodePudding

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.

CodePudding user response：

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');