Skip to main content

SQL SERVER - How to serach stored procedure for a specific text

Search in stored procedures can be performed through INFORMATION_SCHEMA.ROUTINES view, or syscomments. Let say if you want to search for keyword 'power' in your stored procedure. try following:
1. Search using INFORMATION_SCHEMA.ROUTINES view


SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%power%' AND ROUTINE_TYPE='PROCEDURE'
2. Search using syscomments


SELECT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%power%' AND OBJECTPROPERTY(id, 'IsProcedure') = 1 GROUP BY OBJECT_NAME(id)
BUT you may not get correct result if your stored procedure is very large(>8kb) since whole text of procedure is not being returned. You will not see this problem in sql2005 as in sql2005 there are function like OBJECT_DEFINITION , which returns the whole text of the procedure.


SELECT Name FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%power%'

SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE Definition LIKE '%power%' AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%powerr%' AND ROUTINE_TYPE = 'PROCEDURE'


Still its not 100% perfect. There are 3rd party tools available to do this i.e.Gplex Database (www.gplexdb.com)

Comments

Popular posts from this blog

WPF - Checking Cap Lock Status in WPF

Checking Cap Lock status will be useful in Logon page where we can provide warning to user <Caps Lock is on. Having Caps Lock on may cause you to enter password incorrectly.>

Following sample uses the Control class that is a standard class within the System.Windows.Forms namespace. The DLL containing this namespace is automatically included in Windows Forms applications. The class includes a method named IsKeyLocked, which allows you to determine whether keys such as Caps Lock are switched on or off. To check the status of the Caps Lock key, you can use the method in the following manner:

Example:
privatevoid KeyDownEventHanlder(object sender, KeyEventArgs e)
{
if (Console.CapsLock == true)
{
lblError.Foreground = Brushes.Red;
lblError.Content = "Caps Lock is on.";// Having Caps Lock on may cause you to enter password incorrectly.";

}

else

{
if (lblError.Foreground == Brushes.Red)lblError.Foreground = Brushes.Transparent;
}

}

Error CS0234 The type or namespace name 'ApplicationInsights' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)

Error CS0234 The type or namespace name 'ApplicationInsights' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
To fix this issue run the following command in the Package Manager Console 
Install-Package Microsoft.ApplicationInsights.WindowsApps