I don't think that there exists one generic way that works for all Databases, since this is something very specific that depends on how the DB is built.
But, why do you want to do this using a specific query ?
Can't you abstract the implementation away from what you want to do ?
I mean: why not create a generic interface, which has among others, a method called 'TableExists( string tablename )' for instance.
Then, for each DBMS that you want to support , you create a class which implements this interface, and in the TableExists method, you write specific logic for this DBMS.
The SQLServer implementation will then contain a query which queries sysobjects.
In your application, you can have a factory class which creates the correct implementation for a given context, and then you just call the TableExists method.
IMyInterface foo = MyFactory.CreateMyInterface (SupportedDbms.SqlServer);
if( foo.TableExists ("mytable") )
I think this is how I should do it.