validar - verificar si existe una tabla en sql server

Cómo comprobar si existe un procedimiento almacenado antes de crearlo

Question

Tengo un script SQL que se debe ejecutar cada vez que un cliente ejecuta la funcionalidad de "administración de base de datos". El script incluye la creación de procedimientos almacenados en la base de datos del cliente. Es posible que algunos de estos clientes ya tengan el procedimiento almacenado al ejecutar el script y otros no. Necesito que los procedimientos almacenados faltantes se agreguen a la base de datos del cliente, pero no importa cuánto intente doblar la sintaxis de T-SQL, obtengo

CREAR / ALTERAR PROCEDIMIENTO ''debe ser la primera declaración en un lote de consultas

He leído esa caída antes de crear trabajos, pero no me gusta hacerlo de esa manera.

IF EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND name = ''MyProc'')
DROP PROCEDURE MyProc
GO

CREATE PROCEDURE MyProc
...

¿Cómo puedo agregar la verificación de la existencia de un procedimiento almacenado y crearlo si no existe, pero modificarlo si existe?

Answer 1

¡Me pregunto! ¿Por qué no escribo toda la consulta como

GO
create procedure [dbo].[spAddNewClass] @ClassName varchar(20),@ClassFee int
as
begin
insert into tblClass values (@ClassName,@ClassFee)
end

GO
create procedure [dbo].[spAddNewSection] @SectionName varchar(20),@ClassID       int
as
begin
insert into tblSection values(@SectionName,@ClassID)
end

Go
create procedure test
as
begin 
select * from tblstudent
end

Ya sé que los dos primeros procedimientos ya existen. SQLS ejecutará la consulta. Dará el error de los dos primeros procedimientos, pero aún así creará el último procedimiento. SQl está cuidando de lo que ya existe. Esto es lo que siempre hago para todos. ¡clientela!

Answer 2

¿por qué no vas de la manera simple como

    IF EXISTS(SELECT * FROM sys.procedures WHERE NAME LIKE ''uspBlackListGetAll'')
    BEGIN
         DROP PROCEDURE uspBlackListGetAll
    END
    GO

    CREATE Procedure uspBlackListGetAll

..........

Answer 3

** La forma más sencilla de eliminar y recrear un proc almacenado en T-Sql es **

Use DatabaseName
go
If Object_Id(''schema.storedprocname'') is not null
begin
   drop procedure schema.storedprocname
end
go

create procedure schema.storedprocname
as

begin
end

Answer 4

A partir de SQL SERVER 2016 puede utilizar el nuevo DROP PROCEDURE IF EXISTS .
DROP { PROC | PROCEDURE } [ IF EXISTS ] { [ schema_name. ] procedure } [ ,...n ]

Referencia: https://msdn.microsoft.com/en-us/library/ms174969.aspx

Answer 5

Además de la respuesta de @Geoff , he creado una herramienta simple que genera un archivo SQL que @Geoff declaraciones de procedimientos almacenados, vistas, funciones y activadores.

Ver MyDbUtils @ CodePlex .

Answer 6

Aparentemente no tengo la reputación requerida para votar o comentar, pero solo quería decir que la respuesta de Geoff usando EXEC (sp_executesql podría ser mejor) es definitivamente el camino a seguir. Dejar caer y luego volver a crear el procedimiento almacenado hace el trabajo al final, pero hay un momento en el que el procedimiento almacenado no existe en absoluto, y eso puede ser muy malo, especialmente si esto es algo que será corre repetidamente Estaba teniendo todo tipo de problemas con mi aplicación porque un subproceso en segundo plano estaba haciendo una GOTA DE EXISTA IF ... CREAR al mismo tiempo que otro subproceso intentaba usar el procedimiento almacenado.

Answer 7

Aquí está el script que uso. Con él, evito innecesariamente soltar y recrear los procesos almacenados.

IF NOT EXISTS (
    SELECT *
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N''[dbo].[uspMyProcedure]'')
    )
BEGIN
  EXEC sp_executesql N''CREATE PROCEDURE [dbo].[uspMyProcedure] AS select 1''
END
GO

