Как найти таблицу по полям

I’m using PostgreSQL 9.1. I have the column name of a table. Is it possible to find the table(s) that has/have this column? If so, how?

jkdev's user avatar

jkdev

11.2k15 gold badges54 silver badges77 bronze badges

asked Aug 29, 2013 at 10:25

QuestionEverything's user avatar

You can also do

 select table_name from information_schema.columns where column_name = 'your_column_name'

Chankey Pathak's user avatar

answered Jul 30, 2015 at 6:58

Ravi Shekhar's user avatar

Ravi ShekharRavi Shekhar

2,5874 gold badges19 silver badges19 bronze badges

3

you can query system catalogs:

select c.relname
from pg_class as c
    inner join pg_attribute as a on a.attrelid = c.oid
where a.attname = <column name> and c.relkind = 'r'

sql fiddle demo

answered Aug 29, 2013 at 10:39

Roman Pekar's user avatar

Roman PekarRoman Pekar

106k28 gold badges192 silver badges196 bronze badges

3

I’ve used the query of @Roman Pekar as a base and added schema name (relevant in my case)

select n.nspname as schema ,c.relname
    from pg_class as c
    inner join pg_attribute as a on a.attrelid = c.oid
    inner join pg_namespace as n on c.relnamespace = n.oid
where a.attname = 'id_number' and c.relkind = 'r'

sql fiddle demo

answered Apr 9, 2018 at 6:56

jutky's user avatar

Simply:

$ psql mydatabase -c 'd *' | grep -B10 'mycolname'

Enlarge -B offset to get table name, if need

answered Jul 26, 2019 at 14:11

Dmitry's user avatar

DmitryDmitry

8381 gold badge7 silver badges19 bronze badges

1

Wildcard Support
Find the table schema and table name that contains the string you want to find.

select t.table_schema,
       t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name
                                and c.table_schema = t.table_schema
where c.column_name like '%STRING%'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

answered Oct 29, 2019 at 13:18

jjj's user avatar

jjjjjj

2,5746 gold badges36 silver badges57 bronze badges

select t.table_schema,
       t.table_name
from information_schema.tables t
inner join information_schema.columns c on c.table_name = t.table_name 
                                and c.table_schema = t.table_schema
where c.column_name = 'name_colum'
      and t.table_schema not in ('information_schema', 'pg_catalog')
      and t.table_type = 'BASE TABLE'
order by t.table_schema;

answered Dec 5, 2019 at 20:32

romuloMendes's user avatar

1

Время на прочтение
5 мин

Количество просмотров 61K

Описание общей потребности в поиске данных и объектов в базе данных

Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.

Достаточно часто может возникнуть ситуация, при которой нужно найти:

  1. объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
  2. данные (значение и в какой таблице располагается)
  3. фрагмент кода в определениях объектов базы данных

Существует множество готовых решений как платных, так и бесплатных.

Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.

Поиск с помощью встроенных средств самой СУБД

Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:

Поиск таблицы по имени

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name]='Employee';

Результат может быть примерно такой:

Здесь выводятся:

  1. идентификаторы объекта и схемы, где располагается объект
  2. название этой схемы и название этого объекта
  3. тип объекта и описание этого типа объекта
  4. даты и время создания и последней модификации объекта

Чтобы найти все вхождения строки “Project”, то можно использовать следующий скрипт:

Поиск всех объектов по подстроке в имени

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name] like '%Project%';

Результат может быть примерно такой:

Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.

Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:

Поиск всех объектов по подстроке в имени с выводом родительских объектов

select ao.[object_id], ao.[schema_id],
	   schema_name(ao.[schema_id]) as [schema_name],
	   ao.parent_object_id,
	   p.[schema_id] as [parent_schema_id],
	   schema_name(p.[schema_id]) as [parent_schema_name],
	   p.[name] as [parent_name],
	   ao.[name], 
	   ao.[type], 
	   ao.[type_desc], 
	   ao.[create_date], 
	   ao.[modify_date]
