sql server - tipos - ¿Revisar la sintaxis de todos los procedimientos almacenados?

ver codigo procedimiento almacenado sql server

Question

Quiero asegurarme de que todos los procedimientos almacenados todavía son sintácticamente válidos. (Esto puede suceder si alguien renombra / borra una tabla / columna).

En este momento, mi solución para verificar la sintaxis de todos los procedimientos almacenados es ingresar a Enterprise Manager, seleccionar el primer procedimiento almacenado en la lista y usar el procedimiento:

1. Entrar
2. Alt + C
3. Escapar
4. Escapar
5. Flecha hacia abajo
6. Goto 1

Funciona, pero es bastante tedioso. me gustaría un procedimiento almacenado llamado

SyntaxCheckAllStoredProcedures

como el otro procedimiento almacenado que escribí que hace lo mismo para las vistas:

Actualizar todas las vistas

Para el beneficio de todos, RefreshAllViews:

ActualizarAllViews.prc

CREATE PROCEDURE dbo.RefreshAllViews AS

-- This sp will refresh all views in the catalog. 
--     It enumerates all views, and runs sp_refreshview for each of them

DECLARE abc CURSOR FOR
     SELECT TABLE_NAME AS ViewName
     FROM INFORMATION_SCHEMA.VIEWS
OPEN abc

DECLARE @ViewName varchar(128)

-- Build select string
DECLARE @SQLString nvarchar(2048)

FETCH NEXT FROM abc 
INTO @ViewName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @SQLString = ''EXECUTE sp_RefreshView ''+@ViewName
    PRINT @SQLString
    EXECUTE sp_ExecuteSQL @SQLString

    FETCH NEXT FROM abc
    INTO @ViewName
END
CLOSE abc
DEALLOCATE abc

Para el beneficio de todos, un procedimiento almacenado para marcar todo el procedimiento almacenado como una necesidad de recompilación (marcar un procedimiento almacenado para la recompilación no le dirá si es sintácticamente válido):

RecompileAllStoredProcedures.prc

CREATE PROCEDURE dbo.RecompileAllStoredProcedures AS

DECLARE abc CURSOR FOR
     SELECT ROUTINE_NAME
     FROM INFORMATION_SCHEMA.routines
    WHERE ROUTINE_TYPE = ''PROCEDURE''
OPEN abc

DECLARE @RoutineName varchar(128)

-- Build select string once 
DECLARE @SQLString nvarchar(2048)

FETCH NEXT FROM abc 
INTO @RoutineName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @SQLString = ''EXECUTE sp_recompile ''+@RoutineName
    PRINT @SQLString
    EXECUTE sp_ExecuteSQL @SQLString

    FETCH NEXT FROM abc
    INTO @RoutineName
END
CLOSE abc
DEALLOCATE abc

Para completar, el procedimiento UpdateAllStatistics . Esto actualizará todas las estadísticas en la base de datos haciendo un análisis completo de los datos:

ActualizarAllStatistics.prc

CREATE PROCEDURE dbo.RefreshAllStatistics AS

EXECUTE sp_msForEachTable ''UPDATE STATISTICS ? WITH FULLSCAN''

Answer 1

Además, es posible que desee considerar utilizar Visual Studio Team System 2008 Database Edition , que, entre otras cosas, realiza una verificación estática de todos los procedimientos almacenados en el proyecto en la compilación, lo que garantiza que todos sean coherentes con el esquema actual.

Answer 2

Aquí hay una enmienda que trata con múltiples esquemas.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[RefreshAllViews] AS

-- This sp will refresh all views in the catalog. 
--     It enumerates all views, and runs sp_refreshview for each of them

DECLARE abc CURSOR FOR
     SELECT TABLE_SCHEMA+''.''+TABLE_NAME AS ViewName
     FROM INFORMATION_SCHEMA.VIEWS
OPEN abc

DECLARE @ViewName varchar(128)

-- Build select string
DECLARE @SQLString nvarchar(2048)

FETCH NEXT FROM abc 
INTO @ViewName
WHILE @@FETCH_STATUS = 0 
BEGIN
    SET @SQLString = ''EXECUTE sp_RefreshView [''+@ViewName+'']''
    PRINT @SQLString
    EXECUTE sp_ExecuteSQL @SQLString

    FETCH NEXT FROM abc
    INTO @ViewName
END
CLOSE abc
DEALLOCATE abc
GO

Answer 3

El chequeo sugerido por KenJ es definitivamente el mejor, ya que recreate / alter-enfoques no encuentra todos los errores. P.ej

• planes de ejecución imposibles debido a sugerencias de consulta
• Incluso tuve un SP que hacía referencia a una tabla no existente que se realizó sin que se detectara el error.

