如何找到在存储过程中的数据表的列引用 [英] How to find data table column reference in stored procedures

问题描述

我在我的SQL Server 2005数据库表中的改列名。我也有一个相当大的集合，可能或可能不被引用该列的存储过程。有没有办法来查找存储过程所引用的列没有实际经历的每个存储过程和手动搜索呢？有没有一种方法能够自动查找存储过程现在会打破什么？我没有获得这样的SQL重构工具，如展鹏的SQL重构。

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.

谢谢！

推荐答案

下面是一些可以帮助你。我创建了两个用户存储的特效做类似的向你所要求的东西。

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

1. usp_depends2 - sp_depends可扩展版本

1. usp_depends2 - an extended version of sp_depends

usp_FindReferences - 这一次使用usp_depends2发现在一个表中的列的所有引用（我认为这是你所需要的）

usp_FindReferences - this one uses usp_depends2 to find all references for a column in a table (I think this is what you need)

---

    /****** 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

---

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