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
Post a Comment