В SQL Server, как я могу найти везде столбец?

Ответ 1

Этот ответ также обсуждается с использованием sys.sql_dependencies. Вам также может потребоваться использовать sp_refreshsqlmodule как здесь, где я упоминаю.

Ответ 2

Предупреждение: Несмотря на то, что это метод текстового поиска, script, который я собираюсь предоставить, сохранил мне много и много часов. Он ищет внутри:

• скалярные функции
• табличные функции
• хранимые процедуры
• вид
• Триггеры

Мне нужно было указать сортировку, чтобы она работала для меня.

SELECT sys.objects.object_id, sys.schemas.name AS [Schema], sys.objects.name AS Object_Name, sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    sys.sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%{Column Name}%' ESCAPE '\'
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

Вывод выглядит следующим образом:

Обновить. Если вам нужно найти определенную таблицу, SP и т.д., вы можете использовать более специализированный запрос:

DECLARE @SCHEMA_NAME VARCHAR(100) = 'dbo';
DECLARE @OBJECT_NAME  VARCHAR(100) = 'MY_OBJECT';

SELECT
    sys.objects.object_id,
    sys.schemas.name AS [Schema], 
    sys.objects.name AS Object_Name, 
    sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    (
           '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'[email protected]_NAME+'.'[email protected]_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['[email protected]_NAME+'\].'[email protected]_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'[email protected]_NAME+'.\['[email protected]_NAME+'\][^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['[email protected]_NAME+'\].\['[email protected]_NAME+'\][^a-z_]%' ESCAPE '\'
    )
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

P.S.. Оба запроса также просматривают внутри комментариев.

Ответ 3

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

SELECT 
R.TABLE_NAME,R.COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    ON U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    AND U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    AND U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE U.COLUMN_NAME = 'your column name'
  AND U.TABLE_NAME = 'your table name'

P.S. Столбец должен находиться в таблице

Ответ 4

Лучшие способы сделать это описаны в в этой статье.

Образец:

SELECT OBJECT_NAME (referencing_id),
              referenced_database_name, 
       referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE OBJECT_NAME(d.referenced_id) = 'TableName'
      AND OBJECT_DEFINITION (referencing_id)  LIKE '%ColumnName%'
ORDER BY OBJECT_NAME(referencing_id);

Ответ 5

Все фигуры здесь, поэтому я собрал их в один пакет. Получите таблицы из ответа Luv, а остальные из Поиск ссылок столбцов в SQL Server. Ничего из этого не принадлежит мне, я просто экономлю вам небольшую вырезку и вставку.

if exists (select * from dbo.sysobjects
 where id = object_id(N'[dbo].[usp_FindColumnUsage]')
  and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_FindColumnUsage]
go

create procedure [dbo].[usp_FindColumnUsage]
 @vcTableName varchar(100),
 @vcColumnName varchar(100)
as
/************************************************************************************************
DESCRIPTION: Creates prinatable report of all stored procedures, views, triggers
  and user-defined functions that reference  the
  table/column passed into the proc.

PARAMETERS:
  @vcTableName - table containing searched column
  @vcColumnName - column being searched for
REMARKS:
  To print the output of this report in Query Analyzer/Management
  Studio select the execute mode to be file and you will
  be prompted for a file name to save as. Alternately
  you can select the execute mode to be text, run the query, set
  the focus on the results pane and then select File/Save from
  the menu.

  This procedure must be installed in the database where it will
  be run due to it use of database system tables.

USAGE:

  usp_FindColumnUsage 'jct_contract_element_card_sigs', 'contract_element_id'

AUTHOR: Karen Gayda

DATE: 07/19/2007

MODIFICATION HISTORY:
WHO  DATE  DESCRIPTION
---  ---------- -------------------------------------------
*************************************************************************************************/
set nocount on

print ''
print 'REPORT FOR DEPENDENCIES FOR TABLE/COLUMN:'
print '-----------------------------------------'
print  @vcTableName + '.' [email protected]

