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
11.2k15 gold badges54 silver badges77 bronze badges
asked Aug 29, 2013 at 10:25
You can also do
select table_name from information_schema.columns where column_name = 'your_column_name'
answered Jul 30, 2015 at 6:58
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 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
Simply:
$ psql mydatabase -c 'd *' | grep -B10 'mycolname'
Enlarge -B offset to get table name, if need
answered Jul 26, 2019 at 14:11
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
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
1
Время на прочтение
5 мин
Количество просмотров 61K
Описание общей потребности в поиске данных и объектов в базе данных
Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.
Достаточно часто может возникнуть ситуация, при которой нужно найти:
- объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
- данные (значение и в какой таблице располагается)
- фрагмент кода в определениях объектов базы данных
Существует множество готовых решений как платных, так и бесплатных.
Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты 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';
Результат может быть примерно такой:
Здесь выводятся:
- идентификаторы объекта и схемы, где располагается объект
- название этой схемы и название этого объекта
- тип объекта и описание этого типа объекта
- даты и время создания и последней модификации объекта
Чтобы найти все вхождения строки “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”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.
Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:
- sys.sql_modules
- sys.all_sql_modules
- 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 нажмите на кнопку .
Появится следующее окно поиска:
Обратите внимание на верхнюю панель (слева направо):
- можно переключать режим поиска (ищем DDL (объекты) или данные)
- непосредственно что ищем (какую подстроку)
- учитывать ли регистр, искать точное соответствие слову, искать вхождения:
- группировать результат по типам объектов — кнопка
- выбрать нужные типы объектов для поиска:
- также можно задать несколько баз данных для поиска и выбрать экземпляр 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
19.1k6 золотых знаков29 серебряных знаков44 бронзовых знака
задан 17 июл 2015 в 8:38
3
Чистыми средствами SQL – нет.
SQL может производить поиск только в конкретном поле конкретной таблицы.
Один из выходов – указать вручную список всех таблиц и полей для поиска.
Другой выход – каким-нибудь образом построить этот список. Как уже сказал Petr Abdulin, большинство СУБД предоставляют такую информацию, но каждая по своему.
ответ дан 17 июл 2015 в 9:15
Герман БорисовГерман Борисов
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 AbdulinPetr Abdulin
2,13012 серебряных знаков18 бронзовых знаков
1
Тут надо строить сложный запрос. Выбирать во временные таблицы, например, а потом их вязать, синтаксис которого зависит от СУБД
.
Логика такая:
- выбрать все таблицы пользователя
- в этих таблицах выбрать все столбцы и их типы
- делать поиск по столбцам типа
char/varchar/nvarchar
- по результатам поиска совершать необходимые действия, которые зависят как от
СУБД
, так и от реализации всего предыдущего процесса
ответ дан 20 июл 2015 в 14:20
Если вы избегаете 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%'
;
Результаты должны выглядеть следующим образом:
2) Затем вы можете просто Right Click
и использовать Copy Row (tab separated)
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; -]