Query to select tables with similar column values

Question

Continuing to my subject "http://stackoverflow.com/questions/3767000/invalid-column-name-though-its-there"

I wanna write a query that selects tables that has the values of the EntityId and DataclassId columns as identical values (skipping the nulls of course)

Here is an example of my DB

Table_1
ID  Sequence    Type    Heigh  Weight   EntityId    DataclassId
0   1           s       1.4     2.5     42-2c-Qi    42-2c-Qi
1   2           s       2.4     2.5     zh-km-xd    zh-km-xd
2   3           s       3.4     2.5     8n-tr-l7    8n-tr-l7

Table_2
ID  Data    Person    EntityId    DataclassId
0   1        Dave     58-zj-4o    41-2c-Q7
1   2        Sara     99-op-t6    oy-7j-mf
2   3        Silve    75-qy-47    2d-74-ds

Table_3
ID  Name    Genre   EntityId    DataclassId
0   LR      Ac      78-jd-o9    78-jd-o9
1   OI      Dr      4t-jb-qj    4t-jb-qj
2   DH      Do      7j-3e-ol    7j-3e-ol

Table_4
ID     Name    EntityId    DataclassId
NULL   NULL     NULL        NULL   
NULL   NULL     NULL        NULL   

Table_5
ID  Data    Person    EntityId    DataclassId
0   1        Dave     58-zj-4o    41-2c-Q7
1   2        Sara     99-op-t6    oy-7j-mf
2   3        Silve    fg-se-pl    2d-74-ds

The output should be

Table_1
Table_3

EDIT

I'm using also c# code to loop through the tables of the DB. But I don't get the correct results yet. What I need is a correct query for rootQ variable.

    public void getRootTables_checkSP()
    {
        string connect = "Data Source= EUADEVS06\\SS2008;Initial Catalog=TacOps_4_0_0_4_test;integrated security=SSPI; persist security info=False;Trusted_Connection=Yes";
        //Query to Select all Tables that have  
        //EntityId and DataclassId columns in them
        string query =
            "SELECT tabs.name " +
            "FROM sys.tables tabs INNER JOIN sys.columns cols " +
            "ON tabs.object_id = cols.object_id " +
            "AND cols.name IN ('EntityId', 'DataclassId')";

        int i = 0;
        int j = 0;  //Count for Root Tables
        SqlDataReader tables_list = null;
        SqlDataReader rootTables_list = null;
        SqlConnection conn = new SqlConnection(connect);
        SqlConnection conn2 = new SqlConnection(connect);
        try
        {
            //Write all table that have EntityId and
            //DataclassId cols into tables_list
            conn.Open();
            SqlCommand cmd_1 = new SqlCommand(query, conn);
            tables_list = cmd_1.ExecuteReader();

            while (tables_list.Read())
            {
                //Query to select Root Tables
                //EntityId and DataclassId values are identical in Root Tables
                //HERE IS WHERE I GOT STUCK AND COULDN'T FIGURE OUT THE CORRECT QUERY
                string rootQ =
                    "SELECT * " +
                    "FROM " + tables_list[0] + " " +
                    "WHERE EntityId not NULL " +
                    " AND DataclassId not NULL " +
                    " AND EntityId != DataclassId";
                try
                {
                    //Write Root Tables into rootTables_list
                    conn2.Open();
                    SqlCommand cmd_2 = new SqlCommand(rootQ, conn2);
                    rootTables_list = cmd_2.ExecuteReader();

                    //Loop through the rootTables_list and print out the values
                    while (rootTables_list.Read())
                    {
                        string s = rootTables_list[0].ToString();
                        Console.WriteLine(s);
                        j++;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex);
                }
                finally
                {
                    Console.WriteLine();
                    Console.WriteLine("{0} Root Tables-Count ", j);
                    Console.WriteLine();
                    conn2.Close();
                }
                i++;
            }

        }
        catch (Exception e)
        {
            Console.WriteLine("Error: " + e);
        }
        finally
        {
            Console.WriteLine();
            Console.WriteLine("{0} All Tables-Count ", i);
            Console.WriteLine();
            conn.Close();
        }

    }

Answer 1

It may be easiest to do this with a combination of SQL and code rather than a massive query.

If I were to do this, I'd create a query that finds and rows that would disqualify a table:

SELECT * FROM tablx
WHERE EntityId not NULL
 AND DataclassId not NULL
 AND EntityId != DataclassId;

If it returns any rows, that table is out.

Use this query on each table in turn. If any rows are returned, it's no good; discard it. If no rows are returned, save the name in a list.

When you're done, you have the list of all valid tables.

Answer 2

The entire logic using a cursor -- couldnt think of any other way to do it directly in the DB. For brevity sake, i am just printing the table names in the cursor. You could potentially do any processing there including save them in a table etc for future use

DECLARE @tableName nvarchar(500);
DECLARE @dynamicQuery nvarchar(600);

DECLARE candidateTables_cursor CURSOR FOR 
SELECT tabs.name  
FROM sys.tables tabs INNER JOIN sys.columns cols 
ON tabs.object_id = cols.object_id 
AND cols.name IN ('EntityId', 'DataClassId')
OPEN candidateTables_cursor;

FETCH NEXT FROM candidateTables_cursor INTO @tableName;

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @count int;
    DECLARE @ParmDefinition NVARCHAR(500);

    SET @dynamicQuery = N'SELECT @countOUT = COUNT(1) FROM ' + @tableName + ' WHERE EntityId = DataclassId'

    SET @ParmDefinition = N'@countOUT int OUTPUT'

    sp_executeSQL(@dynamicQuery , @ParmDefinition , @countOUT=@count OUTPUT)

    if @count > 0
       PRINT @tableName 

FETCH NEXT FROM candidateTables_cursor INTO @tableName;
END

CLOSE candidateTables_cursor;
DEALLOCATE candidateTables_cursor;

Answer 3

A UNION ALL would suffice for that.

SELECT TOP 1 'Table1' FROM Table1 WHERE EntityID = DataClassID
UNION ALL SELECT TOP 1 'Table2' FROM Table2 WHERE EntityID = DataClassID
UNION ALL SELECT TOP 1 'Table3' FROM Table3 WHERE EntityID = DataClassID
UNION ALL SELECT TOP 1 'Table4' FROM Table4 WHERE EntityID = DataClassID