Как найти все таблицы с колонкой по имени в 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% на название столбца, который вам потребен. Этот код поможет вам быстро и эффективно выполнить поставленную задачу.

Уточнение поиска с принятием во внимание схемы

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

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;

Полезные материалы

1. sys.columns (Transact-SQL) – SQL Server | Microsoft Docs — Описание sys.columns в официальной документации.
2. SQL Search – Find SQL fast in SQL Server Management Studio and Visual Studio 2017 — Инструмент от Redgate для быстрого поиска SQL объектов.
3. TSQL: How to add a char to a select statement – Stack Overflow — Обсуждение на Stackoverflow с полезными советами по TSQL.
4. SQL Server – Find Column used in Stored Procedure – Search Stored Procedures for Text — Способ найти использование столбца в хранимых процедурах на SQLAuthority.com.
5. Finding a Needle in a Haystack: Search for Database Objects in SQL Server — Если вам интересно, как искать объекты в базе данных, этот материал может быть полезным!