Find invalid objects

[@AlanDenton]

К минусам неплохо было-бы прикреплять свои комментарии :)

[@p777]

Я не специалист по базам данным. Мне не понятно, что такое не валидные объекты зачем их надо искать.

[@vladimirkolyada]

Это объекты в базе данных, которые там созданы, лежат себе такие спокойно, например хранимые процедуры. По прошествии времени кто-то поменял таблицу, с которой работала эта процедура. Допустим удалил колонку. Вроде бы в базе есть и хранимая процедура и таблица. Но колонки не стало. Там нет перекомпиляции:) Вот вам и ошибка времени выполнения. А таких мест может быть не одна сотня. Хотя, конечно, есть механизмы отображения зависимостей и все это надо учитывать (и многое другое), но так бывает.

[@p777]

спасибо, теперь все стало на свои места :)

[@FractalizeR]

Поддерживаю. «невалидные объекты» — термин, применимый не ко всем СУБД, так что хотелось бы понять, где они есть и что они собой представляют. В MySQL, Postgres, SQLite я не помню такой терминологии.

[@AlanDenton]

Точно знаю, что невалидные объекты есть в Oracle и SQL Server, поскольку на уровне метаданных есть соответствующие признаки. Например, Oracle переводит объекты в состояние INVALID, если зависимый объект изменяется. Если следующая компиляция проходит успешно, то объект становится помечается как VALID.

[@AlanDenton]

На уровне метаданных, в SQL Server существуют понятия soft и hard зависимостей. Последние всегда валидируют связанные объекты при их изменении или попытке удаления.

Пример таких зависимостей – скалярная функция, которая используется в COMPUTED столбце:

CREATE FUNCTION dbo.udf_Computed (@a INT)
RETURNS INT
AS BEGIN
	RETURN @a
END
GO

CREATE TABLE dbo.tbl_Table (
    ID INT,
    Value AS dbo.udf_Computed(ID)
)
GO

DROP FUNCTION dbo.udf_Computed
GO

Msg 3729, Level 16, State 1, Line 2
Cannot DROP FUNCTION 'dbo.udf_Computed' because it is being referenced by object 'tbl_Table'.

В свою очередь, soft зависимости таким свойством не обладают. На практике же, существует большое количество нюансов с которыми я столкнулся, когда реализовывал поиск зависимостей в этом туле. Если будет желание могу описать такие случаи в отдельном посте по мере появления свободного времени.

[@kolu4iy]

Первый скрипт на 2008r2 мне отчего-то выдал
Msg 3930, Level 16, State 1, Line 95
Текущая транзакция не может быть зафиксирована и не может поддерживать операции, производящие запись в файл журнала. Выполните откат транзакции.

много раз :)

Падает в:

    BEGIN TRY
        EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
    END TRY
    BEGIN CATCH
        INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
        SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type
    END CATCH

на INSERT.

[@vladimirkolyada]

В Management Studio после выполнения запроса миниум 2 закладки. Results и Messages. Откройте Результаты уже скорее:) В Messages выводит SELECT в самом скрипте.

[@kolu4iy]

Предупреждать надо :) Я привык не доверять результатам, если в messages куча ошибок :)
Плюсануть не могу, потому спасибо так.

[@vladimirkolyada]

Меня это тоже настораживает.

[@AlanDenton]

Спасибо за комментарий. Я проверю в чем может быть проблема и обновлю пост.

[@AlanDenton]

Попробуйте заменить блок:

BEGIN TRY
    EXEC sys.sp_refreshsqlmodule @name = @obj_name
END TRY
BEGIN CATCH
    INSERT INTO #objects (obj_name, err_message, obj_type) 
    SELECT @obj_name, ERROR_MESSAGE(), @obj_type
END CATCH

на вот такой:

BEGIN TRY

	BEGIN TRANSACTION
		EXEC sys.sp_refreshsqlmodule @name = @obj_name, @namespace = N'OBJECT' 
	COMMIT TRANSACTION

END TRY
BEGIN CATCH
	INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
	SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type

	IF XACT_STATE() <> 0
		ROLLBACK TRANSACTION
END CATCH

[@kolu4iy]

Теперь ошибка всего одна:
Msg 3930, Level 16, State 1, Line 99
Текущая транзакция не может быть зафиксирована и не может поддерживать операции, производящие запись в файл журнала. Выполните откат транзакции.

Справедливо происходит на

INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
    SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type

ибо предыдущая транзакция незафиксирована. Т.е. по идее, ее надо откатить раньше.
Но если собрать блок catch вот так:

	BEGIN CATCH
		IF XACT_STATE() <> 0
			ROLLBACK TRANSACTION

		INSERT INTO #objects (obj_id, obj_name, err_message, obj_type) 
		SELECT @obj_id, @obj_name, ERROR_MESSAGE(), @obj_type
	END CATCH

то проблем не возникает.