Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Searching through SPs, Tables, Views Expand / Collapse
Author
Message
Posted Tuesday, February 8, 2011 8:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 1, 2014 9:32 PM
Points: 5, Visits: 16
Comments posted to this topic are about the item Searching through SPs, Tables, Views
Post #1060778
Posted Wednesday, February 9, 2011 7:19 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Monday, January 11, 2016 9:13 AM
Points: 5,002, Visits: 2,273
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:
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



Post #1061111
Posted Tuesday, May 17, 2016 6:49 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 21, 2016 5:37 AM
Points: 7,886, Visits: 763
Thanks for the script.
Post #1786834
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse