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.