from sys.all_objects as ao
left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
where ao.[name] like '%Project%';

Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:

В запросах используются следующие системные объекты:

  • таблица sys.all_objects
  • скалярная функция schema_name

Итак, разобрали как найти объекты в базе данных с помощью встроенных средств самой СУБД.
Теперь покажем как найти данные в базе данных на примере поиска строк.

Чтобы найти строковое значение по всем таблицам базы данных, можно воспользоваться следующим решением. Упростим данное решение и покажем как можно найти например значение “Ramiro” с помощью следующего скрипта:

Поиск строковых значений по подстроке во всех таблицах базы данных

set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'

declare @sql nvarchar(max);

create table #rslt 
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
 declare c cursor for 
	select quotename(column_name) as column_name from information_schema.columns 
	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name  = @name
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   --print 'Processing table - ' + @name + ', column - ' + @column

   set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column + 
	' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';

	print @sql;

   exec(@sql);

   fetch next from c into @column;
 end
 close c
 deallocate c
 fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name

drop table #rslt
close s
deallocate s

Результат выполнения может быть таким:

Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.

Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:

  1. sys.sql_modules
  2. sys.all_sql_modules
  3. sys.syscomments

Например, используя последнее представление, можно с помощью следующего скрипта найти все объекты, в определениях которых встречается заданный фрагмент кода:

Поиск фрагмента кода в определениях объектов базы данных

select obj.[object_id],
	   obj.[name],
	   obj.[type_desc],
	   sc.[text]
from sys.syscomments as sc
inner join sys.objects obj on sc.[id]=obj.[object_id]
where sc.[text] like '%code snippet%';

Здесь будет выведен идентификатор, название, описание и полное определение объекта.

Поиск с помощью бесплатной утилиты dbForge Search

Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.

Для вызова этой утилиты в окне SSMS нажмите на кнопку .

Появится следующее окно поиска:

Обратите внимание на верхнюю панель (слева направо):

  1. можно переключать режим поиска (ищем DDL (объекты) или данные)
  2. непосредственно что ищем (какую подстроку)
  3. учитывать ли регистр, искать точное соответствие слову, искать вхождения:

  4. группировать результат по типам объектов — кнопка
  5. выбрать нужные типы объектов для поиска:

  6. также можно задать несколько баз данных для поиска и выбрать экземпляр MS SQL Server

Это все в режиме поиска объектов, т е когда включен DDL:

В режиме поиска данных изменится только выбор типов объектов:

А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:

Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:

Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.

При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.

Также можно переместить навигацию на найденный объект, щелкнув на кнопку :

Можно также сгруппировать найденные объекты по их типу:

Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.

Теперь найдем значение “Ramiro” по всем таблицам:

Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.

Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :

Таким образом мы можем искать нужные объекты и данные в базе данных.

Заключение

Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.

Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.

Источники

  • Search_Script.sql
  • SSMS
  • dbForge Search
  • Документация по Microsoft SQL
  • Бесплатные решения от компании Devart

Как найти таблицу по имени поля?

Ivan Neonoff



Мыслитель

(5553),
на голосовании



14 лет назад

Известно имя поля, но имя таблицы неизвестно. Как можно найти таблицу из огромного множества таблиц, в которой есть искомое поле.
Есть sql developer и sql navigator

Голосование за лучший ответ

Serge_M

Мыслитель

(7152)


14 лет назад

select table_name from information_schema.columns
where column_name=’имя столбца’

Ivan NeonoffМыслитель (5553)

14 лет назад

information_schema – так и должно быть или поменять на имя схемы ?
не работает ни так ни эдак..

Serge_M
Мыслитель
(7152)
Именно так:
information_schema.columns

Это стандартное обращение к метаданным. В приведенном запросе вам нужно только подставить свое <имя столбца>.

Можно ли найти таблицы по записи?

