SQL_scripts

1

2
set nocount on
3

4
declare
5
	@srch_like varchar(100) = '%search%' -- текст для поиска
6
	,@col_like varchar(max) = '%column%' -- пусто или перечень колонок в которых искать 
7
	,@tbl_like varchar(max) = '%table%' -- пусто или перечень таблиц в которых искать
8
	,@row_cnt_less int = 150 -- искать в таблицах в которых количество строк <= значению
9

10

11
select
12
	@srch_like = isnull(ltrim(rtrim(@srch_like)), '')
13
	,@col_like = isnull(ltrim(rtrim(@col_like)), '')
14
	,@tbl_like = isnull(ltrim(rtrim(@tbl_like)), '')
15

16
if (ltrim(@srch_like) = '')
17
	throw 50000, '@srch_like не задан!', 1
18

19
drop table if exists #t1
20

21
select
22
	identity(int) as id
23
	,quotename(schema_name(t.schema_id)) + '.' + quotename(t.[name]) as Таблица
24
	,p.[rows] as Всего_строк
25
	,quotename(c.[name]) as Колонка
26
	--,tp.[name] as Тип
27
	,0 as Найдено
28
into #t1
29
from
30
	sys.tables as t (nolock)
31
	inner join	sys.indexes as i (nolock)
32
		on i.object_id = t.object_id
33
	inner join sys.columns as c (nolock)
34
		on c.object_id = t.object_id
35
	inner join sys.types as tp
36
		on c.user_type_id = tp.user_type_id
37
	inner join (
38
		select
39
			object_id
40
			,index_id
41
			,sum([rows]) as [rows]
42
		from sys.partitions (nolock)
43
		group by
44
			object_id
45
			,index_id
46
		having (((@row_cnt_less > 0) and (sum([rows]) between 1 and @row_cnt_less)) or (@row_cnt_less < 1))
47
	) as p
48
		on p.object_id = i.object_id
49
		and p.index_id = i.index_id
50
where 1=1
51
	and t.is_ms_shipped = 0
52
	and i.[type] in (0,1)
53
	and ((@tbl_like <> '' and t.[name] like @tbl_like) or (@tbl_like = ''))
54
	and ((@col_like <> '' and c.[name] like @col_like) or (@col_like = ''))
55
	and tp.[name] in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'nchar', 'sysname')
56
order by t.[name]
57
option (maxdop 8)	
58

59
declare
60
	@id int
61
	,@cnt int = 0
62
	,@query nvarchar(max) = ''
63
	,@max_id int = (select max(id) from #t1)
64

65
declare curs cursor for
66
	select
67
		id
68
		,'select @cnt = count(*) from ' + Таблица + ' (nolock) where ' + Колонка + ' like ''' + @srch_like + ''''
69
	from #t1
70
	order by id
71
open curs
72
fetch next from curs into @id, @query
73
while @@fetch_status = 0
74
begin
75
	
76
	exec sys.sp_executesql
77
		@query
78
		,N'@cnt int out'
79
		,@cnt = @cnt out
80

81
	update #t1 set
82
		Найдено = @cnt
83
	where id = @id
84

85
	raiserror('[%d/%d]', 10, 1, @id, @max_id) with nowait
86

87
fetch next from curs into @id, @query
88
end
89
close curs
90
deallocate curs
91

92
select
93
	db_name() as База
94
	,Таблица
95
	,Всего_строк
96
	,Колонка
97
	,Найдено
98
	--,'select ' + Колонка + ' from ' + Таблица + ' (nolock) where ' + Колонка + ' like ''' + @srch_like + '''' as Запрос
99
from #t1
100
where Найдено > 0
101
order by 1,3
102

103

104