Encuentre mi versión que comprueba todos los SP existentes a la vez con el método de KenJ a continuación. AFAIK, detectará todos los errores que impidan que se ejecute el SP.

--Forces the creation of execution-plans for all sps.
--To achieve this, a temporary SP is created that calls all existing SPs.
--It seems like the simulation of the parameters is not necessary. That makes things a lot easier.
DECLARE @stmt NVARCHAR(MAX) = ''CREATE PROCEDURE pTempCompileTest AS '' + CHAR(13) + CHAR(10)
SELECT @stmt = @stmt + ''EXEC ['' + schemas.name + ''].['' + procedures.name + ''];''
    FROM sys.procedures
        INNER JOIN sys.schemas ON schemas.schema_id = procedures.schema_id
    WHERE schemas.name = ''dbo''
    ORDER BY procedures.name

EXEC sp_executesql @stmt
GO

--Here, the real magic happens.
--In order to display as many errors as possible, XACT_ABORT is turned off.
--Unfortunately, for some errors, the execution stops anyway.
SET XACT_ABORT OFF
GO
--Showplan disables the actual execution, but forces t-sql to create execution-plans for every statement.
--This is the core of the whole thing!
SET SHOWPLAN_ALL ON
GO
--You cannot use dynamic SQL in here, since sp_executesql will not be executed, but only show the string passed in in the execution-plan
EXEC pTempCompileTest
GO
SET SHOWPLAN_ALL OFF
GO
SET XACT_ABORT ON
GO
--drop temp sp again
DROP PROCEDURE pTempCompileTest
--If you have any errors in the messages-window now, you should fix these...

Answer 4

No hay forma de hacerlo desde T-SQL o Enterprise Manager, así que tuve que escribir algo desde el código del cliente. No publicaré todo el código aquí, pero el truco es:

1) Obtener una lista de todos los procedimientos almacenados

 SELECT ROUTINE_NAME AS StoredProcedureName
 FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = ''PROCEDURE'' --as opposed to a function
 ORDER BY ROUTINE_NAME

2) Obtener el procedimiento almacenado crear T-SQL:

select
   c.text
from dbo.syscomments c
where c.id = object_id(N''StoredProcedureName'')
order by c.number, c.colid
option(robust plan)

3) Ejecute la declaración de creación con NOEXEC activado, para que se compruebe la sintaxis, pero en realidad no intenta crear el procedimiento almacenado:

connection("SET NOEXEC ON", ExecuteNoRecords);
connection(StoredProcedureCreateSQL, ExecuteNoRecords);
connection("SET NOEXEC ON", ExecuteNoRecords);

Answer 5

Sé que es muy antiguo, pero creé una versión ligeramente diferente que en realidad vuelve a crear todos los procedimientos almacenados, por lo que arroja errores si no se pueden compilar. Esto es algo que no se logra con el comando SP_Recompile.

CREATE PROCEDURE dbo.UTL_ForceSPRecompilation
(
    @Verbose BIT = 0
)
AS
BEGIN

    --Forces all stored procedures to recompile, thereby checking syntax validity.

    DECLARE @SQL NVARCHAR(MAX)
    DECLARE @SPName NVARCHAR(255)           

    DECLARE abc CURSOR FOR
         SELECT NAME, OBJECT_DEFINITION(o.[object_id])
         FROM sys.objects AS o 
         WHERE o.[type] = ''P''
         ORDER BY o.[name]

    OPEN abc

    FETCH NEXT FROM abc
    INTO @SPName, @SQL
    WHILE @@FETCH_STATUS = 0 
    BEGIN       

        --This changes "CREATE PROCEDURE" to "ALTER PROCEDURE"
        SET @SQL = ''ALTER '' + RIGHT(@SQL, LEN(@SQL) - (CHARINDEX(''CREATE'', @SQL) + 6))

        IF @Verbose <> 0 PRINT @SPName

        EXEC(@SQL)

        FETCH NEXT FROM abc
        INTO @SPName, @SQL
    END
    CLOSE abc
    DEALLOCATE abc  

END

Answer 6

Sé que esta es una pregunta antigua, pero esta es mi solución cuando no pude encontrar ninguna satisfacción.

Necesité validar mis procedimientos y vistas almacenados después de muchos cambios en la base de datos.

Básicamente, lo que quería era intentar realizar un ALTER PROCEDURE y ALTER VIEW utilizando los procedimientos y la vista actuales (sin cambiarlos realmente).

He escrito esto que funciona bastante bien.

¡Nota! No realice en la base de datos en vivo, haga una copia para validar y luego arregle las cosas que deben solucionarse. Además, sys.sql_modules puede ser inconsistente, así que tenga mucho cuidado. No utilizo esto para realizar los cambios, solo para verificar que no funcionan correctamente.

