use the views instead of the table name

IF EXISTS (SELECT * FROM dbo.sysforeignkeys WHERE fkeyid = OBJECT_ID(N'[dbo].[FK_Order_Customer]') AND rkeyid = OBJECT_ID(N'[dbo].[Order]'))
ALTER TABLE [dbo].[yourtable] DROP CONSTRAINT [FK_yourfk]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[yourtable]') AND type in (N'U'))
DROP TABLE [dbo].[yourtable]



----- Original Message -----
From: "David.Ekren" <mssqldba-ezmlmshield-x82930388.[Email address protected]
To: "LazyDBA Discussion" <[Email address protected]
Sent: Wednesday, April 30, 2008 4:32:04 PM (GMT-0500) America/New_York
Subject: Script to drop table against SQL 2000 and 2005

I need one script to drop a table if it exists that works for SQL 2000
and 2005. When I run the script I will not know if the database I am
running it against is 2000 or 2005.

Normally I use this for SQL 2000
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE object_id =
OBJECT_ID(N'[dbo].[History]') AND type in (N'U'))

DROP TABLE [dbo].[History]

and this for SQL 2005

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[History]') AND type in (N'U'))

DROP TABLE [dbo].[History]

with the only difference being sys.objects and dbo.sysobjects.

But I want one script to drop the table if it exists whether it runs
against a SQL 2000 or 2005 database.

Thank you in advance,


David Ekren




****************************************************************
The information contained in this E-mail and any of its
attachments is intended only for the use of the address(es)
indicated above, and is confidential. This information may
also be legally privileged. If you are not the intended
recipient(s), you are hereby notified that any dissemination,
review or use of the information contained herein is
strictly prohibited. You may not copy, forward, disclose or use
any part of this information. If you have received this
information in error, please delete it and all copies from your
system and notify the sender immediately by return E-mail.

Thank you.

Internet communications are not always timely or
secure, and can contain errors and viruses. The sender does not
accept liability for any errors or omissions which arise as a
result.
****************************************************************


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page