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é!