Вот что я нашел: метод 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
end
/* ** Теперь проверьте, что зависит от объекта. */ если существует (выберите * из sysdepends, где depid = @objid) begin
raiserror(15460,-1,-1)
INSERT INTO @sp_depends_xref (RefType, dep_name, type)
выберите отдельный 'BY ', 'name' = (s.name + '.' + o.name), type = substring(v.name, 5, 16)
from sysobjects o, master.dbo.spt_values v, sysdepends d,< br> sysusers s
где o.id = d.id
и o.xtype = substring(v.name,1,2) сопоставить database_default и v.type = 'O9T'
и d.depid = @objid
и o.uid = s.uid
и 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
set nocount off
return (0) -- sp_depends
GO
/** Объект: 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 AS BEGIN
-- SET NOCOUNT ON добавлен для предотвращения -- дополнительных наборов результатов, мешающих операторам SELECT.
SET NOCOUNT ON;
-- Перед запуском - обновить модуль sql, объявить @sql как nvarchar( Максимум); установить @sql = ''; выберите @sql = @sql + N'begin try exec sp_refreshsqlmodule @name = ''' + CAST (имя как nvarchar (4000)) + N'''; end try start catch print ''Не удалось обновить' + CAST(имя как nvarchar(4000)) + N': '' + ERROR_MESSAGE(); ЕСЛИ XACT_STATE() = -1 ОТМЕНА; концевой захват; ' из sys.sysobjects, где введите ('P', 'V', 'TF', 'FN'); -- упорядочить по имени; exec sp_executesql @sql; -- Теперь мы можем приступить к созданию таблицы со свежими данными #tempTableDependencies (
reftype nvarchar(20),
dep_name nvarchar(500),
type nvarchar(500),
обновленный nvarchar(500),
выбрано nvarchar(500),
столбец nvarchar(500))
вставить в #tempTableDependencies выполнить usp_depends2 @tablename
создать таблицу #tempDependencies (
reftype nvarchar(20),
dep_name nvarchar(500),
type 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
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
вставить в #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 в порядке по имени объекта drop table #tempDependencies
drop table #tempTableDependencies
END
GO
person
Tsachy-Gal Shemesh
schedule
16.03.2011
