if exists DROP PROCEDURE/VIEW

how can I drop a propcedure or a view without error for MS SQL 2000/2500

for a table

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

and for a procedure

IF EXISTS ?
DROP PROCEDURE [dbo].[SP_myTABLE_Count]

thank you for helpingPlease excuse me, but I find the number of people who can't use google abolutely unbelievable. A search for sys.objects gives the answer, and the type is P for TSQL stored procedures.

However, keep in mind that SQL Server 2005 hides objects on which you have no access, so even if you query sys.objects first you may get an error, and you should handle that situation.|||please excuse me, but a forum is not google|||please excuse me, but a forum is not google
I am perfectly aware of that. What I tried to point out is that very simple questions, like this one, can very easily be solved by searching google. And it's even way faster. Even better, by eliminating a few of the simpler questions, the users of the forum can use their time trying to resolve a bit tougher issues, for which there is NOT an obvious solution when searching google. I do think you get my point.

By all means, do not think of my post as a personal critism, rather a good guideline for the future. My approach is alway as this: Try to solve, search with google, post on forum.|||actually u need not think that hard to get the "if exists" script. sql server can do it for u. right click on any SP from EM and select "generate SQL Script" or from QA Object-Browser select "create drop script" by right clicking on a SP. check the code generated.....

if exists condition..

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

regardsfireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

Whichever you fancy. I would probably write the second nine times out
of ten, but that it would only be because db_id() would not come in
my mind. And a nice characteristic of the first, is that it works on
SQL 2000 as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I would probably write the second
[...]

thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I would probably write the second

[...]
>
thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?

Yes, both are perfectly OK, and which you pick is a matter of taste or
the flux of the moment.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The first one; its more portable between versions of SQL Server, the second
only works in SQL 2005 and upwards.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"fireball" <fireball@.onet.kropka.euwrote in message
news:embrdl$n8d$1@.atlantis.news.tpi.pl...

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?
>
>
>
>
regards
>
>