Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
Добрый день всем.

SQL 2014

Как можно определить все не валидные SP на БД
НЕ выполняя их
DDL накатываются в автомат.режиме - некоторые SP и View могут стать не валидными
из за изменения полей.

с View проще - можно сгенерить скрипт
Select top 200 * from View1,2,3 ...
выполнить - и ошибки вылезут сразу.

Вот бы такой фокус с SP - но выполнять их Нельзя ( это ETL - они загружают данные )
19 дек 17, 12:41    [21045131]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36696
Выполнить их на тестовом окружении. Вообще, для этого придумали процесс тестирования.
19 дек 17, 12:43    [21045140]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Гулин Федор,

А SET FMTONLY ON не спасет?
19 дек 17, 13:24    [21045331]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
invm
Member

Откуда: Москва
Сообщений: 9128
Гулин Федор
НЕ выполняя их
select * from sys.dm_sql_referenced_entities('процедура', 'object');
Даст ошибку.
19 дек 17, 13:25    [21045339]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Вы, для начала, вменяемо опишите, что такое "валидность" и "невалидность"(кстати, пишется в одно слово).
19 дек 17, 13:40    [21045397]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
invm
Гулин Федор
НЕ выполняя их
select * from sys.dm_sql_referenced_entities('процедура', 'object');
Даст ошибку.


ЗОЛОТЫЕ СЛОВА !!!
СПАСИБО

The dependencies reported for entity "[ETL].[Account_Receivable_Fact]" might not include references to all columns.
This is either because the entity references an object that does not exist or because of an error in one or more statements in the entity.
Before rerunning the query, ensure that there are no errors in the entity and that all objects referenced by the entity exist.



ЗЫ как чувствовал что-то такое должно быть
19 дек 17, 13:43    [21045412]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33271
Блог
-- ссылки на отсутствующие объекты
select object_name(t1.referencing_id) as [Объект],
       t1.referenced_class_desc as [Вид связи],
       t1.referenced_schema_name as [Схема ссылочного объекта],
       t1.referenced_entity_name as [Имя ссылочного объекта],
       t3.object_id as [ID ссылочного объекта]
  from sys.sql_expression_dependencies as t1
  left join sys.objects as t3 on SCHEMA_NAME(t3.schema_id) = t1.referenced_schema_name and
                                 t3.name = t1.referenced_entity_name
  where t3.object_id is null and
        len(t1.referenced_schema_name) > 2 and    -- исключаем всякие with и прочее
        len(t1.referenced_entity_name) > 3 and    -- исключаем всякие with и прочее
        t1.referenced_schema_name is not null and -- исключаем, если схема не указана
        t1.referenced_database_name is null and   -- исключаем ссылки на другие базы данных
        t1.referenced_class_desc <> 'TYPE' and    -- исключаем табличные типы
        object_name(t1.referencing_id) <> 'sp_upgraddiagrams' and
        object_name(t1.referencing_id) not like '%for_del%'
19 дек 17, 13:55    [21045483]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Критик
-- ссылки на отсутствующие объекты
select object_name(t1.referencing_id) as [Объект],
       t1.referenced_class_desc as [Вид связи],
       t1.referenced_schema_name as [Схема ссылочного объекта],
       t1.referenced_entity_name as [Имя ссылочного объекта],
       t3.object_id as [ID ссылочного объекта]
  from sys.sql_expression_dependencies as t1
  left join sys.objects as t3 on SCHEMA_NAME(t3.schema_id) = t1.referenced_schema_name and
                                 t3.name = t1.referenced_entity_name
  where t3.object_id is null and
        len(t1.referenced_schema_name) > 2 and    -- исключаем всякие with и прочее
        len(t1.referenced_entity_name) > 3 and    -- исключаем всякие with и прочее
        t1.referenced_schema_name is not null and -- исключаем, если схема не указана
        t1.referenced_database_name is null and   -- исключаем ссылки на другие базы данных
        t1.referenced_class_desc <> 'TYPE' and    -- исключаем табличные типы
        object_name(t1.referencing_id) <> 'sp_upgraddiagrams' and
        object_name(t1.referencing_id) not like '%for_del%'

Добавьте в запрос еще "exist" и "value", иначе XML-запросы тоже в результат валит.
19 дек 17, 14:05    [21045554]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
@Критик
Интересный запрос
СПС - мне пока надо тока НЕ валидные - я их списком нашел по ошибке как Invm указал


но думаю будет полезен в дальнейшем
не могу быстро сообразить как схему и тип (View , SP ) головоного объекта выдать
19 дек 17, 14:25    [21045667]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Col
Member

