if I want to find what's referencing an object in SQL Server, is searching syscomments comprehensive?

Question

Let's say I have information in a table that is in the wrong database (like Customer info in the Items database). I really want to move this information, but I need to figure out who's using it. If I use this code to search for the table name, say CustomerContactNumbers, is there any possibility of things slipping by? I'm going to ignore any composed SQL that's happening outside of the database, I can do a text search in source control for that (plus don't you think they deserve to have their code broken in that case?). [Not my code, I lifted it from somewhere, probably here]:

declare @Search varchar(255) 
SET @Search='%CustomerContactNumbers%' 

SELECT DISTINCT 
    LEFT(so.name, 100) AS Object_Name, 
    "object_type"=left( 
          case so.type 
        when 'U' then 'Table - User' 
        when 'S' then 'Table - System' 
        when 'V' then 'Table - View' 
        when 'TR' then 'Trigger' 
        when 'P' then 'Stored Procedure' 
        when 'C' then 'Constraint - Check' 
        when 'D' then 'Default' 
        when 'K' then 'Key - Primary' 
        when 'F' then 'Key - Foreign' 
        when 'L' then 'Log'           
        when 'R' then 'Rule' 
        when 'RF' then 'Replication Filter stp' 
        else '<<UNKNOWN '''+so.type+'''>>' 
    end  -- case so.type 
        ,25) 
FROM syscomments sc  
    INNER JOIN sysobjects so  
        ON so.id = sc.id 
WHERE 
    text Like '%'+@Search+'%' 
ORDER BY  
    2,1 

Does that cover it?

Answer 1

Not entirely, no, because syscomments is broken down into 4k chunks. I.e. if the reference to the object you're looking for happens to sit on that 4k boundary, then this query will not find it in any single 4k chunk. If you are on 2005+ then you can use sys.sql_modules, and that will give you a much better answer, but you will also need to double check other tables that may have a definition (for example, check constraints).

The other concern would be objects in other databases which make reference to the objects - those would not be picked up by your query.

Answer 2

You didn't specify what version of SQL Server you are using. I am assuming 2000 but if you are on 2005 or later you should look into the sys.sql_modules catalog view and/or the OBJECT_DEFINITION() function. As a quick example:

SELECT SCHEMA_NAME(schema_id) +'.'+ name
FROM sys.objects
WHERE OBJECT_DEFINITION(object_id) LIKE '%' + @Search + '%';

-- or

SELECT OBJECT_SCHEMA_NAME(object_id) +'.'+ OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE [definition] LIKE '%' + @Search + '%';

Also you might want to check out the following StackOverflow thread about RedGate's new and free SQL Search tool, and some alternative approaches:

http://stackoverflow.com/questions/2187763/what-other-products-are-similar-to-redgates-sql-search/

If you truly are stuck on 2000 then this article I wrote almost 10 years ago may still be relevant and helpful (including accommodating for the 4K chunk problem that Matt mentioned):

http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html