Search for a text in all databases - SQLServerCentral


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



Copy this code and paste it in your HTML
  1. /*****************************************************************************************************
  2. *
  3. * Author Rafal Skotak
  4. * Purpose Procedure searches for a text in all text-like columns in all or one selected database
  5. * in all or selected table
  6. * Created 2008.01.25
  7. * Updated 2011.02.18 - bug fixed (next table was displayed with current results)
  8. *
  9. ******************************************************************************************************/
  10.  
  11.  
  12. IF EXISTS(SELECT * FROM sys.objects WHERE object_id = object_id('dbo.proc_search_for_text') AND TYPE = 'P')
  13. ����DROP PROCEDURE dbo.proc_search_for_text
  14. GO
  15.  
  16. CREATE PROCEDURE dbo.proc_search_for_text
  17. ����@text VARCHAR(MAX),
  18. ����@db_name sysname = NULL,
  19. ����@sel_table_name sysname = NULL
  20. AS
  21. BEGIN
  22. ����SET nocount ON
  23.  
  24. ����----------------------------------------------------------------------------
  25. ����-- check parameters
  26.  
  27. ����IF @text IS NULL
  28. ����BEGIN
  29. ��������raiserror('Text is null', 16, 1)
  30. ��������RETURN
  31. ����END
  32.  
  33. ����IF @text = ''
  34. ����BEGIN
  35. ��������raiserror('Text is empty', 16, 1)
  36. ��������RETURN
  37. ����END
  38.  
  39. ����IF @db_name IS NOT NULL
  40. ����BEGIN
  41. ��������IF NOT EXISTS(SELECT * FROM master.sys.databases WHERE name = @db_name AND name NOT IN ('tempdb'))
  42. ��������BEGIN
  43. ������������raiserror('Database does not exist or can not be scanned', 16, 1)
  44. ������������RETURN
  45. ��������END
  46. ����END
  47.  
  48. ����------------------------------------------------------------------------------------------------
  49. ����-- get databases list
  50. ����
  51. ����CREATE TABLE #temp_dbs_table
  52. ����(
  53. ��������db_name sysname NOT NULL PRIMARY KEY
  54. ����)
  55.  
  56. ����INSERT INTO #temp_dbs_table (db_name) SELECT name FROM master.sys.databases WHERE name NOT IN ('tempdb') AND (@db_name IS NULL OR (@db_name IS NOT NULL AND @db_name = name))
  57.  
  58. ����DECLARE @current_db_name sysname
  59.  
  60. ����SET @current_db_name = N''
  61.  
  62. ����CREATE TABLE #temp_columns_table
  63. ����(
  64. ��������table_id INT NOT NULL,
  65. ��������schema_name sysname NOT NULL,
  66. ��������TABLE_NAME sysname NOT NULL,
  67. ��������column_name sysname NOT NULL,
  68. ��������mod_flag tinyint NOT NULL DEFAULT 1
  69.  
  70. ��������PRIMARY KEY(schema_name, TABLE_NAME, column_name)
  71. ����)
  72.  
  73. ����while @current_db_name IS NOT NULL
  74. ����BEGIN
  75. ��������SET @current_db_name = NULL
  76.  
  77. ��������-----------------------------------------------------------------------------------------------
  78. ��������-- move to next database
  79.  
  80. ��������SELECT top 1 @current_db_name = db_name FROM #temp_dbs_table
  81.  
  82. ��������IF @current_db_name IS NULL
  83. ������������break
  84. ��������
  85. ��������---------------------------------------------------------------------------------------------
  86. ��������-- get columns list
  87.  
  88. ��������TRUNCATE TABLE #temp_columns_table
  89.  
  90. ��������DECLARE @n_cmd nvarchar(MAX)
  91.  
  92. ��������IF @sel_table_name IS NULL
  93. ��������BEGIN
  94. ������������SET @n_cmd = N'insert into #temp_columns_table
  95. ������������select distinct st.object_id, ss.name as scheme_name, st.name as table_name, sc.name as column_name, 1
  96. ������������������������ from [' + @current_db_name + '].sys.schemas as ss inner join
  97. ������������������������ [' + @current_db_name + '].sys.tables as st on ss.schema_id = st.schema_id inner join
  98. ������������������������ [' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
  99. ����������������������������system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */)
  100. ����������������������������order by ss.name, st.name, sc.name'
  101. ��������END
  102. ��������ELSE
  103. ��������BEGIN
  104. ������������SET @n_cmd = N'insert into #temp_columns_table
  105. ������������select distinct st.object_id, ss.name as scheme_name, st.name as table_name, sc.name as column_name, 1
  106. ������������������������ from [' + @current_db_name + '].sys.schemas as ss inner join
  107. ������������������������ [' + @current_db_name + '].sys.tables as st on ss.schema_id = st.schema_id inner join
  108. ������������������������ [' + @current_db_name + '].sys.columns as sc on st.object_id = sc.object_id where
  109. ����������������������������system_type_id in (35 /* text */, 99 /* ntext */, 167 /* varchar */, 175 /* char */, 231 /* nvarchar */, 239 /* nchar */)
  110. ������������������������and st.name = ''' + @sel_table_name + ''' order by ss.name, st.name, sc.name'
  111. ��������END
  112.  
  113. ��������-- print @n_cmd
  114. ��������
  115. ��������EXEC sp_executesql @n_cmd
  116.  
  117. ��������----------------------------------------------------------------------------------
  118. ��������-- cycle through columns
  119.  
  120. ��������DECLARE @n_sec_cmd nvarchar(MAX)
  121. ��������DECLARE @n_thi_cmd nvarchar(MAX)
  122. ��������DECLARE @schema_name sysname
  123. ��������DECLARE @TABLE_NAME sysname
  124. ��������DECLARE @column_name sysname
  125. ��������DECLARE @table_id INT
  126. ��������DECLARE @prev_table_id INT
  127.  
  128. ��������DECLARE @previous_schema_name sysname
  129. ��������DECLARE @previous_table_name sysname
  130.  
  131. ��������SET @table_id = 0
  132. ��������SET @prev_table_id = -1
  133.  
  134. ��������while @table_id >= 0
  135. ��������BEGIN
  136.  
  137. ������������SET @table_id = NULL
  138. ��������
  139. ������������SELECT top 1
  140. ����������������@table_id = table_id,
  141. ����������������@schema_name = schema_name,
  142. ����������������@TABLE_NAME = TABLE_NAME,
  143. ����������������@column_name = column_name
  144. ������������FROM
  145. ����������������#temp_columns_table
  146. ������������WHERE
  147. ����������������mod_flag <> 0
  148. ������������ORDER BY
  149. ����������������schema_name,
  150. ����������������TABLE_NAME,����
  151. ����������������column_name
  152.  
  153. ������������IF @table_id IS NULL
  154. ����������������SET @table_id = -1
  155.  
  156. ������������IF @table_id <> @prev_table_id
  157. ������������BEGIN
  158. ����������������-------------------------------------------------------------------------------------
  159. ����������������-- execute previous command
  160.  
  161. ����������������IF @prev_table_id > 0
  162. ����������������BEGIN
  163. ��������������������SET @n_thi_cmd = 'if exists (' + @n_sec_cmd + ') ' + CHAR(13)
  164. ������������������������+ 'begin ' + CHAR(13)
  165. ������������������������+ 'select '''
  166. ������������������������+ @current_db_name + ''' as database_name, '''
  167. ������������������������+ @previous_schema_name + ''' as schema_name, '''
  168. ������������������������+ @previous_table_name + ''' as table_name'
  169. ������������������������+ CHAR(13)
  170. ������������������������+ @n_sec_cmd + CHAR(13) + 'end'
  171.  
  172. ��������������������print @n_thi_cmd
  173.  
  174. ��������������������EXEC sp_executesql @n_thi_cmd
  175. ����������������END
  176.  
  177. ����������������SET @n_sec_cmd = 'select * from [' + @current_db_name + '].[' + @schema_name + '].[' + @TABLE_NAME + '] where ([' + @column_name + '] is not null and [' + @column_name + '] like ''%' + @text + '%'') '
  178. ������������END
  179. ������������ELSE
  180. ������������BEGIN
  181. ����������������SET @n_sec_cmd = @n_sec_cmd + CHAR(13) + ' or ([' + @column_name + '] is not null and replace(upper(cast([' + @column_name + '] as varchar(64))), ''-'', '''' ) like ''%' + @text + '%'') '
  182. ������������END
  183.  
  184. ������������SET @prev_table_id = @table_id
  185.  
  186. ������������UPDATE #temp_columns_table SET mod_flag = 0 WHERE table_id = @table_id AND column_name = @column_name
  187.  
  188. ������������SET @previous_schema_name = @schema_name
  189. ������������SET @previous_table_name = @TABLE_NAME
  190. ��������END
  191.  
  192. ��������DELETE FROM #temp_dbs_table WHERE db_name = @current_db_name
  193. ����END
  194.  
  195. ����---------------------------------------------------------------------------------
  196. ����-- cleanup
  197.  
  198. ����DROP TABLE #temp_columns_table
  199.  
  200. ����DROP TABLE #temp_dbs_table
  201.  
  202. END
  203. GO
  204.  
  205. -- examples:
  206.  
  207. -- exec dbo.proc_search_for_text 'Nathan'
  208.  
  209. -- exec dbo.proc_search_for_text 'Everett', 'AdventureWorks'

URL: http://www.sqlservercentral.com/scripts/SQL+Server+2005/62066/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.