Post reply

Searching through SPs, Tables, Views

  • February 8, 2011 at 8:16 pm

    #231144

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

  • February 9, 2011 at 7:19 am

    #1284443

    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]

  • May 17, 2016 at 6:49 am

    #1879499

    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