I don't understand.

I want to check to see if the History table exists, and drop it if it
does. I want the script to check to see if it is a SQL 2000 server or
2005 and then run the appropriate "IF EXISTS" depending on if it is SQL
2000 or 2005. I don't understand the first SQL statement you sent and
the second one is the same as one of them I sent. Can you please
elaborate?

Thanks.

-----Original Message-----
From: Jaime E. Maccou
[mailto:mssqldba-ezmlmshield-x23484853.[Email address protected]
Sent: Wednesday, April 30, 2008 4:47 PM
To: LazyDBA Discussion
Subject: Re: Script to drop table against SQL 2000 and 2005


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




---------------------------------------------------------------------
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




****************************************************************
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.
****************************************************************

MS Sql Server LazyDBA home page