How to search all columns of all tables in a database for a keyword


/ Published in: SQL
Save to your folder(s)



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

URL: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.