Post
Ask Question

Search a word in all table of a Database

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]  
  2.   
  3. (  
  4.   
  5. @SearchStr nvarchar(100)  
  6.   
  7. )  
  8.   
  9. AS  
  10.   
  11. BEGIN  
  12.   
  13.   
  14.   
  15. CREATE  
  16. TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  
  17.   
  18.   
  19. SET  
  20. NOCOUNT  
  21. ON  
  22.   
  23.   
  24. DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)  
  25.   
  26. SET @TableName =  
  27. ''  
  28.   
  29. SET @SearchStr2 =  
  30. QUOTENAME('%'  
  31. + @SearchStr +  
  32. '%','''')  
  33.   
  34.   
  35. WHILE @TableName IS  
  36. NOT  
  37. NULL  
  38.   
  39. BEGIN  
  40.   
  41. SET @ColumnName =  
  42. ''  
  43.   
  44. SET @TableName =  
  45.   
  46. (  
  47.   
  48. SELECT  
  49. MIN(QUOTENAME(TABLE_SCHEMA)  
  50. +  
  51. '.'  
  52. +  
  53. QUOTENAME(TABLE_NAME))  
  54.   
  55. FROM INFORMATION_SCHEMA.TABLES  
  56.   
  57. WHERE TABLE_TYPE =  
  58. 'BASE TABLE'  
  59.   
  60. AND QUOTENAME(TABLE_SCHEMA)  
  61. +  
  62. '.'  
  63. +  
  64. QUOTENAME(TABLE_NAME)  
  65. > @TableName  
  66.   
  67. AND OBJECTPROPERTY(  
  68.   
  69. OBJECT_ID(  
  70.   
  71. QUOTENAME(TABLE_SCHEMA)  
  72. +  
  73. '.'  
  74. +  
  75. QUOTENAME(TABLE_NAME)  
  76.   
  77. ),  
  78. 'IsMSShipped'  
  79.   
  80. )  
  81. = 0  
  82.   
  83. )  
  84.   
  85.   
  86. WHILE  
  87. (@TableName IS  
  88. NOT  
  89. NULL)  
  90. AND  
  91. (@ColumnName IS  
  92. NOT  
  93. NULL)  
  94.   
  95. BEGIN  
  96.   
  97. SET @ColumnName =  
  98.   
  99. (  
  100.   
  101. SELECT  
  102. MIN(QUOTENAME(COLUMN_NAME))  
  103.   
  104. FROM INFORMATION_SCHEMA.COLUMNS  
  105.   
  106. WHERE TABLE_SCHEMA =  
  107. PARSENAME(@TableName, 2)  
  108.   
  109. AND TABLE_NAME =  
  110. PARSENAME(@TableName, 1)  
  111.   
  112. AND DATA_TYPE IN  
  113. ('char',  
  114. 'varchar',  
  115. 'nchar',  
  116. 'nvarchar')  
  117.   
  118. AND QUOTENAME(COLUMN_NAME)  
  119. > @ColumnName  
  120.   
  121. )  
  122.   
  123.   
  124. IF @ColumnName IS  
  125. NOT  
  126. NULL  
  127.   
  128. BEGIN  
  129.   
  130. INSERT  
  131. INTO #Results  
  132.   
  133. EXEC  
  134.   
  135. (  
  136.   
  137. 'SELECT '''  
  138. + @TableName +  
  139. '.'  
  140. + @ColumnName +  
  141. ''', LEFT('  
  142. + @ColumnName +  
  143. ', 3630)  
  144.   
  145. FROM '  
  146. + @TableName +  
  147. ' (NOLOCK) '  
  148. +  
  149.   
  150. ' WHERE '  
  151. + @ColumnName +  
  152. ' LIKE '  
  153. + @SearchStr2  
  154.   
  155. )  
  156.   
  157. END  
  158.   
  159. END   
  160.   
  161. END  
  162.   
  163.   
  164. SELECT ColumnName, ColumnValue FROM #Results  
  165.   
  166. END  
 Exec [SearchWordsInAllTables] 'gmail'