Searching through SPs, Tables, Views

• Jano Petras

  Old Hand

  Points: 327

  More actions

  February 8, 2011 at 8:16 pm

  #231144

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

• craigpessano

  SSCertifiable

  Points: 5358

  More actions

  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]

• akljfhnlaflkj

  SSC Guru

  Points: 76202

  More actions

  May 17, 2016 at 6:49 am

  #1879499

  Thanks for the script.