Advanced Search

Global search on DataBase

/ Published in: SQL
Save to your folder(s)
by: Narayana Vyas Kondreddi

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. --EXEC SearchAllTables 'A38010088'
  2. --GO 
  3.  
  4. Here IS the complete stored PROCEDURE code:
  5.  
  6. CREATE PROC SearchAllTables
  7. (
  8. 	@SearchStr nvarchar(100)
  9. )
  10. AS
  11. BEGIN
  12.  
  13. 	-- Copyright �© 2002 Narayana Vyas Kondreddi. All rights reserved.
  14. 	-- Purpose: To search all columns of all tables for a given search string
  15. 	-- Written by: Narayana Vyas Kondreddi
  16. 	-- Site: http://vyaskn.tripod.com
  17. 	-- Tested on: SQL Server 7.0 and SQL Server 2000
  18. 	-- Date modified: 28th July 2002 22:50 GMT
  19.  
  20.  
  21. 	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  22.  
  23. 	SET NOCOUNT ON
  24.  
  25. 	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  26. 	SET  @TableName = ''
  27. 	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
  28.  
  29. 	WHILE @TableName IS NOT NULL
  30. 	BEGIN
  31. 		SET @ColumnName = ''
  32. 		SET @TableName = 
  33. 		(
  34. 			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
  35. 			FROM 	INFORMATION_SCHEMA.TABLES
  36. 			WHERE 		TABLE_TYPE = 'BASE TABLE'
  37. 				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
  38. 				AND	OBJECTPROPERTY(
  39. 						OBJECT_ID(
  40. 							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
  41. 							 ), 'IsMSShipped'
  42. 						       ) = 0
  43. 		)
  44.  
  45. 		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  46. 		BEGIN
  47. 			SET @ColumnName =
  48. 			(
  49. 				SELECT MIN(QUOTENAME(COLUMN_NAME))
  50. 				FROM 	INFORMATION_SCHEMA.COLUMNS
  51. 				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
  52. 					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
  53. 					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
  54. 					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
  55. 			)
  56.  
  57. 			IF @ColumnName IS NOT NULL
  58. 			BEGIN
  59. 				INSERT INTO #Results
  60. 				EXEC
  61. 				(
  62. 					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
  63. 					FROM ' + @TableName + ' (NOLOCK) ' +
  64. 					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
  65. 				)
  66. 			END
  67. 		END	
  68. 	END
  69.  
  70. 	SELECT ColumnName, ColumnValue FROM #Results
  71. END

URL: http://vyaskn.tripod.com

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.