Поиск всех колонок с определенным типом в SQL Server

Быстрый ответ

Если вам нужно получить список всех таблиц и столбцов определённого типа данных, воспользуйтесь следующим SQL-запросом:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE DATA_TYPE = 'your_data_type';

Замените 'your_data_type' на желаемый тип данных, например: 'varchar', 'datetime' или любой другой, поддерживаемый SQL Server. Данный код выдаст имена схем, таблиц и столбцов с максимальной эффэктивностью.

Расширение диапазона поиска

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

SELECT 
    c.TABLE_SCHEMA, 
    c.TABLE_NAME, 
    c.COLUMN_NAME, 
    c.CHARACTER_MAXIMUM_LENGTH, 
    t.name AS Data_Type
FROM 
    INFORMATION_SCHEMA.COLUMNS AS c 
JOIN 
    sys.types AS t ON c.DATA_TYPE = t.name
WHERE 
    t.name = 'your_data_type'
    AND c.TABLE_CATALOG = 'your_database_name';

Проблема производительности

Если размеры ваших баз данных сопоставимы с размерами Амазонского леса, важно обеспечить высокую производительность запросов. Операции с INFORMATION_SCHEMA могут быть медленными, поскольку они обращаются к множеству базовых таблиц. Следите за эффективностью запросов, используя планы выполнения SQL или инструменты мониторинга сервера.

Подробнее об этом расскажет наш спикер на видео

Исключение дубликатов

Чтобы устранить дублированные записи, используйте ключевое слово DISTINCT:

SELECT DISTINCT
    TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    DATA_TYPE = 'your_data_type';

Визуализация

Представьте себе, что таблицы – это деревья в лесу, а вы ищете в них столбец с определённым типом данных, как будто ищете птицу среди ветвей:

🌳🔍🦉🌳🌳🌳      🌳🌳🦉🔍🌳🌳🌳      🌳🌳🌳🔍🦉🌳
| Таблица 1       |      | Таблица 2       |      | Таблица 3       |

Считайте запрос навигатором, который помогает вам отыскать этих птиц среди деревьев-гигантов.

**SQL запрос**: Ищем 🦉 в каждом 🌳.

Поиск с учетом пользовательских типов данных

Если вам необходимо учесть пользовательские типы данных (UDT), для этого предусмотрен специальный запрос:

SELECT 
    SCHEMA_NAME(t.schema_id) as SchemaName,
    OBJECT_NAME(c.object_id) as TableName,
    c.name as ColumnName,
    ty.name as DataType
FROM 
    sys.columns c
INNER JOIN 
    sys.types ty ON c.user_type_id = ty.user_type_id
WHERE 
    ty.name = 'your_udt_name';

Упорядочивание результатов

Чтобы упорядочить результаты и облегчить поиск нужного значения, используйте ORDER BY:

SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.COLUMNS 
WHERE 
    DATA_TYPE = 'your_data_type'
ORDER BY 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME;

Глубокий поиск по характеристикам столбцов

Если вам потребуется осуществить более детальный поиск с учётом максимальной длины, точности и допустимости NULL, добавьте в запрос следующие параметры:

SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME, 
    CHARACTER_MAXIMUM_LENGTH, 
    NUMERIC_PRECISION, 
    IS_NULLABLE 
FROM 
    INFORMATION_SCHEMA.COLUMNS
WHERE 
    DATA_TYPE = 'your_data_type'
ORDER BY 
    TABLE_SCHEMA,
    TABLE_NAME,
    COLUMN_NAME;

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

1. Системные представления информационной схемы (Transact-SQL) — SQL Server — детализированная документация по использованию представлений INFORMATION_SCHEMA от MS SQL.
2. sys.columns (Transact-SQL) — SQL Server — главное руководство по использованию sys.columns для работы со столбцами.
3. Основы системных представлений SQL Server: Простым языком — вводное руководство по системным представлениям SQL Server, изложенное в простом и понятном языке.
4. Обсуждение на форуме SQL Server Central — обсуждения и решения сообщества программистов на тему запросов к структуре баз данных.