Searching through SPs, Tables, Views

  • Comments posted to this topic are about the item Searching through SPs, Tables, Views

  • Be careful. The ROUTINE_DEFINITION returned by INFORMATION_SCHEMA.ROUTINES is nvarchar(4000). Using this view will miss references beyond that for procs/functions that are more than 4000 characters. The same goes for VIEW_DEFINITION. In SQL Server 2005 and above sys.sql_modules is a better alternative. The definition column returns the full code text - nvarchar(MAX). It includes procs, functions, views, triggers, and more.

    Sample query:

    [font="Courier New"]SELECT OBJECTPROPERTYEX(object_id, 'BaseType') AS ObjectType,

    OBJECT_NAME(object_id) AS ObjectName,


    FROM sys.sql_modules

    WHERE CHARINDEX('<text to find>', definition) > 0

    ORDER BY ObjectType, ObjectName[/font]

  • Thanks for the script.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply