如何检查sql server对象的存在并删除它？

EDIT: the function creation was missing, sorry about that

编辑:缺少功能创建,抱歉

I have a T-SQL request that goes:

我有一个T-SQL请求:

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)

5 个解决方案

#1

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[IsSomething]') AND OBJECTPROPERTY(id, N'IsFunction') = 1) 

DROP function IsSomething
GO

#2

The answer to your edited question is:

您编辑的问题的答案是:

if object_id('IsSomething', 'fn') is not null drop function IsSomething
go

#3

Like this

IF OBJECT_ID('YourObject', 'ObjectsType') IS NOT NULL DROP <ObjectsType> [YourObject]

#4

The template, from Visual Studio 2008 Add -> Stored Procedure Script is

该模板来自Visual Studio 2008添加 - >存储过程脚本

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

对于过程,Sql Server Management Studio提供以下脚本

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.

我在大多数代码库中都经常看到后面的形式(2行版本),并且没有必要声明变量。

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

我不认为声明的变量是“数据库对象”,BTW。但是如果出于某种原因,你的问题更有意义。

#1

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (N'[dbo].[IsSomething]') AND OBJECTPROPERTY(id, N'IsFunction') = 1) 

DROP function IsSomething
GO

#2