Синтаксис проверяет все хранимые процедуры?

Ответ 1

Вы также можете сделать это "на месте" - без получения всех операторов создания.

В дополнение к настройке NOEXEC ON вам также нужно будет установить ваш любимый SHOWPLAN_* ON (я использую SHOWPLAN_TEXT). Теперь вы можете избавиться от своего шага 2 и просто выполнить каждую процедуру, которую вы получили на шаге 1.

Вот пример с использованием отдельной хранимой процедуры. Вы можете использовать его в своем любимом цикле:

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

Вышеприведенный образец должен сгенерировать следующий вывод:

Msg 208, Уровень 16, Состояние 1, Процедурные тесты, Строка 2
Недопустимое имя объекта 'missing_table_or_view'.

Ответ 2

Если вы используете sql 2008 r2 или ниже, не используйте

SET NOEXEC ON

Он проверяет синтаксис, а не на возможные ошибки, такие как наличие таблиц или столбцов. Вместо этого используйте:

УСТАНОВИТЬ FMTONLY ON

он выполнит полную компиляцию при попытке вернуть метаданные хранимой процедуры.

В 2012 году вам нужно будет использовать хранимую процедуру: sp_describe_first_result_set

Также вы можете сделать полный script в Tsql, который проверяет все sp и представления, это всего лишь небольшая работа.

UPDATE Я написал полное решение для tsql, которое проходит через все пользовательские хранимые процедуры и проверяет там синтаксис. script длинный, но можно найти здесь http://chocosmith.wordpress.com/2012/12/07/tsql-recompile-all-views-and-stored-proceedures-and-check-for-error/

Ответ 3

Проверка, предложенная KenJ, определенно самая лучшая, так как recreate/alter-approach не находит всех ошибок. Например.

• невозможные планы выполнения из-за подсказок-подсказок
• У меня даже был SP, ссылающийся на несуществующую таблицу, которая прошла без обнаружения ошибки.

Пожалуйста, найдите мою версию, которая проверяет все существующие SP одновременно с помощью метода KenJ ниже. AFAIK, он обнаружит каждую ошибку, которая будет препятствовать выполнению 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...

Ответ 4

Кроме того, вы можете рассмотреть возможность использования Visual Studio Team System 2008 Database Edition, который, помимо прочего, выполняет статическую проверку всех хранимые процедуры в проекте по сборке, гарантируя тем самым, что все согласуются с текущей схемой.

Ответ 5

Я знаю, что это старый способ, но я создал немного другую версию, которая фактически воссоздает все хранимые процедуры, тем самым бросая ошибки, если они не могут скомпилироваться. Этого не достигается с помощью команды 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

Ответ 6

Немного затянутый вариант:

• Создать копию базы данных (резервное копирование и восстановление). Вы можете сделать это в целевой базе данных, если ваш уровень достоверности высокий.
• Используйте SSMS для script всех хранимые процедуры в один файл script
• УКАЗАТЬ все процедуры
• Запустите script, чтобы воссоздать их. Любой, который не может быть создан, будет отключен.

Пара суетливых ошибок здесь, например:

• Вы хотите иметь "if proc" затем отпустите proc GO create proc... GO " синтаксис для каждой процедуры.
• Вложенные процедуры потерпят неудачу, если они вызовите proc, который еще не был (Вос) создано. Запуск script нескольких времени следует поймать (с тех пор упорядочение их должным образом может быть реальным боль).
• Другие и более неясные проблемы могут возникнуть, поэтому будьте осторожны.

Чтобы быстро сбросить 10 или 1000 процедур, запустите

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

выберите выход и запустите его.

Это предполагает, что вы выполняете очень редкую задачу. Если вам нужно делать это регулярно (ежедневно, еженедельно...), пожалуйста, сообщите нам почему!

Ответ 7

Нет способа сделать это из T-SQL или Enterprise Manager, поэтому мне пришлось написать что-то из кода клиента. я не буду публиковать весь код здесь, но трюк заключается в следующем:

1) Получить список всех хранимых процедур

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

2) Получить хранимую процедуру для создания 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) Запустите инструкцию create с NOEXEC, чтобы синтаксис был проверен, но на самом деле он не пытается создать хранимую процедуру:

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

Ответ 8

Вот поправка, которая касается нескольких схем

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 ['[email protected]+']'
    PRINT @SQLString
    EXECUTE sp_ExecuteSQL @SQLString

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

Ответ 9

Я знаю, что это старый вопрос, но это мое решение, когда я не мог найти подходящего.

Мне нужно проверить мои хранимые процедуры и представления после многих изменений в базе данных.

В основном то, что я хотел, это попытаться выполнить ALTER PROCEDURE и ALTER VIEW с помощью текущих процедур и представлений (фактически не изменяя их).

Я написал это, что работает достаточно хорошо.

Внимание! Не выполняйте работу в живой базе данных, создайте копию для проверки, а затем исправьте все, что нужно для исправления. Также sys.sql_modules могут быть непоследовательными, поэтому проявляйте особую осторожность. Я не использую это, чтобы на самом деле внести изменения, только чтобы проверить, какие из них не работают должным образом.

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