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

Question

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.

This sounds like an XY problem to me. Why do you need tables dynamically named like this? That is a strong indication that the design is less than ideal. Seems like a single table to hold that data would be appropriate with one extra column to hold the identity value. — Sean Lange
– Sean Lange, Commented Jan 23, 2023 at 20:03
We are data logging devices and each device could have anywhere from 1 to 150 points of data being logged every 15 minutes. There could be 1000+ devices per site and 50+ sites per server the software is running on. Devices are rarely identical so you might have 30 with 45 points of data, 120 with 20 points of data. A single table wouldn't work at all. — ADY
– ADY, Commented Jan 23, 2023 at 20:50
Not sure how those numbers mean you have to resort to a dynamic architecture. It still seems to me that there is a more standard approach to whatever it is you are trying to do that wouldn't require so much dynamic logic. — Sean Lange
– Sean Lange, Commented Jan 23, 2023 at 20:53
How would you take a infinite number of different devices with a different number of values of different data types and store them all into the same table??? It only makes sense logically to have a table per device. If there were a set number of device types with the same points sure but there isn't which is why the tables, and columns themselves, are all dynamically created. — ADY
– ADY, Commented Jan 23, 2023 at 21:00
Either add each value as another row or use JSON, or maybe even XML. — Sean Lange
– Sean Lange, Commented Jan 23, 2023 at 21:03

Sebastian S. · Accepted Answer · 2023-01-23 20:03:50Z

2

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

answered Jan 23, 2023 at 20:03

Sebastian S.

1,4013 gold badges17 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Cetin Basoz · Accepted Answer · 2023-01-23 20:07:02Z

1

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

answered Jan 23, 2023 at 20:07

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

1 Comment

ADY Over a year ago

Thank-you. This worked perfectly and found a single device (out of 18k) in the tDevices table that was orphaned.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related