Как найти ссылку на столбец таблицы данных в хранимых процедурах

Question

Как найти ссылку на столбец таблицы данных в хранимых процедурах

Я изменил имя столбца в таблице в моей базе данных SQL Server 2005. У меня также есть довольно большая коллекция хранимых процедур, которые могут или не могут ссылаться на этот столбец. Есть ли способ найти хранимые процедуры, которые ссылаются на этот столбец, без фактического прохождения каждой хранимой процедуры, и выполнить поиск вручную? Есть ли способ автоматически найти, какие хранимые процедуры сейчас сломаются или что-то еще? У меня нет доступа к таким инструментам рефакторинга SQL, как SQL Refactor от RedGate.

Спасибо!

4

stored-procedures automation sql-server-2005 refactoring schema

Источник

user58375 10 июл '09 в 20:08

4 ответа

Решение

Стандартный ответ "sp_depends", но в SQL 7.0 и 2000 он не гарантированно был точным (то есть обновленным). Я не знаю, обращались ли они к этому в SQL 2005 или 2008, так как я довольно давно поработал над этим. Это не делает именно то, что вы хотите, но может получить вас раньше, чем в противном случае

Он основан на этом запросе:

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)
CodeLength (насколько велик код чанка, в котором была найдена строка)
определение (копия указанного куска кода. Хм, я никогда этим не пользуюсь, может, мне стоит его убрать?)

2

Источник

user7491 10 июл '09 в 20:51

Вот что я обнаружил: метод Nir является лучшим, так как он находит реальные зависимости (не по тексту хранимой процедуры), хотя он не будет работать должным образом, если вы не обновите модуль sql. Решения nip и Philip одинаковы - найдите строку в коде хранимой процедуры, она не будет работать должным образом, если в нескольких таблицах указано одинаковое имя столбца.

Поэтому я решил использовать решение 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

конец
/* ** Теперь проверим, что зависит от объекта. */ если существует (выберите * из sysdepends, где depid = @objid) начинаются
RAISERROR (15460, -1, -1)
INSERT INTO @sp_depends_xref (RefType, dep_name, type)
выберите различное 'BY', 'name' = (s.name + '.' + o.name), тип = подстрока (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
и o.uid = s.uid
и deptype < 2
выберите @found_some = 1 конец
/* ** Мы нашли что-нибудь в sysdepends? */ if @found_some = 0
RAISERROR (15461,-1,-1)

ВЫБЕРИТЕ reftype, dep_name, type, updated, selected, [column]
ОТ @sp_depends_xref

отключить отсчет
return (0) - sp_depends

ИДТИ

/** Объект: StoredProcedure [dbo].[Usp_FindReferences] Дата скрипта: 18.11.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 КАК НАЧАТЬ
- SET NOCOUNT ON добавлен, чтобы предотвратить дополнительные наборы результатов от - вмешательства в инструкции SELECT.
УСТАНАВЛИВАЙТЕ NOCOUNT ON;
- Перед запуском - обновить модуль sql, объявив @sql как nvarchar(max); set @sql = ''; select @sql = @sql + N'begin попробуйте exec sp_refreshsqlmodule @name = ''' + CAST(имя как nvarchar(4000)) + N'''; конец попытаться начать ловить печать '' Не удалось обновить '+ CAST (имя как nvarchar(4000)) + N': '' + ERROR_MESSAGE(); IF XACT_STATE() = -1 ROLLBACK; конечный улов; 'из sys.sys объекты, где введите ('P', 'V', 'TF', 'FN');- порядок по имени; exec sp_executesql @sql; - Теперь мы можем приступить к созданию свежих данных, создав таблицу #tempTableDependencies (
reftype nvarchar (20),
имя_пункта nvarchar(500),
тип nvarchar(500),
обновленный nvarchar(500),
выбран нварчар (500),
col nvarchar (500))

вставить в #tempTableDependencies выполнить usp_depends2 @tablename

создать таблицу #tempDependencies (
reftype nvarchar (20),
имя_пункта nvarchar(500),
тип nvarchar(500),
обновленный nvarchar(500),
выбран нварчар (500),
col nvarchar (500))

объявить таблицу @tempFilteredDependencies (
имя объекта nvarchar(500),
reftype nvarchar (20),
имя_пункта nvarchar(500),
тип nvarchar(500),
обновленный nvarchar(500),
выбран нварчар (500),
col nvarchar (500))

ОБЪЯВИТЬ @loopcounter INT
select @loopcounter = COUNT (*) FROM #tempTableDependencies
ОБЪЯВИТЬ @dependencyname nvarchar (500)
WHILE @loopcounter> 0
НАЧАТЬ
ВЫБЕРИТЕ ТОП 1 @dependencyname = dep_name FROM #tempTableDependencies
print 'loop_counter =' + CAST (@loopcounter как nvarchar (20))
print 'зависимость = ' + @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

КОНЕЦ

выберите * из порядка @tempFilteredDependencies по таблице перетаскивания имени объекта #tempDependencies
удалить таблицу #tempTableDependencies
КОНЕЦ
ИДТИ

1

Источник

user662569 16 мар '11 в 13:26

Нечто подобное должно сработать

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'

0

Источник

user5818 10 июл '09 в 20:47

Другие вопросы по тегам stored-procedures automation sql-server-2005 refactoring schema

user213137 18 ноя '09 в 18:13 2009-11-18 18:13 · Accepted Answer · 2009-11-18 18:13

Вот кое-что, что может вам помочь. Я создал два пользовательских хранимых прока, которые делают что-то похожее на то, что вы просите.

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