如何在存储过程中查找数据表列引用

I have changed a column name in a table in my SQL Server 2005 database. I also have a rather large collection of stored procedures that may or may not be referencing that column. Is there a way to find what stored procedures are referencing that column without actually going through each stored procedure and search for it manually? Is there a way to automatically find what stored procedures will now break or something? I do not have access to such SQL refactoring tools like RedGate's SQL Refactor.

我在SQL Server 2005数据库的表中更改了列名。我还有一个相当大的存储过程集合,可能会也可能不会引用该列。有没有办法找到哪些存储过程引用该列而不实际遍历每个存储过程并手动搜索它?有没有办法自动查找哪些存储过程现在会破坏什么?我无法访问像RedGate的SQL Refactor这样的SQL重构工具。

Thanks!

4 个解决方案

#1

Here is something that might help you. I have created two user stored procs that do something similar to what you are asking.

这可能对你有所帮助。我创建了两个用户存储过程,它们可以执行类似于您所要求的操作。

usp_depends2 - an extended version of sp_depends

usp_depends2 - sp_depends的扩展版本
usp_FindReferences - this one uses usp_depends2 to find all references for a column in a table (I think this is what you need)

usp_FindReferences - 这个使用usp_depends2来查找表中列的所有引用(我认为这是你需要的)

    /****** Object:  StoredProcedure [dbo].[usp_depends2]    Script Date: 11/18/2009 11:55:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


create procedure [dbo].[usp_depends2]  --- 1996/08/09 16:51
@objname nvarchar(776)  /* the object we want to check */
as
declare @objid int   /* the id of the object we want */
declare @found_some bit   /* flag for dependencies found */
declare @dbname sysname

/*
**  Make sure the @objname is local to the current database.
*/


DECLARE @sp_depends_xref table (
   reftype char(2)
 , dep_name nvarchar(256)
 , type   char(16)
 , updated char(7)
 , selected char(8)
 , [column] nvarchar(128))


select @dbname = parsename(@objname,3)

if @dbname is not null and @dbname <> db_name()
 begin
  raiserror(15250,-1,-1)
  return (1)
 end

/*
**  See if @objname exists.
*/
select @objid = object_id(@objname)
if @objid is null
 begin
  select @dbname = db_name()
  raiserror(15009,-1,-1,@objname,@dbname)
  return (1)
 end

/*
**  Initialize @found_some to indicate that we haven't seen any dependencies.
*/
select @found_some = 0

set nocount on

/*
**  Print out the particulars about the local dependencies.
*/
if exists (select *
  from sysdepends
   where id = @objid)
begin
 raiserror(15459,-1,-1)
 INSERT INTO @sp_depends_xref (
    refType
  ,  dep_name
  , type
  , updated
  , selected
  , [column])
 select   'TO', 'name' = (s6.name+ '.' + o1.name),
    type = substring(v2.name, 5, 16),
    updated = substring(u4.name, 1, 7),
    selected = substring(w5.name, 1, 8),
             'column' = col_name(d3.depid, d3.depnumber)
  from  sysobjects  o1
   ,master.dbo.spt_values v2
   ,sysdepends  d3
   ,master.dbo.spt_values u4
   ,master.dbo.spt_values w5 --11667
   ,sysusers  s6
  where  o1.id = d3.depid
  and  o1.xtype = substring(v2.name,1,2) collate database_default and v2.type = 'O9T'
  and  u4.type = 'B' and u4.number = d3.resultobj
  and  w5.type = 'B' and w5.number = d3.readobj|d3.selall
  and  d3.id = @objid
  and  o1.uid = s6.uid
  and deptype < 2

 select @found_some = 1
end

/*
**  Now check for things that depend on the object.
*/
if exists (select *
  from sysdepends
   where depid = @objid)
