Posted By

Eloi on 07/09/10


Tagged

search sp sqlserver StoredProcedure


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Eloi


SQL Searh in ALL DataBase


 / Published in: SQL
 

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

  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  

You need to login to post a comment.