Posted By

ezerick on 03/03/11


Tagged

database search tsql


Versions (?)

Who likes this?

1 person have marked this snippet as a favorite

Beppoi


Search for a text in all databases - SQLServerCentral


 / Published in: SQL
 

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

  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'

Report this snippet  

You need to login to post a comment.