c# - Invalid Column Name though it's there! -


i'm trying print out tables db have entityid column equals dataclassid column here 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 * sys.tables entityid = dataclassid", conn);         roottables_list = s_cmd.executereader();          while (roottables_list.read())         {             string test = roottables_list[0].tostring();             console.writeline("root tables {0}", test);         }         roottables_list.close();         conn.close();     } 

but keeps saying these columns invalid though when printed out columns in db "syscolumns" there...

can tell me why i'm getting such error?

edit

what want query db tacops_4_0_0_4_test not system. realized that

edit 2

here example of tables in 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           7j-3e-ol    7j-3e-ol 

the output should be

table_1 table_3 

i think may understand you're trying based on comment frederik's answer

i tried "syscolumns" make sure columns exist. when query entityid = dataclassid says "invalid column name

it sounds entityid , dataclassid columns in table (or tables) have in database , want find rows tables contain same value in both columns??

if that's case, querying sys.tables incorrectly - you'd need query specific tables directly i.e.

select * table1 entityid = dataclassid 

can clarify?

edit: can find tables contain both columns using this:

select t.name sys.tables t exists(select * sys.columns c c.object_id = t.object_id , c.name='entityid')     , exists(select * sys.columns c c.object_id = t.object_id , c.name='dataclassid') 

from this, either iterate round each table , run query find rows match on entityid/dataclassid values - insert temp table , return 1 resultset @ end.

or, create view union tables , query view (would need update view each time added new table).

or, dynamic sql generation based on above generate select statement on-the-fly union tables together.

update: here's generic way in pure tsql - way means if new tables added automatically include them:

declare @sql varchar(max)  select @sql = coalesce(@sql + char(10) + 'union all' + char(10), '')      + 'select ''' + replace(quotename(t.name), '''', '''''') + ''' tablename, count(*) rowsmatched ' + quotename(t.name)      + ' entityid = dataclassid' sys.tables t exists(select * sys.columns c c.object_id = t.object_id , c.name='entityid')     , exists(select * sys.columns c c.object_id = t.object_id , c.name='dataclassid')  set @sql = 'select x.tablename, x.rowsmatched (' + @sql + ') x x.rowsmatched > 0 order x.tablename' execute(@sql) 

if don't need dynamic, change above execute print see sql generates, , create view it. can select view.

of course, either loop round each table 1 1 trying.


Comments

Popular posts from this blog

unicode - Are email addresses allowed to contain non-alphanumeric characters? -

c++ - Convert big endian to little endian when reading from a binary file -

C#: Application without a window or taskbar item (background app) that can still use Console.WriteLine() -