Check if a table exists based on a column value in SQL Server-CodePudding

We have a data collection program that dynamically creates tables for data storage based on the identity value from another table. For example if 15 devices are created then the Devices table would have 15 entries (name, address, etc) and the DeviceID value would be say 134 - 149 then 15 tables would be created called Dev134 through Dev149.

Occasionally an issue occurred where some DEV tables were deleted but the record in the device table was not deleted leaving a orphan entry in the devices table. I.e. there is a DeviceID = 1245, but there is no table Dev1245.

What we would like to do is go through the Devices table and see if there is a corresponding Dev table in the database, and if not list the ID.

I have done this through a separate program, pulling the DeviceID's from the Device table into a list and then doing a

SELECT * 
FROM @DeviceID

(@DeviceID = "Dev" DeviceID)

and if I get something I know it's there and if I return nothing it's missing but I was hoping to do this with a single select statement that would return the ID of the missing tables.

CodePudding user response：

You can select table information from sys.tables: https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql?view=sql-server-ver16

This statement should give you all entries which misses the corresponding table:

SELECT [devices].*
FROM Devices AS [devices]
LEFT JOIN sys.tables AS [tables]
    ON [devices].[name] = [tables].[name]
WHERE [tables].[name] IS NULL

CodePudding user response：

SELECT 'Dev' CAST(deviceId AS VARCHAR(10))
FROM   devices
WHERE  NOT EXISTS (SELECT * FROM sys.tables WHERE name='Dev' CAST(deviceId AS VARCHAR(10)));

Here is DBFiddle demo