How to get the name of the table that has the desired data?-CodePudding

I have three tables that contain certain data, I need to use a SQL query to get the name of the table that contains the data used in the query. For example, I have three tables, conditionally table A, B and C. These tables are completely identical, but they always contain different data, in the query I specify the value of one of the columns and I need to get the name of the table.

How can this be done using a query?

CodePudding user response：

You could query all three tables in a UNION and include the name of the table in the results. (Which would also cover the case when the data exists in more than one table.)

For example, if we create these tables:

CREATE TABLE [A] ([Data] NVARCHAR(10) NOT NULL);
CREATE TABLE [B] ([Data] NVARCHAR(10) NOT NULL);
CREATE TABLE [C] ([Data] NVARCHAR(10) NOT NULL);

INSERT INTO [B] ([Data]) VALUES ('test');

Then we can select from them like this:

SELECT [Data], 'A' AS [Table] FROM [A] WHERE [Data] = 'test'
UNION ALL
SELECT [Data], 'B' AS [Table] FROM [B] WHERE [Data] = 'test'
UNION ALL
SELECT [Data], 'C' AS [Table] FROM [C] WHERE [Data] = 'test'

Another approach could be to use a combination of IIF and EXISTS, for example:

SELECT
  IIF(EXISTS (SELECT * FROM [A] WHERE [Data] = 'test'), 'A',
    IIF (EXISTS (SELECT * FROM [B] WHERE [Data] = 'test'), 'B',
      IIF (EXISTS (SELECT * FROM [C] WHERE [Data] = 'test'), 'C',
      '')
    )
  )

Which would only show you the first table where the value was found, but won't show if it exists in more than one table.