Как найти все таблицы с колонкой по имени в SQL Server
Быстрый ответ
Если вам требуется отыскать все таблицы, содержащие столбец с указанным именем, можно использовать следующий рецепт SQL:
SELECT t.name AS TableName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%'
Не забудьте заменить %myColumn%
на название столбца, который вам потребен. Этот код поможет вам быстро и эффективно выполнить поставленную задачу.
[AsideBanner]
Уточнение поиска с принятием во внимание схемы
Время от времени полезно получить не только названия таблиц, но и подробное описание схемы. Для этих целей можно воспользоваться следующим запросом:
SELECT SCHEMA_NAME(t.schema_id) + '.' + t.name AS FullTableName, c.name AS ColumnName
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%'
ORDER BY FullTableName, ColumnName;
Расширение диапазона: применение запроса представлений через использование INFORMATION_SCHEMA
Если вам надо углубленное изучение и таблиц, и представлений, то вам может прийти на помощь INFORMATION_SCHEMA.COLUMNS:
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%myColumn%'
ORDER BY TABLE_NAME, COLUMN_NAME;
Заметьте, что этот метод хорошо справляется в разных СУБД и на различных SQL базах данных, поэтому его можно считать универсальным средством в вашем наборе инструментов SQL.
Анализ INFORMATION_SCHEMA
Нюансы детального анализа
Вы можете сконфигурировать свои запросы таким образом, чтобы выводились не только основные данные, но и более специфическая информация, например, типы данных или требования по заполнению полей:
SELECT
C.TABLE_SCHEMA,
C.TABLE_NAME,
C.COLUMN_NAME,
C.DATA_TYPE,
C.CHARACTER_MAXIMUM_LENGTH,
C.IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE C.COLUMN_NAME LIKE '%myColumn%'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME;
Выбор инструментария работы с базами данных
Не смотря на то, что sys.tables
и sys.columns
являются составной частью SQL Server, INFORMATION_SCHEMA
представляет собой универсальное решение, применимое в любой ситуации.
Исследование возможностей метаданных системных представлений
Для доступа к метаданным, вроде даты изменения или статусов идентификаторов, используйте системные представления в SQL Server:
SELECT
t.name AS TableName,
c.name AS ColumnName,
t.create_date,
t.modify_date,
c.is_identity,
c.is_computed
FROM sys.tables AS t
INNER JOIN sys.columns AS c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%'
ORDER BY TableName, ColumnName;
Анализ не пустых колонок: Когда NULL просто не подходит
Если вас интересуют поле, которые должны быть обязательно заполнены, можете воспользоваться следующим запросом:
SELECT t.name AS TableName, c.name AS ColumnName
FROM sys.tables AS t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%' AND c.is_nullable = 0
ORDER BY TableName, ColumnName;
Визуализация
Предположим, вам надо найти определенный объект (столбец) среди множества разнокалиберных контейнеров (таблиц) в вашем Microsoft SQL Server:
🔎: Ваша цель = "Гаечный ключ" (имя столбца)
SQL-запрос – это ваш инструмент, который поможет вам составить всю картину целиком:
SELECT table_name
FROM information_schema.columns
WHERE column_name = 'Wrench';
Вот что у вас выйдет на выходе:
До: [🗄, 🗄, 🗄]
После: [❌, ✅ (Ура, мы нашли гаечный ключ!), ❌]
Результат: Теперь у вас есть список всех контейнеров, в которых обнаруживается нужный вам объект.
Углубленный ответ: Исследование поглубже обычного
Точность на долю миллиметра с точными совпадениями
Возможно, вы ищете точное совпадение имен, а не примеры по шаблону. У нас есть для этого решение!
SELECT t.name AS TableName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'ExactColumnName'
Поиск с учетом схемы
Может быть полезно расширить радиус поиска и включить различные схемы, используя sys.schemas
:
SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.tables t
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name LIKE '%myColumn%'
ORDER BY SchemaName, TableName, ColumnName;
Приоритизация результатов поиска
Иногда не все колонки равноценны, и было бы хорошо, чтобы некоторые из них были в верхней части вашего списка. В этом могут помочь условные конструкции CASE
:
SELECT t.name AS TableName, c.name AS ColumnName,
CASE WHEN c.name = 'PrimaryInterest' THEN 1 ELSE 2 END AS Priority
FROM sys.tables AS t
JOIN sys.columns AS c ON t.object_id = c.object_id
WHERE c.name LIKE '%myColumn%'
ORDER BY Priority, TableName, ColumnName;
Полезные материалы
- sys.columns (Transact-SQL) – SQL Server | Microsoft Docs — Описание
sys.columns
в официальной документации. - SQL Search – Find SQL fast in SQL Server Management Studio and Visual Studio 2017 — Инструмент от Redgate для быстрого поиска SQL объектов.
- TSQL: How to add a char to a select statement – Stack Overflow — Обсуждение на Stackoverflow с полезными советами по TSQL.
- SQL Server – Find Column used in Stored Procedure – Search Stored Procedures for Text — Способ найти использование столбца в хранимых процедурах на SQLAuthority.com.
- Finding a Needle in a Haystack: Search for Database Objects in SQL Server — Если вам интересно, как искать объекты в базе данных, этот материал может быть полезным!