Check to see if column exists in table

Hi All, I need to poll a database to see if a given column exists in a table or not. I came up with two queries, and I am curious to know if one is more efficient than the other: Method A) SELECT * FROM systables,syscolumns WHERE systables.id = syscolumns.id AND systables.name = 'giventable' AND syscolumns.name = 'givencolumn'; Method B) SELECT * FROM systables t INNER JOIN syscolumns c ON t.id=c.id WHEREt.name='giventable' AND c.name='givencolumn'; Also, if there is a better way to do this, please let me know. tia, rouble

On Nov 30, 2:56 pm, rouble <rou...@gmail.com> wrote: > Hi All, > > I need to poll a database to see if a given column exists in a table > or not. I came up with two queries, and I am curious to know if one is > more efficient than the other: > > Method A) > SELECT * FROM systables,syscolumns WHERE systables.id = syscolumns.id > AND systables.name = 'giventable' AND syscolumns.name = 'givencolumn';> > Method B) > SELECT * FROM systables t INNER JOIN syscolumns c ON t.id=c.id WHERE> t.name='giventable' AND c.name='givencolumn'; > > Also, if there is a better way to do this, please let me know. > > tia, > rouble systables.id should read systables.tabid and syscomlumns.id should read syscolumns.tabid c.name should be c.colname and t.name should be t.tabname I don't think it makes any difference. You can get the optimizer's opinion by setting explain plan on and looking at the sqexplain.out file in the users home directory.

What is going to be done after the column is found? Consider for instance... Rather than being proactive by looking for the column [presumed failure], maybe it could make sense to just attempt to use the column [presumed success] in the intended manner, and then react to the error for /column not in table/ if that occurs.? Regards, Chuck -- All comments provided "as is" with no warranties of any kind whatsoever and may not represent positions, strategies, nor views of my employer rouble wrote: > I need to poll a database to see if a given column exists in a table > or not. I came up with two queries, and I am curious to know if one is > more efficient than the other: > > Method A) > SELECT * FROM systables,syscolumns WHERE systables.id = syscolumns.id > AND systables.name = 'giventable' AND syscolumns.name = 'givencolumn';> > Method B) > SELECT * FROM systables t INNER JOIN syscolumns c ON t.id=c.id WHERE> t.name='giventable' AND c.name='givencolumn'; > > Also, if there is a better way to do this, please let me know.

On Nov 30, 2:56 pm, rouble <rou...@gmail.com> wrote: > Hi All, > > I need to poll a database to see if a given column exists in a table > or not. I came up with two queries, and I am curious to know if one is > more efficient than the other: I'll ignore that t.name and systables.name should be t.tabname/ systables.tabname, c/syscolumns.name should be .colname and the .id column references should all be 'tabid', that said: Because of the rules of satisfying ANSI 92 style queries the 'B' query will perform the WHERE clause filters on tabname and colname post- join. This will cause the query to first join all systables rows to all corresponding syscolumns rows placing the result into a temp table then to select from the temp table using the WHERE clause filters. Only ON clause filters are performed pre-join. You can make the B query equivalent to the former by: SELECT * FROM systable AS t INNER JOIN syscolumns AS c ON t.tabid = c.tabid AND t.tabname = 'giventable' AND c.colname ='givencolumn'; Both can be made more efficient by replacing 'SELECT *' with SELECT 1' since all you care about is whether a record was returned on the result of the query is SQLNOTFOUND. This will eliminate any need to actually read systables data pages and can operate purely off of the index nodes. Art S. Kagel > Method A) > SELECT * FROM systables,syscolumns WHERE systables.id = syscolumns.id > AND systables.name = 'giventable' AND syscolumns.name = 'givencolumn';> > Method B) > SELECT * FROM systables t INNER JOIN syscolumns c ON t.id=c.id WHERE> t.name='giventable' AND c.name='givencolumn'; > > Also, if there is a better way to do this, please let me know. > > tia, > rouble