Поиск всех процедур и функций по названию конкретного столбца

#sql #sql_server

Как можно вывести название всех процедур и функций, в которых используется конкретный
столбец, точнее имя столбца? С поиском в таблицах и представлениях я разобрался, а
с процедурами понимаю не так все просто.

Я не знаю как вывести (возможно через запрос) все названия процедур которые находятся
в моей базе, по конкретному столбцу. То есть, все процедуры в которых мы или используем
<Название столбца>для решения процедуры или используем для вывода в select. Решить
проблему надеюсь с помощью запроса T-SQL, потому что вывод всех процедур из базы данных
реализовать можно,думаю значить и можно от фильтровать, только не знаю как.  В результате,
в конце концов мне нужны названия процедур в которых встречается  <Название столбца>
    

Ответы

Ответ 1

Не знаю, возможно ли получить точный список процедур, где используется конкретный
столбец конкретной таблицы. Для того, чтобы получить приблизительный список, можно
воспользоваться следующим запросом:

SELECT SCHEMA_NAME(objects.schema_id), OBJECT_NAME(objects.object_id)
    FROM sys.sql_expression_dependencies
        JOIN sys.objects
            ON objects.object_id = sql_expression_dependencies.referencing_id
                AND objects.type IN ('P')
    WHERE sql_expression_dependencies.referenced_id = OBJECT_ID('MyTableName')
        AND OBJECT_DEFINITION(sql_expression_dependencies.referencing_id) LIKE '%MyColumnName%'


Соответственно, MyTableName - это имя таблицы, MyColumnName - это имя столбца этой
таблицей.


Ответ 2

Способа, который бы давал 100% результат, мне не известно. И я склоняюсь к мысли,
что скорее всего его не существует (хотя бы из-за deferred name resolution и возможных
dynamic sql вставок).



Можно попробовать посмотреть что выдаст sys.dm_sql_referenced_entities:

declare @columnName sysname = 'Column';

select distinct
    o.type,
    objName = quotename(s.name) + '.' + quotename(o.name)
from sys.objects o
    join sys.schemas s on s.schema_id = o.schema_id
    cross apply sys.dm_sql_referenced_entities(quotename(s.name) + '.' + quotename(o.name),
'OBJECT') ref
where
    o.type in ('FN', 'TF', 'IF', 'TR', 'P')
    and ref.referenced_class_desc = 'OBJECT_OR_COLUMN'
    and ref.referenced_minor_name = @columnName
order by 2;




Также можно посмотреть, что есть в sys.sql_dependencies и sys.sql_expression_dependencies:

declare @columnName sysname = 'Column';

select
    o.type,
    objName = quotename(s.name) + '.' + quotename(o.name)
from sys.sql_dependencies d
    join sys.objects o on o.object_id = d.object_id
    join sys.schemas s on s.schema_id = o.schema_id
    join sys.objects ref_o on ref_o.object_id = d.referenced_major_id
    join sys.columns c on c.object_id = ref_o.object_id and c.column_id = d.referenced_minor_id
where
    c.name = @columnName
    and o.type in ('FN', 'TF', 'IF', 'TR', 'P')
order by 2;


select distinct
    o.type,
    objName = quotename(s.name) + '.' + quotename(o.name)
from sys.sql_expression_dependencies d
    join sys.objects o on o.object_id = d.referencing_id
    join sys.schemas s on s.schema_id = o.schema_id
    join sys.objects ref_o on ref_o.object_id = d.referenced_id
    join sys.columns c on
        c.object_id = ref_o.object_id
        and (
            c.column_id = d.referenced_minor_id and c.name = @columnName
            or d.referenced_minor_id = 0 and charindex(@columnName, object_definition(o.object_id),
1) > 0
        )
where
    d.referenced_class_desc = 'OBJECT_OR_COLUMN'
    and o.type in ('FN', 'TF', 'IF', 'TR', 'P')
order by 2




Плюс можно поискать напрямую по скрипту объекта (если он не encrypted):

declare @columnName sysname = 'Column';

select
    o.type,
    objName = quotename(s.name) + '.' + quotename(o.name)
from sys.objects o
    join sys.schemas s on s.schema_id = o.schema_id
where
    charindex(@columnName, object_definition(o.object_id), 1) > 0
    and o.type in ('FN', 'TF', 'IF', 'TR', 'P')
order by 2;


Такой способ может дать ложные вхождения. Так, например, задав для поиска "Column"
могут быть также найдены объекты содержащие "OtherColumn", или содержащие "Column"
в строках или комментариях. Однако такой способ может пригодиться для нахождения ссылок
на столбцы в динамических подзапросах.