select
TableName = R.TABLE_NAME, ColumnName = R.COLUMN_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE R
    on R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    and R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    and R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
where U.TABLE_NAME = @vcTableName
  and U.COLUMN_NAME = @vcColumnName
order by U.TABLE_NAME, U.COLUMN_NAME

print ''
print ''
print 'STORED PROCEDURES:'
print ''

select distinct  substring(o.NAME,1,60) as [procedure Name]
  from sysobjects o
  inner join syscomments c
   on o.ID = c.ID
  where  o.XTYPE = 'P'
   and c.Text like '%' + @vcColumnName + '%' + @vcTableName + '%'


 order by  [procedure Name]
print cast(@@ROWCOUNT as varchar(5)) + ' dependent stored procedures for column "' + @vcTableName + '.' [email protected] +  '".'



print''
print''
print 'VIEWS:'
print''
select distinct  substring(o.NAME,1,60) as [view Name]
  from sysobjects o
  inner join syscomments c
   on o.ID = c.ID
  where  o.XTYPE = 'V'
   and c.Text like '%' + @vcColumnName + '%' + @vcTableName + '%'


 order by  [view Name]
print cast(@@ROWCOUNT as varchar(5)) + ' dependent views for column "' + @vcTableName + '.' [email protected] +  '".'

print ''
print ''
print 'FUNCTIONS:'
print ''

select distinct  substring(o.NAME,1,60) as [function Name],
  case when o.XTYPE = 'FN' then 'Scalar'
   when o.XTYPE = 'IF' then 'Inline'
   when o.XTYPE = 'TF' then 'Table'
   else '?'
  end
  as [function type]
  from sysobjects o
  inner join syscomments c
   on o.ID = c.ID
  where  o.XTYPE in ('FN','IF','TF')
   and c.Text like '%' + @vcColumnName + '%' + @vcTableName + '%'


 order by  [function Name]
print cast(@@ROWCOUNT as varchar(5)) + ' dependent functions for column "' + @vcTableName + '.' [email protected] +  '".'

print''
print''
print 'TRIGGERS:'
print''

select distinct  substring(o.NAME,1,60) as [trigger Name]
  from sysobjects o
  inner join syscomments c
   on o.ID = c.ID
  where  o.XTYPE = 'TR'
   and c.Text like '%' + @vcColumnName + '%' + @vcTableName + '%'


 order by  [trigger Name]
print cast(@@ROWCOUNT as varchar(5)) + ' dependent triggers for column "' + @vcTableName + '.' [email protected] +  '".'

go

exec usp_FindColumnUsage 'MyTable', 'MyColumn'

Ответ 6

Как сказал Лув, это старый вопрос, но я нашел еще два решения, которые могут быть полезны.

Я использую системный объект sys.dm_sql_referenced_entities, который находит все ссылочные объекты и столбцы в указанном объекте. Вы можете использовать следующий запрос:

SELECT DISTINCT 
referenced_schema_name AS SchemaName, 
referenced_entity_name AS TableName, 
referenced_minor_name  AS ColumnName
FROM sys.dm_sql_referenced_entities ('yourrefencingobject', 'OBJECT'); 
GO

Что дает следующий результат:

Нижняя сторона этого объекта состоит в том, что вам нужно указать ссылочный объект.

Или выполните поиск типа:

SELECT DISTINCT object_name(id) 
FROM AdventureWorks2012.dbo.syscomments (nolock) 
WHERE text like '%BusinessEntityID%'

Что дает следующий результат:

Я также нашел следующий SP, который вы могли бы использовать в в этой статье, но не проверили его правильно:

