SQL Searh in ALL DataBase


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

Stored Procedure for search a string in all fields of all tables of one DataBase


Copy this code and paste it in your HTML
  1. CREATE Proc SEARCH
  2. (
  3. @SearchStr nVarChar(100)
  4. )
  5. AS
  6. BEGIN
  7.  
  8. CREATE TABLE #Results ( TableName nVarChar(370),
  9. ColumnName nVarChar(370),
  10. ColumnValue nVarChar(3630))
  11.  
  12. SET NoCount ON
  13.  
  14. DECLARE @TableName nVarChar(256),
  15. @ColumnName nVarChar(128),
  16. @SearchStr2 nVarChar(110),
  17. @ColumnValue nVarChar(4000)
  18.  
  19. SET @TableName = ''
  20. SET @SearchStr2 = QuoteName('%' + @SearchStr + '%','''')
  21.  
  22. While @TableName IS NOT NULL
  23. BEGIN
  24. SET @ColumnName = ''
  25. SET @TableName =
  26. (
  27. SELECT MIN(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME))
  28. FROM INFORMATION_SCHEMA.Tables
  29. WHERE Table_TYPE = 'BASE Table'
  30. AND QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME) > @TableName
  31. AND ObjectProperty (
  32. Object_Id(QuoteName(TABLE_SCHEMA) + '.' + QuoteName(TABLE_NAME)),
  33. 'IsMSShipped'
  34. ) = 0
  35. )
  36.  
  37. While (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  38. BEGIN
  39. SET @ColumnName =
  40. (
  41. SELECT MIN(QuoteName(COLUMN_NAME))
  42. FROM INFORMATION_SCHEMA.COLUMNS
  43. WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
  44. AND TABLE_NAME = PARSENAME(@TableName, 1)
  45. AND DATA_TYPE IN ('char', 'VarChar', 'nchar', 'nVarChar', 'int', 'decimal')
  46. AND QuoteName(COLUMN_NAME) > @ColumnName
  47. )
  48.  
  49. IF @ColumnName IS NOT NULL
  50. BEGIN
  51. INSERT INTO #Results
  52. EXEC
  53. (
  54. 'Select ''' + @TableName + ''', ' +
  55. '''' + @TableName + '.' + @ColumnName + ''', ' +
  56. 'LEFT(' + @ColumnName + ', 3630) From ' + @TableName + ' (NOLOCK) Where ' + @ColumnName + ' LIKE ' + @SearchStr2
  57. )
  58. END
  59. END
  60. END
  61.  
  62. SELECT TableName, ColumnName, ColumnValue
  63. FROM #Results
  64.  
  65. END

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.