Check if DWH tables existing in a dynamic way

54 views Asked by At

In a SQL Server data warehouse, I have approximately 90 tables. During a nightly run, it can happen that a table is not created. I want to write SQL code that checks if every table exists, if possible in a dynamic way.

What would be the best way to do this? I assume that there are a lot of existing code samples to do this?

I am thinking about following steps:

In first step I have to create a fix table with all the table names right? I suppose if I use DWH_NAME.INFORMATION_SCHEMA.TABLES and select all relevant tables it can happen that the failed table is not included in DWH_NAME.INFORMATION_SCHEMA.TABLES?

In the second step, I need code that checks if the table names from the created fix table are included in DWH_NAME.INFORMATION_SCHEMA.TABLES.

Something like:

IF (EXISTS (SELECT *
            FROM DWH_NAME.INFORMATION_SCHEMA.TABLES
            WHERE TABLE_SCHEMA = 'dbo'
              AND TABLE_TYPE = 'BASE TABLE'
              AND TABLE_NAME = 'TABLE_NAME'))
BEGIN
    PRINT 'Database table exists'
END
ELSE
BEGIN
    PRINT 'No table in database'
END

In the third step, I have to create this code dynamically. TABLE_NAME should go through the 90 table names. An output that lists the 90 tables with the info if they exist or not would be nice.

Can someone confirm these steps or are there better methods? Maybe someone knows an appropriate dynamic code that was already written?

Many thanks.

I go through this article

If table exists with dynamic query in SQL Server

but was confused what @TABLENAME is and where it comes from.

0

There are 0 answers