Откуда: Торонто
Сообщений: 177
Гулин Федор,

	   SELECT
    '[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']' 
        AS [this sproc, UDF or VIEW...],
    isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']' 
        AS [... depends ON this missing entity name]
FROM 
    sys.sql_expression_dependencies
WHERE 
    is_ambiguous = 0 AND 
    (
        (
            [referenced_class_desc] = 'TYPE' and 
            TYPE_ID(
                isnull('[' + referenced_schema_name + '].', '') + 
                '[' + referenced_entity_name + ']'
            ) IS NULL
        ) or
        (   
            [referenced_class_desc] <> 'TYPE' and 
            OBJECT_ID(
                isnull('[' + referenced_schema_name + '].', '') + 
                '[' + referenced_entity_name + ']'
            ) IS NULL
        )
    )
ORDER BY 
    '[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']',
    isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']'
19 дек 17, 21:51    [21047023]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Гулин Федор,

Кстати, все эти подходы не применимы в общем случае. А точнее, когда в процедурах используется динамический SQL. Очевидно, что если ссылка на объект содержится не в тексте процедуры, а в строковой констатнте, то и проверить эту ссылку без запуска процедуры невозможно.
Есть у меня решение, кстати, где подавляющее большинство хранимых процедур, выступающих в качестве источников данных отчетов, именно такие.
19 дек 17, 22:13    [21047070]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
wouef
Guest
и не только из-за динамического sql

use [tempdb]
go

-- создаём код
if exists (select 1 from sys.tables where object_id = OBJECT_ID(N'dbo.tableA')) drop table dbo.tableA;
go
create table dbo.tableA ( f1 int null );
go
insert into dbo.tableA ( f1 ) values ( 1 );
go

if exists (select 1 from sys.objects WHERE object_id = OBJECT_ID(N'dbo.functionA') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	drop function dbo.functionA
GO

create function dbo.functionA ()
returns int
as begin
	return (select top (1) f1 from dbo.tableA);
end
go

-- всё работает
select OBJECT_NAME(object_id), object_id, modify_date from sys.all_objects where object_id in (OBJECT_ID(N'dbo.tableA'), OBJECT_ID(N'dbo.functionA'))
go
select dbo.functionA ();
go

-- меняем код
alter table dbo.tableA add f2 int null;
alter table dbo.tableA drop column f1;
go


-- dependencies ничего не видит
-- ссылки на отсутствующие объекты
select object_name(t1.referencing_id) as [Объект],
       t1.referenced_class_desc as [Вид связи],
       t1.referenced_schema_name as [Схема ссылочного объекта],
       t1.referenced_entity_name as [Имя ссылочного объекта],
       t3.object_id as [ID ссылочного объекта]
  from sys.sql_expression_dependencies as t1
  left join sys.objects as t3 on SCHEMA_NAME(t3.schema_id) = t1.referenced_schema_name and
                                 t3.name = t1.referenced_entity_name
  where t3.object_id is null and
        len(t1.referenced_schema_name) > 2 and    -- исключаем всякие with и прочее
        len(t1.referenced_entity_name) > 3 and    -- исключаем всякие with и прочее
        t1.referenced_schema_name is not null and -- исключаем, если схема не указана
        t1.referenced_database_name is null and   -- исключаем ссылки на другие базы данных
        t1.referenced_class_desc <> 'TYPE' and    -- исключаем табличные типы
        object_name(t1.referencing_id) <> 'sp_upgraddiagrams' and
        object_name(t1.referencing_id) not like '%for_del%'
GO


-- функция не работает
select dbo.functionA ();
go

-- смотрим почему
select N'dependencies работает, только если объекты ссылаются на более старые объекты'
go
select OBJECT_NAME(object_id), object_id, modify_date from sys.all_objects where object_id in (OBJECT_ID(N'dbo.tableA'), OBJECT_ID(N'dbo.functionA'))
go
20 дек 17, 10:52    [21047878]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3465
В общем случае задача не решаема.
1. из-за динамических sql
2. из-за не всегда актуальных зависимостей
3. даже выполнение не поможет - может быть ветвление (параметры, разные условия, разные данные)
20 дек 17, 10:55    [21047893]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ролг Хупин,

автор
This includes dependences between natively compiled, scalar user-defined functions and other SQL Server modules

что надо сделать?
20 дек 17, 10:59    [21047912]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Ролг Хупин,

автор
3. даже выполнение не поможет - может быть ветвление (параметры, разные условия, разные данные)

ну компилируется без ветвлений
20 дек 17, 11:01    [21047918]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
wouef
Guest
был продукт https://www.osp.ru/cw/1998/12/28318/
который в Oracle для статичных sql (на тот момент в MSSQL поля modify_date не было) позволял исправлять dependencies с помощью alter.
20 дек 17, 11:19    [21047993]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
ptr128
Гулин Федор,

Кстати, все эти подходы не применимы в общем случае. А точнее, когда в процедурах используется динамический SQL. Очевидно, что если ссылка на объект содержится не в тексте процедуры, а в строковой констатнте, то и проверить эту ссылку без запуска процедуры невозможно.
Есть у меня решение, кстати, где подавляющее большинство хранимых процедур, выступающих в качестве источников данных отчетов, именно такие.


Меня интерусует мой ЧАСТНЫЙ СЛУЧАЙ
а для моего частного случая - подсказка InvM мне уже помогла
я быстро понаходил ставшие не валидными SP

динам. скл в данном контектсе меня не интересует в принципе
у меня есть БД с > 100 SP и > 100 View
(причем там куча легаси к-е и могут стать НЕ валидными - и проверяю только саб-сет )

а Критику спасибо за его запрос

зы Увидел варинат Col
Смотрю сейчас его - вроде там Полные имена объектов
20 дек 17, 17:26    [21049581]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
вот лучше подскажите как типы объектов достать в запрос
любой из 2-х

мне бы выдать красиво
SP , View

SP там правда видны по префиоксу usp_
а вот таблицы от вьюх отличить с виду нельзя
20 дек 17, 17:31    [21049602]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
ptr128
Member

Откуда: Moscow
Сообщений: 398
Гулин Федор
Меня интерусует мой ЧАСТНЫЙ СЛУЧАЙ

Ну мне же откуда знать, встречается в Ваших процедурах динамический SQL или нет? Поэтому просто предупредил )
20 дек 17, 17:38    [21049623]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Гулин Федор,