> DECLARE    @string varchar(1000),   @ShowReferences char(1)
> 
> SET @string = 'Person.Person.BusinessEntityID' --> searchstring
> 
> SET @ShowReferences = 'N'
> /****************************************************************************/ /*                                                                    
> */ /* TITLE:   sp_FindReferences                                               */ /*                                                                          */ /* DATE:    18 February, 2004                                               */ /*                                                                          */ /* AUTHOR:  WILLIAM MCEVOY                                                  */ /*                                                                          */ /****************************************************************************/ /*                                                                    
> */ /* DESCRIPTION:  SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */ /*                                                       
> */ /****************************************************************************/ set nocount on
> 
> declare @errnum         int         ,
>         @errors         char(1)     ,
>         @rowcnt         int         ,
>         @output         varchar(255)
> 
> select  @errnum         = 0         ,
>         @errors         = 'N'       ,
>         @rowcnt         = 0         ,
>         @output         = ''        
> 
> /****************************************************************************/ /* INPUT DATA VALIDATION                                              
> */ /****************************************************************************/
> 
> 
> /****************************************************************************/ /* M A I N   P R O C E S S I N G                                      
> */ /****************************************************************************/
> 
> -- Create temp table to hold results DECLARE @Results table (   Name        varchar(55),   Type        varchar(12),   DateCreated datetime,  
> ProcLine    varchar(4000) )
> 
> 
> IF (@ShowReferences = 'N') BEGIN   insert into @Results   select
> distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO
>     join syscomments SC on SC.id = SO.id    where SC.text like '%' + @string + '%'   union   select distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO    where SO.name like '%' + @string + '%'   union   select distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO
>     join syscolumns SC on SC.id = SO.ID    where SC.name like '%' + @string + '%'    order by 2,1 END ELSE BEGIN   insert into @Results  
> select 
>          'Name'      = convert(varchar(55),SO.name),
>          'Type'      = SO.type,
>          crdate,
>          'Proc Line' = text
>     from sysobjects  SO
>     join syscomments SC on SC.id = SO.id    where SC.text like '%' + @string + '%'   union   select 
>          'Name'      = convert(varchar(55),SO.name),
>          'Type'      = SO.type,
>          crdate,
>          'Proc Line' = ''
>     from sysobjects  SO    where SO.name like '%' + @string + '%'   union   select 
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          'Proc Line' = ''
>     from sysobjects  SO
>     join syscolumns SC on SC.id = SO.ID    where SC.name like '%' + @string + '%'    order by 2,1 END
> 
> IF (@ShowReferences = 'N') BEGIN   select Name,
>          'Type' = Case (Type)
>                     when 'P'  then 'Procedure'
>                     when 'TR' then 'Trigger'
>                     when 'X'  then 'Xtended Proc'
>                     when 'U'  then 'Table'
>                     when 'C'  then 'Check Constraint'
>                     when 'D'  then 'Default'
>                     when 'F'  then 'Foreign Key'
>                     when 'K'  then 'Primary Key'
>                     when 'V'  then 'View'
>                     else Type
>                   end,
>          DateCreated
>     from @Results
>     order by 2,1 END ELSE BEGIN   select Name,
>          'Type' = Case (Type)
>                     when 'P'  then 'Procedure'
>                     when 'TR' then 'Trigger'
>                     when 'X'  then 'Xtended Proc'
>                     when 'U'  then 'Table'
>                     when 'C'  then 'Check Constraint'
>                     when 'D'  then 'Default'
>                     when 'F'  then 'Foreign Key'
>                     when 'K'  then 'Primary Key'
>                     when 'V'  then 'View'
>                     else Type
>                   end,
>          DateCreated,
>          ProcLine
>     from @Results
>     order by 2,1 END

Надеюсь, что это поможет

Ответ 7

Привет. Хотя это старый пост, мне удалось совместить несколько советов сверху, получив что-то вроде этого ниже, что помогло мне. Причина была в том, что я создал этот столбец, в котором было много таблиц, поэтому он не дал мне ясного вывода.

SELECT 
      SCHEMA_NAME(schema_id)+'.'+[name] as objectname
      ,type_desc
     ,referenced_schema_name AS SchemaName
