INFORMATICS

The Best

SQL. Search for any text or integer in the database

Inicio desactivadoInicio desactivadoInicio desactivadoInicio desactivadoInicio desactivado
 

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

 

 

Search