查找引用某个表中特定列的所有存储过程 [英] Find all stored procedures that reference a specific column in some table

问题描述

我有一个意外更改的表中的值。所涉及的列是 CreatedDate ：这是在我的项目被创建时设置的，但是被存储过程改变了。

我可以写一些类型的 SELECT 语句来获取从我的表中引用此列的所有过程名称？

解决方案

一个选项是创建一个脚本文件。

右键点击数据库 - >任务 - >生成脚本 然后你可以选择所有的存储过程，并用所有的sps生成脚本。所以你可以找到那里的参考。

或

   -  - 在所有对象中搜索
 SELECT OBJECT_NAME（OBJECT_ID），
定义
 FROM sys.sql_modules 
 WHERE定义LIKE'％'+'CreatedDate'+'％'
 GO 
 $ b $  - 在存储过程中搜索仅
 SELECT DISTINCT OBJECT_NAME（OBJECT_ID），
 object_definition（OBJECT_ID）
 FROM sys.procedures 
 WHERE object_definition OBJECT_ID）LIKE'％'+'CreatedDate'+'％'
 GO 
  

a href =http://blog.sqlauthority.com/2012/07/19/sql-server-find-column-used-in-stored-procedure-search-stored-procedure-for-column-name-part- 2 /rel =noreferrer> SQL SERVER - 在存储过程中使用查找列 - 为列名搜索存储过程

I have a value in a table that was changed unexpectedly. The column in question is CreatedDate: this is set when my item is created, but it's being changed by a stored procedure.

Could I write some type of SELECT statement to get all the procedure names that reference this column from my table?

解决方案

One option is to create a script file.

Right click on the database -> Tasks -> Generate Scripts

Then you can select all the stored procedures and generate the script with all the sps. So you can find the reference from there.

Or

-- Search in All Objects
SELECT OBJECT_NAME(OBJECT_ID),
definition
FROM sys.sql_modules
WHERE definition LIKE '%' + 'CreatedDate' + '%'
GO

-- Search in Stored Procedure Only
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE '%' + 'CreatedDate' + '%'
GO

Source SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name

这篇关于查找引用某个表中特定列的所有存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！