Как найти таблицы с определенными именами столбцов

Чтобы найти таблицы, содержащие столбцы с указанным шаблоном, например ‘%myName%’, можно использовать SQL-запросы, которые используют системные представления и таблицы метаданных. Наиболее распространенный подход заключается в запросе представления INFORMATION_SCHEMA.COLUMNS или специфичных для базы данных системных таблиц, таких как sys.columns в SQL Server, с использованием оператора LIKE с символами подстановки для сопоставления частичных имен столбцов. Этот метод работает в различных системах управления базами данных с небольшими вариациями синтаксиса.

Содержание

• Решения для SQL Server
• Стандартный подход, работающий с разными СУБД
• Решение для Oracle Database
• Варианты для MySQL и PostgreSQL
• Советы по оптимизации производительности
• Практические примеры

Решения для SQL Server

В SQL Server существует несколько способов найти таблицы со столбцами, соответствующими указанному шаблону. Наиболее прямой подход использует представление INFORMATION_SCHEMA.COLUMNS:

SELECT TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%myName%';

Для получения более подробных результатов, включая информацию о схеме, можно использовать системные таблицы:

SELECT t.name AS table_name, 
       SCHEMA_NAME(schema_id) AS schema_name, 
       c.name AS column_name
FROM sys.tables AS t 
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID 
WHERE c.name LIKE '%myName%'
ORDER BY schema_name, table_name;

Согласно руководству IntelliPaat, подход с использованием sys.tables и sys.columns обычно работает лучше, чем INFORMATION_SCHEMA для метаданных-запросов в SQL Server, хотя оба эффективны для поиска шаблонов в столбцах.

Стандартный подход, работающий с разными СУБД

Представление INFORMATION_SCHEMA.COLUMNS предоставляет стандартизированный способ запроса метаданных столбцов в различных системах управления базами данных, поддерживающих стандарты SQL:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%';

Этот подход последовательно работает в:

• SQL Server
• MySQL
• PostgreSQL
• Других базах данных, соответствующих ANSI SQL

Как упоминается в руководстве по базам данных Atlassian, этот метод предоставляет простой способ найти все таблицы и связанные столбцы с определенным или похожим шаблоном имени столбца.

Решение для Oracle Database

Oracle использует собственные системные таблицы для запросов метаданных. Вот как найти таблицы со столбцами, соответствующими вашему шаблону:

SELECT t.owner AS schema_name, 
       t.table_name
FROM sys.dba_tab_columns col 
INNER JOIN sys.dba_tables t ON col.owner = t.owner 
                           AND col.table_name = t.table_name
WHERE col.column_name LIKE '%myName%'
-- Исключаем системные схемы
AND col.owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'DBSNMP', 
                      'EXFSYS', 'LBACSYS', 'MDSYS', 'MGMT_VIEW', 
                      'OLAPSYS', 'ORDPLUGINS', 'ORDSYS', 'OUTLN',
                      'SI_INFORMTN_SCHEMA', 'WKSYS', 'WMSYS', 'XDB')
ORDER BY col.owner, col.table_name;

В ресурсе Запросы словаря данных Oracle подчеркивается важность исключения поддерживаемых Oracle схем для избежания нерелевантных системных таблиц в результатах.

Варианты для MySQL и PostgreSQL

MySQL поддерживает как INFORMATION_SCHEMA, так и собственные системные таблицы:

-- Использование INFORMATION_SCHEMA (стандарт)
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%';

-- Альтернатива для MySQL с использованием information_schema
SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%';

PostgreSQL предлагает похожие подходы с некоторыми специфичными для PostgreSQL возможностями:

-- Стандартный подход
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%myName%';

-- Специфичный для PostgreSQL с использованием pg_catalog
SELECT n.nspname as schema_name,
       c.relname as table_name,
       a.attname as column_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE a.attname LIKE '%myName%'
AND NOT a.attisdropped
AND c.relkind = 'r';

Советы по оптимизации производительности

При запросах к таблицам метаданных для поиска шаблонов в столбцах, рассмотрите эти стратегии производительности:

1. Используйте системные таблицы для лучшей производительности: Как отмечено в руководстве IntelliPaat, sys.columns и sys.tables обычно работают лучше, чем INFORMATION_SCHEMA для метаданных-запросов в SQL Server.

2. Ограничьте область поиска: По возможности указывайте базу данных или схему для сокращения пространства поиска:

   sql

   -- SQL Server
   SELECT TABLE_NAME, COLUMN_NAME 
   FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_CATALOG = 'YourDatabaseName'
   AND COLUMN_NAME LIKE '%myName%';
   

3. Рассмотрите индексацию: В некоторых базах данных возможно индексирование таблиц метаданных для более быстрых запросов.

4. Кэшируйте результаты: Поскольку информация о схеме редко меняется, рассмотрите возможность кэширования результатов запросов к метаданным.

Практические примеры

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

Поиск всех таблиц со столбцами, заканчивающимися на ‘ID’:

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ID%';

Поиск таблиц со столбцами, содержащими ‘metadata’ (без учета регистра):

-- SQL Server
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE LOWER(COLUMN_NAME) LIKE '%metadata%';

-- PostgreSQL
SELECT table_name, column_name
FROM information_schema.columns
WHERE column_name ILIKE '%metadata%';

Получение количества совпадающих столбцов на таблицу:

SELECT TABLE_NAME, COUNT(*) as column_count
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myName%'
GROUP BY TABLE_NAME
ORDER BY column_count DESC;

Как демонстрирует сообщество Stack Overflow, эти запросы “просты как дважды два” и широко используются разработчиками для поиска всех экземпляров имен столбцов в данной схеме базы данных.

Источники

1. SQL - Find all Tables Containing Column with Specified Name - IntelliPaat
2. How to find all tables containing a column with the specified name in MS SQL Server - Sentry
3. Find tables with a specific column name in Oracle database - Dataedo
4. SQL Tutorial: Identifying Tables within a Column - Atlassian
5. SQL - Find all tables containing column with specified name - Stack Overflow
6. How to write a query to find all tables in a db that have a specific column name - Database Administrators Stack Exchange

Заключение

Поиск таблиц со столбцами, соответствующими указанному шаблону, например ‘%myName%’, является распространенной задачей администрирования баз данных, которую можно решить несколькими способами. Представление INFORMATION_SCHEMA.COLUMNS предоставляет стандартизированный метод для разных SQL-баз данных, в то время как специфичные для системы таблицы, такие как sys.columns в SQL Server и dba_tab_columns в Oracle, предлагают более подробные метаданные с потенциально лучшей производительностью. Всегда учитывайте фильтрацию системных схем и баз данных при работе в производственных средах для получения релевантных результатов. Для сложных баз данных кэширование результатов запросов к метаданным или использование специализированных инструментов, таких как Devart, может повысить эффективность.