,referenced_entity_name AS TableName
,referenced_minor_name  AS ColumnName
  FROM [sys].[all_objects] ob cross apply sys.dm_sql_referenced_entities ( SCHEMA_NAME(schema_id)+'.'+[name], 'OBJECT') e

  where is_ms_shipped = 0 and type_desc in ('AGGREGATE_FUNCTION'
,'SQL_SCALAR_FUNCTION'
,'SQL_INLINE_TABLE_VALUED_FUNCTION'
,'SQL_STORED_PROCEDURE'
,'SQL_TABLE_VALUED_FUNCTION'
,'SQL_TRIGGER'
,'VIEW')
and name !='sp_upgraddiagrams'
and referenced_entity_name  = 'table name'
and referenced_minor_name = 'columnname'

Ответ 8

Я попробовал этот запрос, и это кажется прекрасным:

select 
obj.type REFERENCING_OBJECT_TYPE
 ,SCHEMA_NAME(obj.schema_id) REFERENCING_OBJECT_SCHEMA
 ,obj.name                  REFERENCING_OBJECT_NAME
from sysdepends x
INNER JOIN sys.objects obj ON x.id  = obj.object_id
where depid = object_id('yourSchema.yourTable')
and col_name(depid, depnumber) = 'yourColumn'

Ответ 9

Я бы хотел избежать использования sys.sql_dependencies, потому что эта функция будет удалена в будущей версии Microsoft SQL Server. Я также не могу использовать функцию OBJECT_DEFINITION, потому что все мои объекты зашифрованы. Поэтому я придумал следующий запрос, который кажется довольно простым и работает для меня настолько хорошо, что я даже привязал его к функции:

-- =============================================
-- Description: Gets all the stored procedures, functions and triggers referencing the specified column.
-- Example:     SELECT * FROM dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN(N'dbo', N'MY_TABLE', N'MY_COLUMN');
-- =============================================
CREATE FUNCTION dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN 
(
    @SchemaName sysname,
    @TableName sysname,
    @ColumnName sysname
)
RETURNS TABLE
AS 
RETURN

    SELECT R.referencing_schema_name + N'.' + R.referencing_entity_name AS referencing_entity_name
    FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R 
        INNER JOIN sys.objects AS O 
            ON R.referencing_id = O.object_id
    WHERE O.[type] IN ('FN'/*SQL scalar function*/, 'IF'/*SQL inline table-valued function*/, 'TF'/*SQL table-valued-function*/, 'P'/*SQL Stored Procedure*/, 'TR' /*SQL DML trigger*/)
          AND EXISTS(SELECT 1 FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName);

GO

Ответ 10

Здесь небольшая настройка на @alex TV-UDF, чтобы включить также:

/*
    Source: https://stackoverflow.com/a/47775531/852956
    Gets all the stored procedures, functions and triggers referencing the specified column.

    SELECT * FROM Utility.ft_SelectSprocFuncAndTrigrRefs(N'BrakeRotor', N'BrakeRotors', N'BrakeRotorNumber');
*/
CREATE FUNCTION Utility.ft_SelectSprocFuncAndTrigrRefs
(
    @SchemaName sysname,
    @TableName sysname,
    @ColumnName sysname
)
RETURNS TABLE
AS 
RETURN

    SELECT QUOTENAME(R.referencing_schema_name) + N'.' + QUOTENAME(R.referencing_entity_name) AS ReferencingEntityName
    FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R 
        INNER JOIN sys.objects AS O 
            ON R.referencing_id = O.object_id
    WHERE O.[type] IN (
                'FN'/*SQL scalar function*/,
                'IF'/*SQL inline table-valued function*/,
                'TF'/*SQL table-valued-function*/,
                'P'/*SQL Stored Procedure*/,
                'TR' /*SQL DML trigger*/
            )
          AND EXISTS(
                    SELECT 1
                    FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE
                    WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName)

    UNION SELECT QUOTENAME(VIEW_SCHEMA) + N'.' + QUOTENAME(VIEW_NAME) AS ReferencingEntityName
        FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
        WHERE TABLE_SCHEMA = @SchemaName
          AND TABLE_NAME = @TableName
          AND column_name = @ColumnName
GO