Checking if a table exists in a db

[vs.vaidyanathan]

hi,
Is there any sql query to check if a table exists already in a database?
Thanks in advance.:)

[urtrivedi]

select count(*) from information_schema.tables where table_name='SEARCH_TABLE_NAME' AND TABLE_SCHEMA='SEARCH_DATABASE_NAME'

OR
If you want to create new table you may also use

create or replace syntax........

[tyson.crouch]

OR

...

CREATE TABLE IF NOT EXISTS tbl_name

[griswolf]

Be aware that other database engines provide this information in other ways. So far as I know, every engine gives you some way to do this programmatically; and so far as I know, every database engine has associated documentation about how to do it.

[vs.vaidyanathan]

OR

...

CREATE TABLE IF NOT EXISTS tbl_name

This doesn't seem to work... reports a syntax error near IF.hmmmm....:-/

[vs.vaidyanathan]

getting msg 156, level 15, state 2
syntax error near keyword 'if'
msg 102, level 15, state 1
syntax error near keyword 'exists'
So tried the workaround at microsoft which suggested i put the semicolon on the previous statements, that only threw up the same error twice more.The microsoft site says something about a compatiblity issue which i'm not sure i understand. Some help pl?

[vs.vaidyanathan]

Be aware that other database engines provide this information in other ways. So far as I know, every engine gives you some way to do this programmatically; and so far as I know, every database engine has associated documentation about how to do it.

CREATE TABLE abc
Msg 102, Level 15, State 1
Server 'XYZ', Line 1
Incorrect syntax near 'abc'.
DB-Library error:
General SQL Server error: Check messages from the SQL Server.
Tried with and without the ; on this line as well as the previous query. Nothing seems to work... frusty...

Following the link for some details:

http://www.electrictoolbox.com/check-if-mysql-table-exists/

[vs.vaidyanathan]

@raja thanks for the link. It was informative but the site talks about MySQL statements. My requirement is a server program.