Presents your  
SQL SERVER E-NEWSLETTER for October 28, 2003 
 
<-------------------------------------------> 
  
FINDING ALL OCCURRENCES OF AN OBJECT NAME   
  
It's common for developers to choose an object name that they later 
realize is problematic. The bad news is that by the time most developers
realize there's a problem with the name they chose, there are usually several 
hundred views, stored procedures, and functions in the database--and 
this object name appears in lots of them.   
  
Before you can risk changing the name, you need to determine the task 
size. When the object of interest is a table or a view, you can use the 
Dependencies tool that is built into Enterprise Manager. To do so, run 
Enterprise Manager, expand the databases node, expand the subnode of interest 
(tables or views), right-click on the object of interest, select All 
Tasks, and select Show Dependencies. You get two lists: Objects That Depend 
On X and Objects That X Depends On. The tool even offers a checkbox to 
toggle between first-level dependencies and all levels. However, you can't 
readily do anything with the lists.   
  
For a more useful list (that you could copy and paste, for example), you
could join the sysobjects and sysdepends tables in a query like this:   
  
SELECT sysobjects.name, *   
FROM sysdepends   
JOIN sysobjects ON sysdepends.id = sysobjects.id   
ORDER BY sysobjects.name   
  
You can also add a WHERE predicate if you wish. You still have a little 
footwork to do since all this gives you is the ID of the dependent 
object, which you must resolve.   
  
The problem appears when the object of interest isn't a table or a view 
but a column name. Enterprise Manager doesn't give you a convenient way 
to determine all the stored procedures, views, and user functions that 
depend on a given column name.   
  
The text for all of these objects is stored in the table syscomments in 
a column called Text. This table is keyed on the object ID, so you can 
only make sense of it by joining it to the sysobjects table.   
  
The following stored procedure performs this join and accepts a single 
parameter that is assumed to be the object of interest. You can pass the
name of a table or a view as well. It will perform correctly, but I
wrote it to deal with column names.   
  
CREATE PROCEDURE ap_Occurrences   
  
    (   
        @Target varchar(100)   
    )   
  
AS   
    /* SET NOCOUNT ON */   
    SELECT   
        sysobjects.Name,   
        syscomments.Text,   
        CASE   
        WHEN xtype = 'P' then 'Stored Procedure'   
        WHEN xtype = 'V' then 'View'   
        WHEN xtype = 'IF' then 'Function'   
        ELSE xtype   
        END   
        AS 'Object Type'   
    FROM   
        syscomments JOIN sysobjects ON syscomments.id = sysobjects.id   
    WHERE   
        CHARINDEX(@Target, Text) > 0   
    ORDER BY [Object Type], Name   
    RETURN   
  
To try this procedure, open Query Analyzer, select a database of 
interest, and paste the code featured above into a window. Execute it to
create the stored procedure, and then call the procedure something like this:  

  
Ap_Occurrences 'OrderID'   
  
You'll get a list of the user functions, stored procedures, and views 
that reference OrderID or whatever value you pass as the parameter.   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
  
----------------------------------------