Например, найти все таблицы, в которых присутствует фамилия “Петров П.П.” (имя поля также неизвестно).

Или можно, указав имя поля? Допустим, оно во всех неизвестных таблицах одинаковое.

Regent's user avatar

Regent

19.1k6 золотых знаков29 серебряных знаков44 бронзовых знака

задан 17 июл 2015 в 8:38

bulbazavrik's user avatar

3

Чистыми средствами SQL – нет.
SQL может производить поиск только в конкретном поле конкретной таблицы.

Один из выходов – указать вручную список всех таблиц и полей для поиска.

Другой выход – каким-нибудь образом построить этот список. Как уже сказал Petr Abdulin, большинство СУБД предоставляют такую информацию, но каждая по своему.

ответ дан 17 июл 2015 в 9:15

Герман Борисов's user avatar

Герман БорисовГерман Борисов

10.2k13 серебряных знаков37 бронзовых знаков

Теоретически это возможно. Большинство БД позволяюсь получить метаданные по пользовательским базам (в т.ч. название базы, таблицы в базе, столбы в таблице и т.п.) что в свою очередь позволит вам сконструировать динамические запросы к этим таблицам.

Например чтобы получить имя таблиц в базе dbName в БД MS SQL Server:

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'dbName'

то же для MySQL:

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbName'

ответ дан 17 июл 2015 в 8:55

Petr Abdulin's user avatar

Petr AbdulinPetr Abdulin

2,13012 серебряных знаков18 бронзовых знаков

1

Тут надо строить сложный запрос. Выбирать во временные таблицы, например, а потом их вязать, синтаксис которого зависит от СУБД.

Логика такая:

  • выбрать все таблицы пользователя
  • в этих таблицах выбрать все столбцы и их типы
  • делать поиск по столбцам типа char/varchar/nvarchar
  • по результатам поиска совершать необходимые действия, которые зависят как от СУБД, так и от реализации всего предыдущего процесса

Vladimir Glinskikh's user avatar

ответ дан 20 июл 2015 в 14:20

Дмитрий Рой's user avatar

Если вы избегаете stored procedures, как чума, или не можете выполнить mysql_dump из-за разрешений или столкнуться с другими причинами.

Я бы предложил трехэтапный подход:

1) Если этот запрос создает набор запросов в виде набора результатов.

# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER 
# ** USE AN ALTERNATE BACKUP **

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' LIKE '%stuff%';') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     
        (
            A.DATA_TYPE LIKE '%text%'
        OR  
            A.DATA_TYPE LIKE '%char%'
        )
;

.

# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' IN ('%1234567890%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;

.

# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT 
    CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN ('%someText%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE LIKE '%blob%'
;

Результаты должны выглядеть следующим образом:

Изображение 861

2) Затем вы можете просто Right Click и использовать Copy Row (tab separated)

Изображение 862

3) Вставить результаты в новое окно запроса и запустить контент вашего сердца.

Подробно: я исключаю системную схему, которую вы обычно не видите в своем рабочем месте, если у вас нет опции Show Metadata and Internal Schemas.

Я сделал это, чтобы быстро обеспечить ANALYZE весь HOST или DB, если это необходимо, или для выполнения операторов OPTIMIZE для поддержки повышения производительности.

Я уверен, что есть разные способы сделать это, но это то, что работает для меня:

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

Протестировано в MySQL Версия: 5.6.23

ПРЕДУПРЕЖДЕНИЕ: НЕ ИСПОЛЬЗУЙТЕ ЭТО, ЕСЛИ:

  • Вы беспокоитесь о том, чтобы вызвать Столбцы (следите за своими клиентскими подключениями).
  • Вы не знаете, что вы делаете.

  • Вы пытаетесь возмутить вас администратором базы данных. (у вас могут быть люди за вашим столом с быстротой.)

Cheers, Jay; -]

Добавить комментарий