begin
  raiserror(15460,-1,-1)
 INSERT INTO @sp_depends_xref (
    RefType
  , dep_name
  , type)
 select distinct 'BY', 'name' = (s.name + '.' + o.name),
  type = substring(v.name, 5, 16)
   from sysobjects o, master.dbo.spt_values v, sysdepends d,
    sysusers s
   where o.id = d.id
    and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
    and d.depid = @objid
    and o.uid = s.uid
    and deptype < 2

 select @found_some = 1
end

/*
**  Did we find anything in sysdepends?
*/
if @found_some = 0
 raiserror(15461,-1,-1)

 SELECT
    reftype
  , dep_name
  , type
  , updated
  , selected
  , [column]
 FROM @sp_depends_xref


set nocount off


return (0) -- sp_depends
GO

    /****** Object:  StoredProcedure [dbo].[usp_FindReferences]    Script Date: 11/18/2009 11:55:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_FindReferences] 
 -- Add the parameters for the stored procedure here
 @tablename nvarchar(500) = 0, 
 @colname nvarchar(500) = 0
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

 create table #tempTableDependencies
 (
  reftype nvarchar(20),
  dep_name nvarchar(500),
  type nvarchar(500),
  updated nvarchar(500),
  selected nvarchar(500),
  col nvarchar(500)
 )

 insert into #tempTableDependencies execute usp_depends2 @tablename

 create table #tempDependencies
 (
  reftype nvarchar(20),
  dep_name nvarchar(500),
  type nvarchar(500),
  updated nvarchar(500),
  selected nvarchar(500),
  col nvarchar(500)
 )

 declare @tempFilteredDependencies table
 (
  objectname nvarchar(500),
  reftype nvarchar(20),
  dep_name nvarchar(500),
  type nvarchar(500),
  updated nvarchar(500),
  selected nvarchar(500),
  col nvarchar(500)
 )

 DECLARE @loopcounter INT
 select @loopcounter = COUNT(*) FROM #tempTableDependencies

 DECLARE @dependencyname nvarchar(500)

 WHILE @loopcounter > 0 
 BEGIN
  SELECT TOP 1 @dependencyname = dep_name FROM #tempTableDependencies 
  print 'loop_counter = ' + CAST(@loopcounter as nvarchar(20))
  print 'dependency = ' + @dependencyname

  insert into #tempDependencies execute usp_depends2 @dependencyname
  insert into @tempFilteredDependencies select @dependencyname as objectname, * from #tempDependencies where col = @colname and dep_name like '%' + @tablename
  delete from #tempDependencies
  delete from #tempTableDependencies where dep_name = @dependencyname

  SET @loopcounter = @loopcounter - 1
 END

 select * from @tempFilteredDependencies

 drop table #tempDependencies
 drop table #tempTableDependencies

END

GO

#2

The stock answer is "sp_depends", but in SQL 7.0 and 2000 it was not guaranteed to be accurate (that is, up to date). I don't know if they've addressed this in SQL 2005 or 2008, as I rolled my own work-around quite some time ago. This doesn't do exaclty what you want, but it can get you there sooner than otherwise

库存答案是“sp_depends”,但在SQL 7.0和2000中,它不能保证准确(即最新)。我不知道他们是否已经在SQL 2005或2008中解决了这个问题,因为我在很久以前开始自己的工作。这并不是你想要的,但它可以比你更快到达那里

It's based on this query:

它基于这个查询:

DECLARE @SearchText varchar(100)

SET @SearchText = 'ProductId'

SELECT
   schema_name(ob.schema_id)  SchemaName
  ,ob.name
  ,ob.type_desc
  ,len(mo.definition) CodeLength
  ,mo.definition
 from sys.sql_modules mo
  inner join .sys.objects ob
   on ob.object_id = mo.object_id
 where mo.definition like '%' + @SearchText + '%'
 order by
   case schema_name(ob.schema_id)
     when 'dbo' then 'A'
     else 'B' + str(ob.schema_id, 10)
   end
  ,ob.type_desc
  ,ob.name

This will search through all the text-type database objects stored in sys.objects that have data/definitions in sys.modules. This covers stored procedures, functions, and views, and might also covers triggers and some constraints (I don't know one way or the other). It does not track synonyms, their definitions are stored in their own system table.

这将搜索存储在sys.objects中的所有文本类型数据库对象,这些对象在sys.modules中具有数据/定义。这包括存储过程,函数和视图,也可能包括触发器和一些约束(我不知道这种或那种方式)。它不跟踪同义词,它们的定义存储在自己的系统表中。

The results will return a list of all such objects that contain the specified string. It in no way tries to evaluate the context in which the string appears--if it's a table, column, variable, or comment, it's a hit and gets included. This means your mileage will vary depending on how unique the string your searching for is... but on the flip side, you can look for more than just columns with this.

结果将返回包含指定字符串的所有此类对象的列表。它绝不会试图评估字符串出现的上下文 - 如果它是一个表,列,变量或注释,它是一个命中并被包含在内。这意味着您的里程将根据您搜索的字符串的独特程度而有所不同......但另一方面,您可以查找不仅仅是列的列表。

Returned columns are:

返回的列是:

SchemaName
Name (of object containing the string)

名称(包含字符串的对象)

type_desc (as from sys.objects)

type_desc(来自sys.objects)

CodeLength (how big is the chunk o' code the string was found in)

CodeLength(找到字符串的代码块有多大)

definition (a copy of said chunk of code. Hmm, I never use this, maybe I should take it out?)

定义(代码块的副本。嗯,我从不使用它,也许我应该把它拿出来?)

#3

Here is what I found: Nir method is best as it finds the real dependencies (not by the text of the stored procedure), though it will not work properly if you dont refresh sql module. nip and Philip solutions are the same - find a string in the stored procedure code, it will not work properly if you have the same column name in several tables.

这是我发现的:Nir方法是最好的,因为它找到真正的依赖项(不是通过存储过程的文本),但如果你不刷新sql模块它将无法正常工作。 nip和Philip解决方案是相同的 - 在存储过程代码中查找字符串,如果在多个表中具有相同的列名,它将无法正常工作。

So I decided to use Nir's solution and add my script inside usp_FindReferences to refresh sql modules. Here is my final script:

所以我决定使用Nir的解决方案并在usp_FindReferences中添加我的脚本来刷新sql模块。这是我的最终剧本:

USE [Cetgroups3]
GO
/****** Object:  StoredProcedure [dbo].[usp_depends2]    Script Date: 03/16/2011 14:38:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[usp_depends2]  --- 1996/08/09 16:51 
  @objname nvarchar(776)  /* the object we want to check */ 