https://docs.microsoft.com/ru-ru/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql
select name, type from sys.all_objects
20 дек 17, 17:42    [21049637]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Cammomile
Member

Откуда:
Сообщений: 1212
Соорян, для олл_обжекстс вот этот линк
https://docs.microsoft.com/ru-ru/sql/relational-databases/system-catalog-views/sys-all-objects-transact-sql
20 дек 17, 17:43    [21049642]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Col
Member

Откуда: Торонто
Сообщений: 177
Гулин Федор
зы Увидел варинат Col
Смотрю сейчас его - вроде там Полные имена объектов

Они самые, где-то еще был вариант прохода по всем базам с выводом и имени базы, но чет не нахожу в запаснике, видать затерелось где в черновиках.
20 дек 17, 21:12    [21050101]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
чисто косметические изменения под себя - чтобы типы объектов были
(я так понимаю по )
Всем СПАСИБО за идеи и обсуждение.

SELECT
 o1.type_desc as Type1
,'[' + OBJECT_SCHEMA_NAME(referencing_id) + '].[' + OBJECT_NAME(referencing_id) + ']'  	AS [Obj1] -- this sproc, UDF or VIEW...
,isnull('[' + referenced_schema_name + '].', '') + '[' + referenced_entity_name + ']' 	AS [ref_obj]-- [... depends ON this missing entity name]
,a1.referencing_id
-- , a1.*
FROM 
sys.sql_expression_dependencies a1
LEFT JOIN   sys.all_objects o1
       ON o1.object_id = a1.referencing_id -- referenced_id
WHERE 
is_ambiguous = 0 
AND 
(
	(
		[referenced_class_desc] = 'TYPE' and 
		TYPE_ID(
			isnull('[' + referenced_schema_name + '].', '') + 
			'[' + referenced_entity_name + ']'
		) IS NULL
	) or
	(   
		[referenced_class_desc] <> 'TYPE' and 
		OBJECT_ID(
			isnull('[' + referenced_schema_name + '].', '') + 
			'[' + referenced_entity_name + ']'
		) IS NULL
	)
)
ORDER BY  1 ,2, 3 
;
21 дек 17, 13:01    [21051547]     Ответить | Цитировать Сообщить модератору
 Re: определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
PS
мысль не закончил - в общем случае раз есть
referenced_server_name , referenced_database_name
то возможно найдет нестыковки и по ссылкам на другие БД и линк-сервера
но это я не проверял
21 дек 17, 13:05    [21051569]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: определить не валидные SP  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1202
через N лет возникла такая же задача
но чтобы еще выдавало с полями НЕ корректными
(последний запрос работает ОК )
но туда не попадают SP ссылающиеся на поля к-е были переимунованы

EXEC sp_rename 'Debit.id_treb', 'Id_Debit', 'COLUMN'

причем в SSMS показывает красным но

Alter Procedure
без проблем

Как бы это отловить ставшие невалидными поля
(учитывая то что в #Temp таблицах они могут назыавться по старому)
любые идели велком
21 янв 20, 10:40    [22063540]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить