La syntaxe vérifie toutes les procédures stockées?

Vous pouvez également le faire "sur place" - sans se toutes les instructions de création.

Outre la définition de l' NOEXEC ONvous aurez également besoin de mettre votre favori SHOWPLAN_* ON (j'utilise SHOWPLAN_TEXT). Maintenant, vous pouvez vous débarrasser de votre étape 2 et de l'exécuter à chaque procédure que vous avez récupéré à l'étape 1.

Voici un exemple de l'aide de la procédure stockée. Vous pouvez l'utiliser dans votre préféré boucle:

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

L'exemple ci-dessus doit générer le résultat suivant:

Msg 208, Niveau 16, État 1, Procédure de tests, Ligne 2

Nom d'objet non valide 'missing_table_or_view'.

Le chèque proposé par KenJ est certainement le meilleur d'un seul, depuis le recréer/alter-approches ne trouve pas toutes les erreurs. E. g.

• impossible l'exécution des plans en raison de requête-conseils
• J'ai même eu un SP référencement d'un non-table existante qui est allé sans l'erreur est détectée.

Veuillez trouver ma version qui vérifie tous les SPs à la fois avec KenJ la méthode ci-dessous. Autant que je sache, il permet de détecter toutes les erreurs qui va garder la SP d'être exécutée.

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

Je sais que c'est vieux, mais j'ai créé une version légèrement différente qui fait re-crée de toutes les procédures stockées, jetant ainsi les erreurs si elles ne peuvent pas compiler. C'est quelque chose que vous n'avez pas d'atteindre à l'aide de la SP_Recompile commande.

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

Il n'y a aucun moyen de le faire à partir de T-SQL, ou Enterprise Manager, donc j'ai dû écrire quelque chose de code client. je ne vais pas poster tout le code ici, mais l'astuce consiste à:

1) Obtenez une liste de toutes les procédures stockées

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

2) Obtenir la procédure stockée créer des 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) Exécutez l'instruction de création avec l'option NOEXEC, de sorte que la syntaxe est vérifié, mais il n'a pas vraiment d'essayer de créer la procédure stockée:

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

Je sais que c'est une vieille question, mais c'est ma solution quand je ne pouvais pas trouver un répondant.

- Je valider mes procédures stockées et des vues après beaucoup de changements dans la base de données.

Bref ce que je voulais, c'était pour essayer de faire un ALTER PROCEDURE et de MODIFIER la VUE en utilisant les procédures actuelles et de la vue (et non pas le modifier).

J'ai écrit ce qui fonctionne assez bien.

Remarque! Ne pas effectuer en direct à la base de données, en faire une copie pour valider et corriger ensuite les choses ont besoin de fixation. Aussi sys.sql_modules peut être incompatible donc prendre des précautions supplémentaires. Je n'ai pas l'utiliser pour des modifications, seulement pour vérifier ce qui ne fonctionne pas correctement.

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