Sometimes it happens that we forget where something was in the database. This Stored Procedure allows for searching every table and every field and returns a passed value. It's a powerful tool for searching but its not to be used in Programming.
  1. Create PROC [dbo].[SearchWordsInAllTables]
  3. (
  5. @SearchStr nvarchar(100)
  7. )
  9. AS
  11. BEGIN
  15. CREATE
  16. TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
  19. SET
  20. NOCOUNT
  21. ON
  24. DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
  26. SET @TableName =
  27. ''
  29. SET @SearchStr2 =
  30. QUOTENAME('%'
  31. + @SearchStr +
  32. '%','''')
  35. WHILE @TableName IS
  36. NOT
  37. NULL
  39. BEGIN
  41. SET @ColumnName =
  42. ''
  44. SET @TableName =
  46. (
  48. SELECT
  49. MIN(QUOTENAME(TABLE_SCHEMA)
  50. +
  51. '.'
  52. +
  53. QUOTENAME(TABLE_NAME))
  55. FROM INFORMATION_SCHEMA.TABLES
  57. WHERE TABLE_TYPE =
  58. 'BASE TABLE'
  60. AND QUOTENAME(TABLE_SCHEMA)
  61. +
  62. '.'
  63. +
  64. QUOTENAME(TABLE_NAME)
  65. > @TableName
  67. AND OBJECTPROPERTY(
  69. OBJECT_ID(
  71. QUOTENAME(TABLE_SCHEMA)
  72. +
  73. '.'
  74. +
  75. QUOTENAME(TABLE_NAME)
  77. ),
  78. 'IsMSShipped'
  80. )
  81. = 0
  83. )
  86. WHILE
  87. (@TableName IS
  88. NOT
  89. NULL)
  90. AND
  91. (@ColumnName IS
  92. NOT
  93. NULL)
  95. BEGIN
  97. SET @ColumnName =
  99. (
  101. SELECT
  102. MIN(QUOTENAME(COLUMN_NAME))
  104. FROM INFORMATION_SCHEMA.COLUMNS
  106. WHERE TABLE_SCHEMA =
  107. PARSENAME(@TableName, 2)
  109. AND TABLE_NAME =
  110. PARSENAME(@TableName, 1)
  112. AND DATA_TYPE IN
  113. ('char',
  114. 'varchar',
  115. 'nchar',
  116. 'nvarchar')
  118. AND QUOTENAME(COLUMN_NAME)
  119. > @ColumnName
  121. )
  124. IF @ColumnName IS
  125. NOT
  126. NULL
  128. BEGIN
  130. INSERT
  131. INTO #Results
  133. EXEC
  135. (
  137. 'SELECT '''
  138. + @TableName +
  139. '.'
  140. + @ColumnName +
  141. ''', LEFT('
  142. + @ColumnName +
  143. ', 3630)
  145. FROM '
  146. + @TableName +
  147. ' (NOLOCK) '
  148. +
  150. ' WHERE '
  151. + @ColumnName +
  152. ' LIKE '
  153. + @SearchStr2
  155. )
  157. END
  159. END
  161. END
  164. SELECT ColumnName, ColumnValue FROM #Results
  166. END
Exec [SearchWordsInAllTables] 'gmail'