Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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 08, 2011 8:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 01, 2013 7:37 AM
Points: 5, Visits: 15
Comments posted to this topic are about the item Searching through SPs, Tables, Views
Post #1060778
Posted Wednesday, February 09, 2011 7:19 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:13 PM
Points: 4,365, Visits: 1,722
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse