Invalid Column Name though it's there!!

Question

Hello

I'm trying to print out Tables from the DB that have the EntityId column equals to DataclassId column here is the code

    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";
        SqlDataReader rootTables_List = null;
        SqlConnection conn = new SqlConnection(connect);
        conn.Open();

        SqlCommand s_cmd = new SqlCommand("SELECT * FROM sys.Tables WHERE EntityId = DataclassId", conn);
        rootTables_List = s_cmd.ExecuteReader();

        while (rootTables_List.Read())
        {
            string test = rootTables_List[0].ToString();
            Console.WriteLine("ROOT TABLES ARE {0}", test);
        }
        rootTables_List.Close();
        conn.Close();
    }

but it keeps saying that these columns are invalid though when I printed out all the columns in the DB "syscolumns" they were there...

Can anyone tell me why I'm getting such an error?

EDIT

What I really want is to query the db TacOps_4_0_0_4_test not the system. I just realized that

EDIT 2

Here is an example of the Tables in 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

The output should be

Table_1
Table_3

Answer 1

EntityId and DataclassId are indeed no columns that exists in the sys.tables.

You're selecting data from sys.tables, there's no notion of syscolumns in your query, so i do not know why you're mentionning 'syscolumns' in your explanation ?

Answer 2

Well, if you do a sp_help 'sys.Tables' in SQL Management Studio you'll see that, indeed, those columns are not part of sys.Tables...

Answer 3

I think I may understand what you're trying now based on your comment to Frederik's answer

I tried "syscolumns" just to make sure that the columns do exist. But when I do the query where EntityId = DataclassId it says "Invalid column name

It sounds like EntityId and Dataclassid are columns in a table (or tables) that you have in your database and you want to find the rows from those tables that contain the same value in both those columns??

If that's the case, you are querying sys.Tables incorrectly - you'd need to query the specific tables directly i.e.

SELECT * FROM Table1 WHERE EntityId = DataClassId

Can you clarify?

Edit: You can find all the tables that contain both those columns using this:

SELECT t.name
FROM sys.tables t
WHERE EXISTS(SELECT * FROM sys.columns c WHERE c.object_id = t.object_id AND c.name='EntityId')
    AND EXISTS(SELECT * FROM sys.columns c WHERE c.object_id = t.object_id AND c.name='DataClassId')

From this, you could either iterate round each table and run the query to find rows that match on EntityId/DataClassId values - could insert into a temp table and return 1 resultset at the end.

OR, you could create a view to UNION all the tables together and then query that view (would need to update the view each time you added a new table).

OR, you could do some dynamic SQL generation based on the above to generate a SELECT statement on-the-fly to UNION all the tables together.

Update: Here's a generic way to do it in pure TSQL - this way means if new tables are added it will automatically include them:

DECLARE @SQL VARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL + CHAR(10) + 'UNION ALL' + CHAR(10), '') 
    + 'SELECT ''' + REPLACE(QUOTENAME(t.Name), '''', '''''') + ''' AS TableName, COUNT(*) AS RowsMatched FROM ' + QUOTENAME(t.name) 
    + ' WHERE EntityId = DataClassId'
FROM sys.tables t
WHERE EXISTS(SELECT * FROM sys.columns c WHERE c.object_id = t.object_id AND c.name='EntityId')
    AND EXISTS(SELECT * FROM sys.columns c WHERE c.object_id = t.object_id AND c.name='DataClassId')

SET @SQL = 'SELECT x.TableName, x.RowsMatched FROM (' + @SQL + ') x WHERE x.RowsMatched > 0 ORDER BY x.TableName'
EXECUTE(@SQL)

If you don't need it to be dynamic, change the above EXECUTE to a PRINT to see the SQL it generates, and then create a view from it. You can then SELECT from that view.

Of course, you could either loop round each table 1 by 1 as you are trying.

Answer 4

Based on all the comments, i think what you might be trying to find is ALL tables in your database that have both EntityID and DataClassID columns.

I know...its a pretty WILD guess but dont blame me for trying!! :-)

If my shot in the pretty awesome darkness that is your question is correct, try this out:

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')

Answer 5

So here is what I came up with after the helpful answers... but I still don't get the correct output. I'm pretty sure there is some major drastic logic error in my code so I hope you could help me out.

I commented the code so it will be easier to understand

    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;  //Count for all Tables with EntityId and DataclassId
        int j = 0;  //Count for Root Tables (share same EntityId and DataclassId value)
        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();
            //Loop through the tables_list
            while (tables_list.Read())
            {
                //Query to select Root Tables.
                //EntityId and DataclassId values are identical in Root Tables
                string rootQ =
                    "SELECT count(*) " +
                    "FROM " + tables_list[0] + " " +
                    "WHERE 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())
                    {
                        Console.WriteLine(rootTables_list[0]);
                        j++;
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("Error: " + ex);
                }
                finally
                {
                    Console.WriteLine();
                    Console.WriteLine("{0} j-Count ", j);
                    Console.WriteLine();
                    conn2.Close();
                }
                i++;
            }
        }
        catch (Exception e)
        {
            Console.WriteLine("Error: " + e);
        }
        finally
        {
            Console.WriteLine();
            Console.WriteLine("{0} i-Count ", i);
            Console.WriteLine();
            conn.Close();
        }