MS SQL - find text or command in a stored procedure
ActiveX RegEdit. ActiveX User account Manager Pure-ASP Upload
Export MDB/DBF from ASP Active LogFile WebChecker ActiveX/ASP Multi Dictionary object

See also	IISTracer, real-time IIS monitor and logging tool. аHuge ASP file upload with progress bar.а

Do you like this article?
Please, rate it
and write review!
Rated:

by Aspin.com users

What do you think?

Top messages

22.3.2003 19:18:41

Read and write SQL image data, store binary file to sql table.

(nbsp;WSH, Database, Conversion, VBScript)

4.5.2002 9:16:43

Send an email from ASP (WSH) using VBSscript, CDONTS and Outlook.

(nbsp;ASP / ASP.Net, WSH, VBScript, Email)

10.2.2005

Add an email account to a windows 2003 pop3 service using script

(nbsp;VBA, Email)

19.3.2005

Batch change of ID3 tags by VB Script in MP3 and other files.

(nbsp;VBScript, Sound)

MS SQL - find text or command in a stored procedure

Areas>ASP / ASP.Net>Database>MS SQL

Many, many times I need to find some text in one of hundreds stored procedures in Microsoft SQL server. I need to create some procedure for the task

Next t-sql code returns a recordset with list of stored procedures, containing @text.

execute find_text_in_sp 'delete', 'model'

List of all stored procedures containing 'delete' command in a 'model' database.

execute find_text_in_sp 'CURSOR'

List of all stored procedures containing 'CURSOR'.

-- =============================================
-- Author:    Antonin Foller, www.foller.cz
-- Create date: 2007-09-19
-- Description: Search a text in stored procedure source code.
-- @text - any text to find, search is done by like '%text%'
-- @dbname - database where to search, 
--         - if omitted, all databases in the SQL server instance
-- =============================================
ALTER PROCEDURE [dbo].[find_text_in_sp]
  @text varchar(250),
  @dbname varchar(64) = null
AS BEGIN
SET NOCOUNT ON;

if @dbname is null
  begin
    --enumerate all databases.
  DECLARE #db CURSOR FOR Select Name from master..sysdatabases
  declare @c_dbname varchar(64)

OPEN #db FETCH #db INTO @c_dbname
  while @@FETCH_STATUS <> -1 --and @MyCount < 500
   begin
     execute find_text_in_sp @text, @c_dbname
     FETCH #db INTO @c_dbname
   end  
  CLOSE #db DEALLOCATE #db
 end --if @dbname is null
else
 begin --@dbname is not null
  declare @sql varchar(250)
  --create the find like command
  select @sql = 'select ''' + @dbname + ''' as db, o.name,m.definition '
  select @sql = @sql + ' from '+@dbname+'.sys.sql_modules m '
  select @sql = @sql + ' inner join '+@dbname+'..sysobjects o on m.object_id=o.id'
  select @sql = @sql + ' where [definition] like ''%'+@text+'%'''
  execute (@sql)
 end --@dbname is not null
END

See also for 'MS SQL - find text or command in a stored procedure' article:

If you like this page, please include next link on your pages:

<A
 Href="http://www.motobit.com/tips/detpg_sql-find-text-stored-procedure/"
 Title="Special stored procedure to find any
  text inside a source code
  of all stored procedures in
  one database/server."
>MS SQL - find text or command in a stored procedure</A>

аааааIISTracer - IIS ISAPI real-time monitor IISTracer is a real-time monitoring tool for Microsoft IIS, which will show/log you what is happenning on IIS server right now. It let's you reveal problems with long-running scripts (.asp, .cgi, cfm...), hang-up states and low resource situations and lets you stop long-running requests (uploads/downloads).	аааааActiveX User account Manager - Set of simple objects for creating, deleting, and managing user accounts, groups, servers and domains in the Windows NT environment.
аааааActive log file - Hi-performance text file logging for ASP/VBS/VBA applications. Lets you create daily/weekly/monthly log files with variable number of logged values and extra timing and performance info.	аааааActiveX windows registry editor - Intuitive, easy to use COM interface to windows registry. Set of classes to read/enumerate/modify windows registry keys and values from ASP, VBS and T-SQL.
аааааActiveX/ASP Multi Dictionary object - Free-threaded hi-speed dictionary algorithm with unique/nonunique keys (map/multimap). Connect to another dictionary object in the same process. Lock and Unlock methods to synchronize tasks (application scope). Share ASP Application/Session objects.	аааааExport DBF/MDB from ASP - Conversion from recordset to MDB/DBF. Direct binary output of MDB or DBF files from ASP pages with one row of code.
аааааPure-ASP upload - lets you upload files using Pure ASP VBS code (using multipart/form-data and input type=file).	аааааByteArray - Works with safearray binary data (VT_UI1 \| VT_ARRAY) - save/restore binary data from disk, find, work with code pages, convert to string/hexstring(SQL).
аааааWebChecker - Checks http, https, ftp and gopher internet connections in regular intervals. Lets you monitor web site functionality (uptime). Enables restart or notification on problems.	аааааHTTPLog ISAPI filter - Lets you log incomming/outgoing http header and document data to separate files. Monitor of IIS service input/output.

й 1996 Ц 2008 Antonin Foller, PSTRUH Software, e-mail help@pstruh.cz