如何找到在存储过程中的数据表中的列引用(How to find data table column

2019-09-18 07:11发布

站内文章 / 后端开发

24 0

女 | 书童

私信

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

谢谢！

Answer 1:

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

usp_depends2 - sp_depends可扩展版本
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

Answer 2:

股票的答案是“sp_depends将”，但在SQL 7.0和2000年它不能保证是准确的（即最新的）。我不知道他们是否已经在SQL 2005或2008年解决这一点，因为我前推出我自己的工作，周围很长一段时间。这并不做exaclty你想要什么，但它可以让你有比其他快

它基于此查询：

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

这将通过存储在sys.objects中有sys.modules中的数据/定义所有的文本类型的数据库搜索对象。这包括存储过程，函数和意见，可能还包括触发器和一些约束（我不知道一个或其他方式）。它不跟踪的同义词，它们的定义存储在自己的系统表。

结果将返回包含指定字符串的所有此类对象的列表。这绝不是试图评估中出现的字符串的上下文 - 如果它是一个表，列，变量或评论，这是一个打击，被包括在内。这意味着你的里程可能会有所不同您如何唯一字符串搜索依赖的是...但在另一面，你可以找比这只是列了。

返回栏目有：

的SchemaName
名（包含字符串对象的）
type_desc（从sys.objects中）
编码长度（有多大块O”串码被发现）
定义（代码所述块的副本。嗯，我从来不使用这个，也许我应该把它拿出来？）

Answer 3:

以下是我发现：尼尔方法是最好的，因为它找到真正的依赖关系（不存储过程的文本），但如果你不刷新SQL模块将无法正常工作。咬和菲利普解决方案是相同的 - 找到存储过程的代码串，它将无法正常如果您在多个表中有相同的列名的工作。

所以我决定用尼尔的解决方案，并添加内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

结束
/ * **现在检查的依赖对象的事情。 * /如果存在（从选择的sysdepends *其中DEPID = @objid）开始
RAISERROR（15460，-1，-1）
INSERT INTO @sp_depends_xref（RefType的，dep_name，类型）
选择不同的 'BY'， '名称'=（s.name + '' + o.name），类型= SUBSTRING（v.name，5，16）
从系统对象O，master.dbo.spt_values V，的sysdepends d，
sysusers中小号
其中o.id = d.id
和= o.xtype子（v.name，1,2）整理database_default和= v.type 'O9T'
和d.depid = @objid
和o.uid = s.uid
和deptype定义<2
选择@found_some = 1端
/ * **难道我们发现在sysdepends中什么？ * /如果@found_some = 0
RAISERROR（15461，1，-1）

SELECT RefType的，dep_name，类型，更新，选择，[柱]
从@sp_depends_xref

SET NOCOUNT关闭
返回（0） - sp_depends可

走

/ **对象：StoredProcedure的[DBO] [usp_FindReferences]脚本日期：11/18/2009 11时55分05秒** / SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE [DBO]。[usp_FindReferences]
- 添加参数的存储过程在这里
@tablename为nvarchar（500）= 0，
@colname为nvarchar（500）= 0 BEGIN AS
- SET NOCOUNT ON加入是为了避免额外的结果集 - 与SELECT语句的干扰。
SET NOCOUNT ON;
- 开始之前 - 刷新SQL模块声明@sql如为nvarchar（最大）; 设置@sql = ''; 选择@sql = @sql + N'begin尝试EXEC sp_refreshsqlmodule @name = '' '+ CAST（名称为nvarchar（4000））+ N '''; 结束Try开始捕获打印 '' 无法刷新 '+ CAST（名称为nvarchar（4000））+ N'： '' + ERROR_MESSAGE（）; IF XACT_STATE（）= -1 ROLLBACK; 抓到底; '从sys.sysobjects其中类型（' P”， 'V'， 'TF'， 'FN'）; - 按名称顺序; EXEC sp_executesql的@sql; - 现在我们可以用新的数据进行创建表#tempTableDependencies（
RefType的为nvarchar（20），
dep_name为nvarchar（500），
类型为nvarchar（500），
更新为nvarchar（500），
选定为nvarchar（500），
山口nvarchar的（500））

插入#tempTableDependencies执行usp_depends2 @tablename

创建表#tempDependencies（
RefType的为nvarchar（20），
dep_name为nvarchar（500），
类型为nvarchar（500），
更新为nvarchar（500），
选定为nvarchar（500），
山口nvarchar的（500））

声明@tempFilteredDependencies表（
对象名为nvarchar（500），
RefType的为nvarchar（20），
dep_name为nvarchar（500），
类型为nvarchar（500），
更新为nvarchar（500），
选定为nvarchar（500），
山口nvarchar的（500））

DECLARE @loopcounter INT
选择@loopcounter = COUNT（*）FROM #tempTableDependencies
DECLARE @dependencyname为nvarchar（500）
WHILE @loopcounter> 0
开始
SELECT TOP 1 @dependencyname = dep_name FROM #tempTableDependencies
打印 'loop_counter =' + CAST（@loopcounter如为nvarchar（20））
打印 '依赖=' + @dependencyname
插入#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

结束

通过对象名DROP TABLE #tempDependencies选择@tempFilteredDependencies顺序*
删除表#tempTableDependencies
结束
走

Answer 4:

类似的东西应该做的伎俩

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'

文章来源: How to find data table column reference in stored procedures

标签： sql-server-2005 stored-procedures refactoring automation schema

再贱就再见

女 | 书童

私信

收藏的人(0)

Ta的文章更多文章

0条评论

还没有人评论过~