DECLARE @scripts TABLE
(
    Name NVARCHAR(MAX),
    Command NVARCHAR(MAX),
    [Type] NVARCHAR(1)
)

DECLARE @name NVARCHAR(MAX),        -- Name of procedure or view
    @command NVARCHAR(MAX),         -- Command or part of command stored in syscomments
    @type NVARCHAR(1)               -- Procedure or view

INSERT INTO @scripts(Name, Command, [Type])
SELECT P.name, M.definition, ''P'' FROM sys.procedures P 
JOIN sys.sql_modules M ON P.object_id = M.object_id

INSERT INTO @scripts(Name, Command, [Type])
SELECT V.name, M.definition, ''V'' FROM sys.views V 
JOIN sys.sql_modules M ON V.object_id = M.object_id

DECLARE curs CURSOR FOR
SELECT Name, Command, [Type]  FROM @scripts

OPEN curs

FETCH NEXT FROM curs
INTO @name, @command, @type


WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        IF @type = ''P''
            SET @command = REPLACE(@command, ''CREATE PROCEDURE'', ''ALTER PROCEDURE'')
        ELSE
            SET @command = REPLACE(@command, ''CREATE VIEW'', ''ALTER VIEW'')


        EXEC sp_executesql @command
        PRINT @name + '' - OK''
    END TRY
    BEGIN CATCH
        PRINT @name + '' - FAILED: '' + CAST(ERROR_NUMBER() AS NVARCHAR(MAX)) + '' '' + ERROR_MESSAGE()
        --PRINT @command
    END CATCH

    FETCH NEXT FROM curs
    INTO @name, @command, @type
END

CLOSE curs 

Answer 7

Si está utilizando sql 2008 r2 o inferior, no use

CONFIGURAR NOEXEC EN

Solo comprueba la sintaxis y no busca errores potenciales como la existencia de tablas o columnas. En su lugar, utilice:

CONFIGURAR FMTONLY EN

hará una compilación completa mientras intenta devolver los metadatos del procedimiento almacenado.

Para 2012 y necesitará usar el procedimiento almacenado: sp_describe_first_result_set

También puede hacer un script completo en Tsql que verifique todos los sp y las vistas, es solo un poco de trabajo.

ACTUALIZACIÓN Escribí una solución completa para tsql que pasa por todos los procedimientos almacenados definidos por el usuario y verifica la sintaxis. La secuencia de comandos es larga, pero se puede encontrar aquí http://chocosmith.wordpress.com/2012/12/07/tsql-recompile-all-views-and-stored-proceedures-and-check-for-error/

Answer 8

También puede hacer esto "en el lugar", sin obtener todas las declaraciones de creación.

Además de configurar NOEXEC ON , también necesitará configurar su SHOWPLAN_* ON favorito (yo uso SHOWPLAN_TEXT ). Ahora puede deshacerse de su paso 2 y simplemente ejecutar cada procedimiento que recuperó en el paso 1.

Aquí hay una muestra utilizando un procedimiento almacenado individual. Puedes trabajar en tu bucle favorito:

create procedure tests @bob int as 
select * from missing_table_or_view
go 

set showplan_text on; 
go 

set noexec on 

exec tests 

set noexec off 
go 
set showplan_XML off 
go 
drop procedure tests 
go

La muestra anterior debe generar la siguiente salida:

Msg 208, nivel 16, estado 1, pruebas de procedimiento, línea 2
Nombre de objeto no válido ''missing_table_or_view''.

Answer 9

Un poco de una opción extraída:

1. Crea una copia de la base de datos (copia de seguridad y restauración). Puede hacer esto en la base de datos de destino, si su nivel de confianza es alto.
2. Utilice SSMS para realizar un script de todos los procedimientos almacenados en un solo archivo de script
3. GOTA todos los procedimientos.
4. Ejecuta el script para recrearlos. Cualquiera que no se pueda crear se producirá un error.

Un par de trampas meticulosas aquí, como:

• Desea tener la sintaxis "si proc existe, entonces elimine proc GO create proc ... GO" para separar cada procedimiento.
• Los procedimientos anidados fallarán si llaman a un proceso que aún no se ha (re) creado. Ejecutar el script varias veces debería captar eso (ya que ordenarlos correctamente puede ser un verdadero dolor).
• Otros problemas más oscuros pueden surgir, así que ten cuidado.

Para soltar rápidamente 10 o 1000 procedimientos, ejecute

SELECT ''DROP PROCEDURE '' + schema_name(schema_id) + ''.'' +  name
 from sys.procedures

Seleccione la salida, y ejecútelo.

Esto supone que estás haciendo una tarea muy infrecuente. Si tiene que hacer esto regularmente (a diario, semanalmente ...), ¡háganos saber por qué!