as 
  declare @objid int   /* the id of the object we want */ 
  declare @found_some bit   /* flag for dependencies found */ 
  declare @dbname sysname  /* **  Make sure the @objname is local to the current database. */   
  DECLARE @sp_depends_xref table (
   reftype char(2), 
dep_name nvarchar(256), 
   type char(16), 
   updated char(7), 
   selected char(8),
   [column] nvarchar(128))   
  select @dbname = parsename(@objname,3)  
  if @dbname is not null and @dbname <> db_name()  
  begin   
   raiserror(15250,-1,-1)   
   return (1)  
  end  
  /* **  See if @objname exists. */ 
  select @objid = object_id(@objname) 
  if @objid is null  
  begin   
   select @dbname = db_name()   
   raiserror(15009,-1,-1,@objname,@dbname)   
return (1)  
end  
  /* **  Initialize @found_some to indicate that we haven't seen any dependencies. */ 
  select @found_some = 0  
  set nocount on  
  /* **  Print out the particulars about the local dependencies. */ 
  if exists (select *   from sysdepends    where id = @objid) 
 begin  
   raiserror(15459,-1,-1)  
   INSERT INTO @sp_depends_xref (refType, dep_name   , type, updated, selected, [column])  
select   'TO', 'name' = (s6.name+ '.' + o1.name), type = substring(v2.name, 5, 16), 
  updated = substring(u4.name, 1, 7), selected = substring(w5.name, 1,8),


         'column' = col_name(d3.depid, d3.depnumber)   
