views:

1252

answers:

3

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.

Thanks!

A: 

Hi Dan,

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'
Nip
This won't work if the procedures are lengthy. The column name could be split across multiple syscomments records.
hurcane
A: 

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

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.

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)
  • CodeLength (how big is the chunk o' code the string was found in)
  • definition (a copy of said chunk of code. Hmm, I never use this, maybe I should take it out?)
Philip Kelley
I get "Msg 195, Level 15, State 10, Line 6'schema_name' is not a recognized function name."
Chris McCall
A: 

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 - an extended version of sp_depends

  2. 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


Nir