Is there a query to search text of all SQL Server database objects that reference a specific table/instance?

Tags:
Database objects
Query
SQL Server migration
We want to migrate to SQL Server 2005. We need to find all database objects such as Stored Procedures, Views, Triggers, User Defined Functions accessed specific tables, DTS packages that reference a specific table/instance. Is there a query that will allow me search the text of all?

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can search the syscomments table within each database on the SQL 2000 server for the text you are looking for. This will search all procedures, views, triggers and UDFs. However the DTS packages are stored in a binary format, and the easiest method to use it to use is to open then up and manually check them.

Discuss This Question: 8  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    66,075 pointsBadges:
    report
  • Tashfeen
    Please refer to: http://whereclause.com/blog/?p=26
    10 pointsBadges:
    report
  • Kccrosser
    Note that the syscomments table contains "chunks" of text for each object - a stored procedure, function, view, etc., may span multiple rows in the syscomments table. Further, the text is arbitrarily "chopped" at the 4000th character in each block, so it is possible that the object name you are looking for is broken across two rows. The safest way to do a search for info in syscomments is to use a function that assembles the chunks and then uses charindex to find the data, e.g.: declare @rowtext nvarchar(4000) declare @fulltext nvarchar(max) declare cur cursor for select text from sys.syscomments with (nolock) where id = <obj_id> order by colid set @fulltext = null open cur fetch next from cur into @rowtext while @@fetch_status = 0 begin set @fulltext = isnull(@fulltext,'') + @rowtext) fetch next from cur into @rowtext end close cur deallocate cur if charindex(<find text>, @fulltext) > 0 print 'text found in ' + @objname You will need to get the ids for the objects by querying the sys.sysobjects table for all objects of the appropriate types (e.g., 'P', 'FN', 'V', etc.): declare curobj cursor for select id, name from sys.sysobjects with (nolock) where type in ('P','FN','V',...) declare @id int declare @name varchar(128) open curobj fetch next from curobj into @id, @name while @@fetch_status = 0 begin ... put the loop to get and test the text above into this area... fetch next from curobj into @id, @name end close curobj deallocate curobj Now - a bit more bad news. It isn't that simple either. The syscomments table contains the text, but it also contains real "comments" - i.e., transact-sql statements and statement fragments that have been commented out, in blocks ("/*" to "*/") and in single line fragments ("--" prefixes). It also contains line break characters (CR and LF). Your best bet is to process as follows: 1. Collect all the code into a single string (@fulltext above) 2. Then strip all the "block comments" (iteratively find "/*", then find the next following "*/" and remove all the text between them, repeat until there are no remaining block comments) 3. Now find all the "--" chars and remove everything to the next CR character 4. Now replace all the CR and LF characters with blanks set @fulltext = replace(replace(@fulltext, char(10),' '), char(13), ' ') Now - at this point, the string contains "clean" transact-sql and you can find the object name of interest with charindex. Depending on how you plan to use it, you can combine the object/text cursor by simply joining the tables, as in: declare cur cursor for select so.id, so.name, so.type, sc.colid, sc.text from sys.sysobjects so with (nolock) inner join sys.syscomments sc with (nolock) on sc.id = so.id where so.type in ('P','FN','V',...) order by so.id, sc.colid Then, your program loop will need to notice changes in the id column to indicate that a new object is starting. When the id changes, clean and check the assembled code (in @fulltext), then clear it and start accumulating the next code/view block.
    3,830 pointsBadges:
    report
  • Kccrosser
    I am not sure why my code was altered when I saved it - here is the correct version: declare cur cursor for select so.id, so.name, so.type, sc.colid, sc.text from sys.sysobjects so with (nolock) inner join sys.syscomments sc with (nolock) on sc.id = so.id where so.type in (’P',’FN’,'V’,…) order by so.id, sc.colid
    3,830 pointsBadges:
    report
  • Kccrosser
    Arrgghhh... Something in the wiki box likes to alter "so" followed by a period followed by "name" and inject all that http reference stuff - trying again with "so" replaced by "sobj". declare cur cursor for select sobj.id, sobj.name, sobj.type, sc.colid, sc.text from sys.sysobjects sobj with (nolock) inner join sys.syscomments sc with (nolock) on sc.id = sobj.id where sobj.type in (’P',’FN’,'V’,…) order by sobj.id, sc.colid
    3,830 pointsBadges:
    report
  • Kccrosser
    Giving up now... I have been defeated by the wiki editor.. One last try with the "code" bracketing.
    declare cur cursor for select so.id, so.name, so.type, sc.colid, sc.text
    from sys.sysobjects so with (nolock)
       inner join sys.syscomments sc with (nolock) on sc.id = so.id
    where so.type in (’P’,’FN’,’V’,…)
    order by so.id, sc.colid
    
    3,830 pointsBadges:
    report
  • Fraymond
    Please read my blog for the query. http://geekatwork.wordpress.com/2010/03/30/database-schema-search/ Search for Text in Stored Procedures DECLARE @SEARCH_TERM VARCHAR(100) SET @SEARCH_TERM = ‘%BANKCARD_999%’ SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE @SEARCH_TERM ORDER BY ROUTINE_NAME Search for Text in All Columns DECLARE @SEARCH_TERM VARCHAR(100) SET @SEARCH_TERM = ‘%BANKCARD_999%’ SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE @SEARCH_TERM ORDER BY TABLE_NAME, COLUMN_NAME Search for Text in All Database Objects DECLARE @SEARCH_TERM VARCHAR(100) SET @SEARCH_TERM = ‘%BANKCARD_999%’ SELECT o.NAME, c.TEXT FROM sysobjects o INNER JOIN syscomments c ON o.id = c.id WHERE c.TEXT LIKE @SEARCH_TERM o.TYPE IN ( ‘P’, — Stored Procedure ‘V’, — View ‘FN’, — Function, ‘PK’ — Key )
    10 pointsBadges:
    report
  • Kccrosser
    Hi Fraymond, The syscomments search you have listed will only work reliably for comments that are less than 4000 characters in length, or when the search term happens to land completely in one syscomments record or the other. As I noted earlier, when records in syscomments are longer than 4000 characters, they are arbitrarily broken at the 4000th character, which may be in the middleof the search term. It is much safer to use a function to collate the syscomments data and then use the charindex function to locate the substring within the complete text string.
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following