Ads

Captured Technology - Blog

SQL Server Grep Function for Keyword Code Searching

SQL Server Grep Function for Keyword Code Searching

Back in the days when I was working with Sybase there was a function I found and used often to search for keywords in database code areas called sp_grep. You could pass a keyword to the stored procedure and it would return all objects that contained code with the keyword present.

This week I had the need to run Grep on a SQL Server database that we have taken over the maintenance of. I found this very useful stored procedure called sp_search_code by Narayana Vyas Kondreddi that performed the needed search.

 

USE [SOMEDB]
GO
/****** Object:  StoredProcedure [dbo].[sp_search_code]   ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_search_code]
(
@SearchStr     varchar(100),
@RowsReturned    int = NULL    OUT
)
AS
/**********************************************************************************
Copyright © 1997 - 2002 Narayana Vyas Kondreddi. All rights reserved. Purpose: To search the stored proceudre, UDF, trigger code for a given keyword. Written by: Narayana Vyas Kondreddi http://vyaskn.tripod.com Tested on: SQL Server 7.0, SQL Server 2000 Date created: January-22-2002 21:37 GMT Date modified: February-17-2002 19:31 GMT Email: [email protected] Examples: To search your database code for the keyword 'unauthorized': EXEC sp_search_code 'unauthorized' To search your database code for the keyword 'FlowerOrders' and also find out the number of hits: DECLARE @Hits int EXEC sp_search_code 'FlowerOrders', @Hits OUT SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result *************************************************************************************************/ BEGIN SET NOCOUNT ON SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name', CASE WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1 THEN 'Replication stored procedure' WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 THEN 'Extended stored procedure' WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1 THEN 'Stored Procedure' WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1 THEN 'Trigger' WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 THEN 'Table-valued function' WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 THEN 'Scalar-valued function' WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 THEN 'Inline function' END AS 'Object type', 'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + ''''
AS 'Run this command to see the object text' FROM syscomments c INNER JOIN sysobjects o ON c.id = o.id WHERE c.text LIKE '%' + @SearchStr + '%' AND encrypted = 0 AND ( OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1 ) ORDER BY 'Object type', 'Object name' SET @RowsReturned = @@ROWCOUNT END

Blog Directory

Latest technology news.
 Patrick Stevens
 532  246381  3/14/2024

Translate

Categories

Blog Calendar