0 Replies - 1774 Views - Last Post: 31 July 2008 - 01:53 AM

#1 dineeshd   User is offline

  • member icon

Reputation: 39
  • View blog
  • Posts: 619
  • Joined: 30-June 08

MSSQL Stored Procedure to find a string in Database

Posted 31 July 2008 - 01:53 AM

Description: Usage : EXEC Pr_FindString 'Hello World', 'MyTable' Passing NULL for table name will make the procedure search the whole database.This stored procedure will help you to find a particular string in a table or whole database.
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'Pr_SearchString' AND type = 'P')
	DROP PROCEDURE Pr_SearchString
GO

CREATE PROCEDURE Pr_SearchString
	@in_TextValue VARCHAR(256)
	, @in_LookInTable SYSNAME
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SelectCmd VARCHAR(4000)
	DECLARE @TableName sysname
	DECLARE @ColumnName sysname
	
	DECLARE CurTableSearch CURSOR FOR
		SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE TABLE_NAME = ISNULL(@in_LookInTable, TABLE_NAME) 
		AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar')
	
	OPEN CurTableSearch    
	FETCH NEXT FROM CurTableSearch INTO @TableName, @ColumnName    
	
	WHILE @@FETCH_STATUS = 0    
	BEGIN    
		SET @SelectCmd = 'SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @in_TextValue + '%'''
		
		EXEC (@SelectCmd)
		
		IF @@ROWCOUNT > 0
			PRINT 'Table : ' + @TableName + '  Column : ' + @ColumnName
		
		FETCH NEXT FROM CurTableSearch INTO @TableName, @ColumnName    
	END    
	
	CLOSE CurTableSearch    
	DEALLOCATE CurTableSearch  
	
	SET NOCOUNT OFF
END
GO


Is This A Good Question/Topic? 0
  • +

Page 1 of 1