Поиск всех колонок с определенным типом в 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 — обсуждения и решения сообщества программистов на тему запросов к структуре баз данных.