Finding Out Whether a Table Exists

In Microsoft Access, it is possible to retrieve a list of all the user created tables that are in a database by querying the system table MSysObjects:

SELECT Name FROM MSysObjects
WHERE Type=1 AND Flags=0

This query won't work in MS SQL server, because the system table which stores this information has a
different name. The same information can be retrieved from an MS SQL Server database with

SELECT name FROM SysObjects
WHERE type = 'U'

It is also possible to retrieve this information using the Microsoft ADO Extensions for DDL and Security library (ADOX). For the moment however, I am only concerned with SQL based solutions to this type of problem.

Although I have mentioned querying the SysObjects table as a method for finding out which tables are in a database, it is much more preferable to use the SQL-92 standard views.

In any SQL-92 compliant database, the following will return the names of all of the non-system tables in a database:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'

The INFORMATION_SCHEMA views are supported in MS SQL Server 7 and later, (but not MS Access 2000).

The INFORMATION_SCHEMA views are based on system tables but are a lot easier to understand, because
they are intended to be read.

Adelle.