ALTER PROCEDURE [dbo].[uspMyProcedure] 
    @variable1 INTEGER  
AS
BEGIN
   -- Stored procedure logic
END

Answer 8

Aquí hay un método y algún razonamiento detrás de usarlo de esta manera. No es tan bonito editar el proceso almacenado, pero hay pros y contras ...

ACTUALIZACIÓN: También puede envolver toda esta llamada en una TRANSACCIÓN. Incluyendo muchos procedimientos almacenados en una sola transacción que puede cometer o revertir. Otra ventaja de envolver en una transacción es que el procedimiento almacenado siempre existe para otras conexiones SQL, siempre y cuando no utilicen el nivel de aislamiento de transacción READ UNCOMMITTED.

1) Evitar altera solo como una decisión de proceso. Nuestros procesos son siempre SI EXISTE DROP LUEGO CREAR. Si sigue el mismo patrón de asumir que el nuevo PROC es el proceso deseado, es mucho más difícil dar con los cambios porque tendría un IF SI EXISTS ALTER ELSE CREATE.

2) Tiene que colocar CREATE / ALTER como la primera llamada en un lote para que no pueda ajustar una secuencia de actualizaciones de procedimientos en una transacción fuera de SQL dinámico. Básicamente, si desea ejecutar una pila completa de actualizaciones de procedimientos o revertirlas todas sin restaurar una copia de seguridad de base de datos, esta es una forma de hacer todo en un solo lote.

IF NOT EXISTS (select ss.name as SchemaName, sp.name as StoredProc 
    from sys.procedures sp
    join sys.schemas ss on sp.schema_id = ss.schema_id
    where ss.name = ''dbo'' and sp.name = ''MyStoredProc'')
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    -- Not so aesthetically pleasing part. The actual proc definition is stored
    -- in our variable and then executed.
    SELECT @sql = ''CREATE PROCEDURE [dbo].[MyStoredProc]
(
@MyParam int
)
AS
SELECT @MyParam''
    EXEC sp_executesql @sql
END

Answer 9

CREAR Procedimiento SI NO EXISTE ''Su nombre de proc'' () COMIENZO ... FINALIZAR

Answer 10

Compruebe si existe un procedimiento almacenado

IF EXISTS (SELECT * FROM sys.objects 
            WHERE object_id = OBJECT_ID
             (N''[Schema].[Procedure_Name]'') AND type IN (N''P'', N''PC''))
BEGIN
       DROP PROCEDURE [Schema].[Procedure_Name]
       Print(''Proceudre dropped => [Schema].[Procedure_Name]'')
END

Verifique si existe un Trigger, también haga clic en el enlace siguiente http://www.gurujipoint.com/2017/05/check-if-exist-for-trigger-function-and.html

Answer 11

DROP IF EXISTS es una nueva característica de SQL Server 2016

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/

DROP  PROCEDURE IF EXISTS dbo.[procname]

Answer 12

En el servidor Sql 2008 en adelante, puede usar " INFORMATION_SCHEMA.ROUTINES "

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES 
  WHERE ROUTINE_NAME = ''MySP''
        AND ROUTINE_TYPE = ''PROCEDURE'') 

Answer 13

Me doy cuenta de que esto ya se ha marcado como contestado, pero solíamos hacerlo así:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE type = ''P'' AND OBJECT_ID = OBJECT_ID(''dbo.MyProc''))
   exec(''CREATE PROCEDURE [dbo].[MyProc] AS BEGIN SET NOCOUNT ON; END'')
GO

ALTER PROCEDURE [dbo].[MyProc] 
AS
  ....

Solo para evitar dejar caer el procedimiento.

Answer 14

Puede ejecutar código de procedimiento en cualquier lugar donde pueda ejecutar una consulta.

Solo copia todo después de AS :

BEGIN
    DECLARE @myvar INT
    SELECT  *
    FROM    mytable
    WHERE   @myvar ...
END

Este código hace exactamente lo mismo que haría un proceso almacenado, pero no se almacena en el lado de la base de datos.

Eso es muy parecido a lo que se llama procedimiento anónimo en PL/SQL .

