How to check existence of a sql server object and drop it?
Solution 1
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[IsSomething]') AND OBJECTPROPERTY(id, N'IsFunction') = 1)
DROP function IsSomething
GO
Solution 2
The answer to your edited question is:
if object_id('IsSomething', 'fn') is not null drop function IsSomething
go
Solution 3
The template, from Visual Studio 2008 Add -> Stored Procedure Script
is
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Stored_Procedure_Name')
BEGIN
DROP Procedure Stored_Procedure_Name
END
GO
CREATE Procedure Stored_Procedure_Name
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
GO
/*
GRANT EXEC ON Stored_Procedure_Name TO PUBLIC
GO
*/
For a Procedure, Sql Server Management Studio gives the following script to drop
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_DeleteXyz]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_DeleteXyz]
likewise for a Function it's generated script is
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_GetXyz]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_GetXyz]
I've mostly seen the latter forms (2-line versions) in most codebases I've worked on, and there's no need to declare a variable.
Solution 4
For SQL Server 2016 and newer you can do the following:
DROP DATABASE IF EXISTS [DBName]
ALTER TABLE [TABLENAME] DROP COLUMN IF EXISTS [COLUMNNAME]
Solution 5
Seems analogous to any other language where you try to declare the same global variable multiple times. Normally we don't write:
var a;
a = 0;
...
...
undefine a;
a = 1;
...
...
undefine a;
a = 2;
Seems to me like you just need to be aware of the structure of the code you are writing.
I wouldn't consider a declared variable to be a "database object", BTW. But your question makes more sense if for some reason you do.
marcgg
Trying to build useful software. Find me on twitter or give my blog a read!
Updated on July 05, 2022Comments
-
marcgg almost 2 years
EDIT: the function creation was missing, sorry about that
I have a T-SQL request that goes:
DECLARE @IsSomething bit SET @IsSomething = 0 IF /some tests/ SET @IsSomething = 1 EXEC(' CREATE FUNCTION IsSomething () RETURNS bit AS BEGIN RETURN ' + @IsSomething + ' END')
Of course if I run it twice I get
There is already an object named 'IsSomething ' in the database.
How would I do something like this:
IF EXIST @IsSomething DESTROY @IsSomething // (Pseudo bad code)
-
dkretz almost 15 yearsA "define" statement has a specific limited scope. A defined variable shouldn't outlive the execution of a single script, nor interact with other scripts unless called as subscripts.
-
marcgg almost 15 yearsthe drop part is not working. If my variable is declared like this: DECLARE @IsSomething bit, how should I drop it?
-
marcgg almost 15 yearsOk I get it thanks to Ben M. Sorry my question was confusing :\ It's updated now with the real problem.
-
Chris McCall almost 15 yearsedited to reflect reality. Note that I didn't use the IF OBJECT_ID('YourObject', 'ObjectsType') IS NOT NULL style because it's too sloppy and would drop other object types you may not have intended.
-
Ben M almost 15 yearsHow's that, Chris? (In my answer, for example.)
-
João Paladini almost 15 yearsYes. Because A) "<ObjectsType>" has to be replaced with the objects type name, ie., "DROP TABLE [..]" or "DROP INDEX [..], and B) "bit" is not an Object Type, it is a domain type and you cannot drop them.
-
João Paladini almost 15 yearsAh, I see now, you've changed your question so that it is clear that you are trying to DROP a variable. You can't do that, you have to start another session/batch. (In scripts, GO will do this)
-
marcgg almost 15 yearsfrom Robert Paulson's answer I'd say that Chris is right about his answer being the best one, but I'm not sure.
-
Ben M almost 15 yearsEither one works--I'm not sure what Chris means about other object types being dropped, since you can't DROP FUNCTION a table, for example.
-
Chris McCall almost 15 yearsIn the effort to construct a reply, I've discovered I have no idea what the Hell I meant by that.
-
Chris McCall almost 15 yearsYeah, that looks fine, I think we're all slugging it out for that last 1% of correctedness
-
dkretz almost 15 yearsThe problem is that you IsSomething (the function) and @IsSomething (the variable) are two distinct things with no relationship to each other.