October 30, 2002 at 9:58 am
I am querying tables in some reports that may or may not exist at the time the report is run. I want to be able to generate a nice error message when the table doesnt exist.
I know that we can check the sysobjects table but this is not good as the schema can change with future release. Also there is the information_schema tables, but I was under the assumption that this is only supported by 2000.
So my question is how can I check for the existance of tables that is supported by 7, 2000 and any future release. Is there a stored procedure? I have looked at sp_tables but not sure how I could use this to accomplish want I need to do.
Thanks
October 30, 2002 at 11:16 am
The INFORMATION_SCHEMA.TABLES view is available in SQL 7.0. I'd say the normal answer is that INFORMATION_SCHEMA views should be used, since MS "might" support these to in future releases, where as system tables might change.
Here is what I would use for finding out if a tables exists:
if exists (select * from information_schema.tables where table_name = '<your table>')
print 'table exists'
else
print 'table does not exist'
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 30, 2002 at 2:33 pm
I agree with Greg. The INFORMATION_SCHEMA views exist in the ANSI standard, which is part of the reason that Microsoft included them. Consequently, as Microsoft continues and improves their support for the ANSI standard, the INFORMATION_SCHEMA views should continue to exist.
Matthew Burr
October 30, 2002 at 2:39 pm
Thanks for the input. If you read about the information_schema views in msdn, it makes it sound as if it wasnt supported until 2000 and I did not have a 7.0 db to test on.
October 30, 2002 at 4:36 pm
What about checking for the existance of a stored procedure?
October 30, 2002 at 5:39 pm
You should be able to use information_schema.routines
Note this is not available in SQL 7.0.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
October 31, 2002 at 4:38 am
Which another all-purpose way to check for existance of an object in a database is to use
object_id('objectnamehere')
which will return a number if exists or NULL if does not, works for tables, SPs, View and other items.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply