SQL. Search for any text or integer in the database

Small queries to search for any number or text in the desired MS SQL database.

Find an integer.

1. set nocount on
2. declare @name varchar(128), @substr bigint, @column varchar(128)
3. set @substr = 462083 -- ИСКОМОЕ ЦЕЛОЕ ЧИСЛОВОЕ ЗНАЧЕНИЕ
4. /* Create a table for output */
5. create table #rslt2
6. (table_name varchar(128), field_name varchar(128), value bigint)
7. /* Populate the created table with the found data */
8. declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
9. open s
10. fetch next from s into @name
11. while @@fetch_status = 0
12. begin
13. declare c cursor for
14.       select quotename(column_name) as column_name from information_schema.columns
15.            where data_type in ('int', 'bigint') and table_name = @name
16. set @name = quotename(@name)
17. open c
18. fetch next from c into @column
19. while @@fetch_status = 0
20. begin
21.       print 'Processing table - ' + @name + ', column - ' + @column
22.       exec('insert into #rslt2 select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column +
23.            ' from' + @name + ' where ' + @column + ' = ' + @substr )
24. fetch next from c into @column
25. end
26. close c
27. deallocate c
28. fetch next from s into @name
29. end
30. select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt2
31. group by table_name, field_name
32. order by table_name, field_name
33. /* Display found values ​​if needed (uncomment next line) */
34. -- select * from #rslt2 order by table_name, field_name
35. drop table #rslt2
36. close s
37. deallocate s

Wyszukaj ciąg znaków.

1. set nocount on
2. declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
3. set @substr = '%Пружинкин%' -- ФРАГМЕНТ ИСКОМОЙ СТРОКИ
4. /* Create a table for output */
5. create table #rslt
6. (table_name varchar(128), field_name varchar(128), value ntext)
7. /* Populate the created table with the found data */
8. declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
9. open s
10. fetch next from s into @name
11. while @@fetch_status = 0
12. begin
13. declare c cursor for
14.       select quotename(column_name) as column_name from information_schema.columns
15.            where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname') and table_name = @name
16. set @name = quotename(@name)
17. open c
18. fetch next from c into @column
19. while @@fetch_status = 0
20. begin
21.       print 'Processing table - ' + @name + ', column - ' + @column
22.       exec('insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column +
23.            ' from' + @name + ' where ' + @column + ' like ''' + @substr + '''')
24.       fetch next from c into @column
25. end
26. close c
27. deallocate c
28. fetch next from s into @name
29. end
30. select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
31. group by table_name, field_name
32. order by table_name, field_name
33. /* Display found values ​​if needed (uncomment next line) */
34. --select * from #rslt order by table_name, field_name
35. drop table #rslt
36. close s
37. deallocate s