Actualizar:

El título de tu pregunta es un poco confuso.

Si solo necesita crear un procedimiento si no existe, entonces su código está bien.

Esto es lo que SSMS produce en el script de creación:

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N''myproc'')
                    AND type IN ( N''P'', N''PC'' ) ) 
DROP …
CREATE …

Actualizar:

Ejemplo de cómo hacerlo al incluir el esquema:

IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N''[dbo].[MyProc]'') 
                   and OBJECTPROPERTY(id, N''IsProcedure'') = 1 )
BEGIN
    DROP PROCEDURE [dbo].[MyProc]
END

En el ejemplo anterior, dbo es el esquema.

Actualizar:

En SQL Server 2016+, solo puedes hacer

CREATE OR ALTER PROCEDURE dbo.MyProc

Answer 15

Sé que desea "alterar un procedimiento si existe y solo eliminarlo si no existe", pero creo que es más simple simplemente abandonar el procedimiento y luego volver a crearlo. A continuación, se explica cómo cancelar el procedimiento solo si ya existe:

IF OBJECT_ID(''MyProcedure'', ''P'') IS NOT NULL
    DROP PROCEDURE MyProcedure
GO

El segundo parámetro le dice a OBJECT_ID que solo busque objetos con object_type = ''P'' , que son procedimientos almacenados:

AF = función de agregado (CLR)

C = restricción CHECK

D = POR DEFECTO (restricción o independiente)

F = restricción FOREIGN KEY

FN = función escalar SQL

FS = Asamblea (CLR) función escalar

FT = Asamblea (CLR) función con valores de tabla

IF = función de valores de tabla en línea SQL

IT = tabla interna

P = Procedimiento almacenado de SQL

PC = Ensamblado (CLR) procedimiento almacenado

PG = Guía del plan

PK = restricción de CLAVE PRIMARIA

R = Regla (estilo antiguo, independiente)

RF = Replicación-filtro-procedimiento

S = tabla base del sistema

SN = Sinónimo

SO = objeto de secuencia

TF = función de valor de tabla SQL

Puede obtener la lista completa de opciones a través de:

SELECT name 
FROM master..spt_values
WHERE type = ''O9T''

Answer 16

Sé que es una publicación muy antigua, pero como esto aparece en los resultados de búsqueda principales, se agrega la última actualización para aquellos que usan SQL Server 2016 SP1 -

create or alter procedure procTest
as
begin
 print (1)
end;
go

Esto crea un procedimiento almacenado si aún no existe, pero lo modifica si existe.

Reference

Answer 17

Si está buscando la forma más sencilla de verificar la existencia de un objeto de base de datos antes de eliminarlo, aquí hay una forma (el ejemplo utiliza un SPROC, como su ejemplo anterior, pero podría modificarse para tablas, índices, etc.):

IF (OBJECT_ID(''MyProcedure'') IS NOT NULL)
  DROP PROCEDURE MyProcedure
GO

Esto es rápido y elegante, pero debe asegurarse de tener nombres de objetos únicos en todos los tipos de objetos, ya que no tiene eso en cuenta.

¡Espero que esto ayude!

Answer 18

Yo tenía el mismo error. Sé que este hilo ya está bastante muerto, pero quiero configurar otra opción además del "procedimiento anónimo".

Lo resolví así:

1. Compruebe si existe el procedimiento almacenado:

   IF NOT EXISTS (SELECT * FROM sysobjects WHERE name=''my_procedure'') BEGIN
       print ''exists''  -- or watever you want
   END ELSE BEGIN
       print ''doesn''''texists''   -- or watever you want
   END

2. Sin embargo, el "CREATE/ALTER PROCEDURE'' must be the first statement in a query batch" todavía está allí. Lo resolví así:

   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE -- view procedure function or anything you want ...

3. Termino con este código:

   IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(''my_procedure''))
   BEGIN
       DROP PROCEDURE my_procedure
   END
   
   SET ANSI_NULLS ON
   GO
   SET QUOTED_IDENTIFIER ON
   GO
   
   CREATE PROCEDURE [dbo].my_procedure ...