Searching through SPs, Tables, Views

  • Jano Petras

    Old Hand

    Points: 327

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

  • craigpessano

    SSCertifiable

    Points: 5358

    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,

    definition

    FROM sys.sql_modules

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

    ORDER BY ObjectType, ObjectName[/font]

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

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

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