from  sysobjects  o1,
      master.dbo.spt_values v2,
      sysdepends  d3,
      master.dbo.spt_values u4,
      master.dbo.spt_values w5, --11667    
      sysusers  s6   
where o1.id = d3.depid   
      and  o1.xtype = substring(v2.name,1,2) collate database_default 
      and v2.type = 'O9T'   
      and  u4.type = 'B' 
      and u4.number = d3.resultobj   
      and  w5.type = 'B' 
      and w5.number = d3.readobj|d3.selall   
      and  d3.id = @objid   
      and  o1.uid = s6.uid   
      and deptype < 2   

select @found_some = 1

end
/* ** Now check for things that depend on the object. */ if exists (select * from sysdepends where depid = @objid) begin
raiserror(15460,-1,-1)
INSERT INTO @sp_depends_xref (RefType, dep_name, type)
select distinct 'BY', 'name' = (s.name + '.' + o.name), type = substring(v.name, 5, 16)
from sysobjects o, master.dbo.spt_values v, sysdepends d,
sysusers s
where o.id = d.id
and o.xtype = substring(v.name,1,2) collate database_default and v.type = 'O9T'
and d.depid = @objid
and o.uid = s.uid
and deptype < 2
select @found_some = 1 end
/* ** Did we find anything in sysdepends? */ if @found_some = 0
raiserror(15461,-1,-1)

结束/ * **现在检查依赖于对象的东西。 * / if exists(select * from sysdepends where depid = @objid)begin raiserror(15460,-1,-1)INSERT INTO @sp_depends_xref(RefType,dep_name,type)select distinct'BY','name'=(s。 name +'。'+ o.name),type = substring(v.name,5,16)来自sysobjects o,master.dbo.spt_values v,sysdepends d,sysusers s其中o.id = d.id和o。 xtype = substring(v.name,1,2)collate database_default和v.type ='O9T'和d.depid = @objid and o.uid = s.uid and deptype <2 select @found_some = 1 end / * * *我们在sysdepends中找到了什么吗? * / if @found_some = 0 raiserror(15461,-1,-1)

SELECT reftype, dep_name, type, updated, selected, [column]
FROM @sp_depends_xref

SELECT reftype,dep_name,type,updated,selected,[column] FROM @sp_depends_xref

set nocount off
return (0) -- sp_depends

set nocount off return(0) - sp_depends

/** Object: StoredProcedure [dbo].[usp_FindReferences] Script Date: 11/18/2009 11:55:05 **/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [dbo].[usp_FindReferences]
-- Add the parameters for the stored procedure here
@tablename nvarchar(500) = 0,
@colname nvarchar(500) = 0 AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.
SET NOCOUNT ON;
-- Before starting - refresh sql module declare @sql as nvarchar(max); set @sql = ''; select @sql = @sql + N'begin try exec sp_refreshsqlmodule @name = ''' + CAST(name as nvarchar(4000)) + N'''; end try begin catch print ''Failed to refresh ' + CAST(name as nvarchar(4000)) + N': '' + ERROR_MESSAGE(); IF XACT_STATE() = -1 ROLLBACK; end catch; ' from sys.sysobjects where type in ('P', 'V', 'TF', 'FN');-- order by name; exec sp_executesql @sql; -- Now we can proceed with fresh data create table #tempTableDependencies (
reftype nvarchar(20),
dep_name nvarchar(500),
type nvarchar(500),
updated nvarchar(500),
selected nvarchar(500),
col nvarchar(500))

/ **对象:StoredProcedure [dbo]。[usp_FindReferences]脚本日期:11/18/2009 11:55:05 ** / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO创建程序[dbo]。[usp_FindReferences] - 添加此处存储过程的参数@tablename nvarchar(500)= 0,@ colname nvarchar(500)= 0 AS BEGIN - 添加SET NOCOUNT ON以防止额外的结果集 - 干扰SELECT语句。设置NOCOUNT ON; - 在开始之前 - 刷新sql模块将@sql声明为nvarchar(max);设置@sql =''; select @sql = @sql + N'begin try exec sp_refreshsqlmodule @name ='''+ CAST(name as nvarchar(4000))+ N''';结束尝试开始捕获打印''无法刷新'+ CAST(名称为nvarchar(4000))+ N':''+ ERROR_MESSAGE();如果XACT_STATE()= -1 ROLLBACK;结束捕获; '来自sys.sysobjects,其中输入('P','V','TF','FN'); - 按名称排序; exec sp_executesql @sql; - 现在我们可以继续使用新数据创建表#tempTableDependencies(reftype nvarchar(20),dep_name nvarchar(500),type nvarchar(500),updated nvarchar(500),selected nvarchar(500),col nvarchar(500))

insert into #tempTableDependencies execute usp_depends2 @tablename

插入#tempTableDependencies执行usp_depends2 @tablename

create table #tempDependencies (
reftype nvarchar(20),
dep_name nvarchar(500),
type nvarchar(500),
updated nvarchar(500),
selected nvarchar(500),
col nvarchar(500))

create table #tempDependencies(reftype nvarchar(20),dep_name nvarchar(500),type nvarchar(500),updated nvarchar(500),selected nvarchar(500),col nvarchar(500))

declare @tempFilteredDependencies table (
objectname nvarchar(500),
reftype nvarchar(20),
dep_name nvarchar(500),
type nvarchar(500),
updated nvarchar(500),
selected nvarchar(500),
col nvarchar(500))

声明@tempFilteredDependencies表(objectname nvarchar(500),reftype nvarchar(20),dep_name nvarchar(500),type nvarchar(500),updated nvarchar(500),selected nvarchar(500),col nvarchar(500))

DECLARE @loopcounter INT
select @loopcounter = COUNT(*) FROM #tempTableDependencies
DECLARE @dependencyname nvarchar(500)
WHILE @loopcounter > 0
BEGIN
SELECT TOP 1 @dependencyname = dep_name FROM #tempTableDependencies
print 'loop_counter = ' + CAST(@loopcounter as nvarchar(20))
print 'dependency = ' + @dependencyname
insert into #tempDependencies execute usp_depends2 @dependencyname

DECLARE @loopcounter INT select @loopcounter = COUNT(*)FROM #tempTableDependencies DECLARE @dependencyname nvarchar(500)WHILE @loopcounter> 0 BEGIN SELECT TOP 1 @dependdencyname = dep_name FROM #tempTableDependencies print'loop_counter ='+ CAST(@loopcounter as nvarchar (20))print'dependent ='+ @dependencyname insert into #tempDependencies执行usp_depends2 @dependencyname

insert into @tempFilteredDependencies 
select @dependencyname as objectname, * 
from #tempDependencies 
where col = @colname 
      and dep_name like '%' + @tablename   

delete from #tempDependencies   
delete from #tempTableDependencies 
where dep_name = @dependencyname    

SET @loopcounter = @loopcounter - 1

END

select * from @tempFilteredDependencies order by objectname drop table #tempDependencies
drop table #tempTableDependencies
END
GO

select * from @tempFilteredDependencies order by objectname drop table #tempDependencies drop table #tempTableDependencies END GO

#4

Something like that should do the trick

这样的事情应该可以解决问题

SELECT so.name 
    FROM sys.sysobjects so
    JOIN sys.syscomments sc ON so.id = sc.id 
    WHERE sc.text LIKE '%ColumnName%'
    AND so.type = 'P'

#1