SQL 查询以根据列找出存储过程

Query to find out the stored procedure depending on a column

提问by Vaibhav Jain

I have to change the name and the datatype of a column of a table. I have about 150 stored procedures in the database, out of which about 25 refer to the same column. I need a query that can find the name of all the stored procedures which are dependent on this column.

我必须更改表列的名称和数据类型。我在数据库中有大约 150 个存储过程，其中大约 25 个指向同一列。我需要一个查询，该查询可以找到依赖于此列的所有存储过程的名称。

回答by DigCamara

I use this query:

我使用这个查询：

SELECT OBJECT_NAME(M.object_id), M.*
FROM sys.sql_modules M
JOIN sys.procedures P
ON M.object_id = P.object_id
WHERE M.definition LIKE '%blah%'

Obviously you'd have to substitute "blah" for the name of your column.

显然，您必须将“blah”替换为您的列名。

回答by Haris

Try this 1 From Sp

试试这个 1 从 Sp

SELECT Name as [Stored Procedure Name]
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%getdate%' order by Name

From Table

从表

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;

回答by MikeTeeVee

Problem:

As you know there is no way to query what fields are referenced by a function or stored procedure.
The closest we can get is an approximation.
We can tell which tables are referenced and what fields might possibly be referenced by those tables.

For example, if you have "CreatedDate" referenced by the "Person" table and you join to the "Order" table (which also has a "CreatedDate" field), it will find a “false-positive” match to "Order.CreatedDate" when you were only looking for "Person.CreatedDate".

Searching the Textof the object's script for field names is unfortunately the best we can do for now.
The good news is, it won't miss identifying fields that are actually used.
If anything it might pull in more than what wereused (due to shared field names or commented out code).
The onlyexception would be dynamic SQL, as Tables are not linked to Object Scripts if they are embedded in a dynamic string.

Workaround:

问题：

如您所知，无法查询函数或存储过程引用了哪些字段。
我们能得到的最接近的是一个近似值。
我们可以判断哪些表被引用以及这些表可能引用哪些字段。

例如，如果您有“ Person”表引用的“ CreatedDate”，并且您加入“ Order”表（它也有一个“ CreatedDate”字段），它会发现“假阳性”匹配到“ Order”。 CreatedDate“当您只查找“ Person.CreatedDate”时。 搜索文本

不幸的是，对象的字段名称脚本是我们目前所能做的最好的。
好消息是，它不会错过识别实际使用的字段。
如果有什么可以拉比多被用于（共享字段名称或注释掉的代码）。
该唯一的例外是动态SQL，如表没有关联，如果它们被嵌入在动态字符串的对象脚本。

解决方法：

CREATE FUNCTION [dbo].[ft_Schema_Column_Script]
(
    @ScriptName nVarChar(128) = '%',
    @TableName  nVarChar(128) = '%',
    @ColumnName nVarChar(128) = '%'
)
RETURNS TABLE
AS
RETURN
(
    SELECT @@SERVERNAME[ServerName], DB_NAME()[DatabaseName],
           SS.name[ScriptSchemaName], SO.name[ScriptName],
           SO.type_desc[ScriptType], 
           TS.name[TableSchemaName], T.name[TableName], C.name[ColumnName],
           UT.name[ColumnType], C.max_length[MaxLength],
           C.precision[NumericPrecision], C.scale[Scale],
           C.is_nullable[Nullable],
           C.is_identity[IsIdentity],
           C.column_id[Ordinal],
           EP.value[Description]
      FROM sys.sql_modules as M
      JOIN sys.objects     as SO--Script Object.
        ON M.object_id = SO.object_id
      JOIN sys.schemas as SS--Script Schema.
        ON SS.schema_id = SO.schema_id
      JOIN sys.sql_expression_dependencies D
        ON D.referencing_id = SO.object_id
      JOIN sys.tables as T
        ON T.object_id = D.referenced_id
      JOIN sys.schemas as TS--Table Schema.
        ON TS.schema_id = T.schema_id
      JOIN sys.columns as C
        ON C.object_id = T.object_id
      LEFT JOIN sys.types AS UT--Left Join because of user-defined/newer types.
        ON UT.user_type_id = C.user_type_id
      LEFT JOIN sys.extended_properties AS EP
        ON EP.major_id = C.object_id
       AND EP.minor_id = C.column_id
       AND EP.name = 'MS_Description'
     WHERE  T.name LIKE @TableName  ESCAPE '\'
       AND  C.name LIKE @ColumnName ESCAPE '\'
       AND SO.name LIKE @ScriptName ESCAPE '\'
       --Use RegEx to exclude false-posotives by from similar ColumnNames.
       --  (e.g. Ignore the "ModifiedBy" field when matching on "Modified").
       --  Use C.name instead of @ColumnName to further reduce false-positives.
       AND M.definition LIKE (  N'%[ ~`!@#$\%\^&*()+-=\[\]\{}|;'''':",./<>?]'
                              + C.name
                              + N'[ ~`!@#$\%\^&*()+-=\[\]\{}|;'''':",./<>?]%'
                             ) ESCAPE '\'
)
GO

Test:

测试：

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SELECT * FROM dbo.ft_Schema_Column_Script('ScriptName', DEFAULT, DEFAULT) as C
   SELECT * FROM dbo.ft_Schema_Column_Script(DEFAULT, 'TableName',  DEFAULT) as C
   SELECT * FROM dbo.ft_Schema_Column_Script(DEFAULT, DEFAULT, 'ColumnName') as C
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Test the example above to see if it's good enough to meet your needs.

Results:

Example output when running this function searching for the Column Name "Created".


It searches Stored Procedures (Sprocs), User-Defined-Functions (UDF's), Triggers, but not Jobs.

The cool thing is:
This not only searches for Columns referenced by Scripts,
butalso Scripts referenced by Columns (or Tables)!

测试上面的示例，看看它是否足以满足您的需求。

结果：

运行此函数搜索列名称“已创建”时的示例输出。


它搜索 Stored Procedures (Sprocs)、User-Defined-Functions (UDF)、Triggers，但不搜索 Jobs。

很酷的事情是：
这不仅是由脚本引用的列的搜索，
但也被列（或表）引用的脚本！

回答by Chandi

Search in Stored Procedures Only:

仅在存储过程中搜索：

SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'BusinessEntityID' + '%'

回答by Chandi

-- Search in All Objects

-- 在所有对象中搜索

SELECT OBJECT_NAME(OBJECT_ID), definition FROM sys.sql_modules WHERE definition LIKE '%' + 'BusinessEntityID' + '%'

SELECT OBJECT_NAME(OBJECT_ID)，定义来自 sys.sql_modules WHERE 定义 LIKE '%' + 'BusinessEntityID' + '%'