Как получить все имена таблиц в SQL Server

Содержание

• Использование представления sys.tables
• Альтернативные методы
• Сравнение производительности
• Расширенные варианты фильтрации
• Лучшие практики

Использование представления sys.tables

Представление sys.tables предоставляет наиболее эффективный и полный метод для получения имен таблиц в SQL Server. Это представление содержит строку для каждой пользовательской таблицы, внутренней таблицы и системной таблицы в базе данных.

Базовый запрос для всех таблиц

SELECT 
    s.name AS SchemaName,
    t.name AS TableName
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
ORDER BY 
    s.name, t.name;

Запрос только для пользовательских таблиц

Чтобы исключить системные таблицы и объекты, поставляемые Microsoft:

SELECT 
    s.name AS SchemaName,
    t.name AS TableName
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.is_ms_shipped = 0
    AND t.type = 'U'  -- U = User Table (Пользовательская таблица)
ORDER BY 
    s.name, t.name;

Этот метод высокоэффективен, поскольку он напрямую обращается к оптимизированным представлениям системного каталога, которые SQL Server использует внутренне для управления метаданными.

Альтернативные методы

sys.objects с фильтром по типу

Для более гибкой фильтрации можно использовать sys.objects с указанием типов:

SELECT 
    s.name AS SchemaName,
    o.name AS ObjectName,
    o.type_desc AS ObjectType
FROM 
    sys.objects o
INNER JOIN 
    sys.schemas s ON o.schema_id = s.schema_id
WHERE 
    o.type IN ('U', 'V')  -- U = Table (Таблица), V = View (Представление)
    AND o.is_ms_shipped = 0
ORDER BY 
    s.name, o.name;

INFORMATION_SCHEMA.TABLES

Для совместимости со стандартом ANSI:

SELECT 
    TABLE_SCHEMA AS SchemaName,
    TABLE_NAME AS TableName
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_TYPE = 'BASE TABLE'
ORDER BY 
    TABLE_SCHEMA, TABLE_NAME;

Устаревший метод (sysobjects)

Для более старых версий SQL Server (до 2005):

SELECT 
    name
FROM 
    sysobjects
WHERE 
    xtype = 'U'
    AND category = 0;

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

sys.tables против INFORMATION_SCHEMA

На основе результатов исследований, sys.tables значительно эффективнее, чем представления INFORMATION_SCHEMA:

• sys.tables: Прямой доступ к оптимизированным представлениям системного каталога, более быстрое выполнение
• INFORMATION_SCHEMA: Требует дополнительной обработки для сопоставления со стандартной схемой, обычно в 2 раза медленнее
• sys.objects: Аналогичная производительность, что и у sys.tables, но с большей гибкостью для разных типов объектов

Согласно DevOpsSchool.com, “Представления sys обычно быстрее, чем представления INFORMATION_SCHEMA, поскольку они используют внутренние системные таблицы, оптимизированные для производительности.”

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

1. Всегда присоединяйтесь к sys.schemas для получения имен схем вместо вызова функции SCHEMA_NAME()
2. Фильтруйте с помощью is_ms_shipped = 0 для исключения системных таблиц
3. Используйте конкретные коды типов ('U' для таблиц, 'V' для представлений) вместо описаний типов, где это возможно
4. Избегайте использования DISTINCT, если это абсолютно не необходимо, поскольку системные представления обычно возвращают уникальные строки

Расширенные варианты фильтрации

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

-- Таблицы без первичных ключей
SELECT 
    s.name AS SchemaName,
    t.name AS TableName
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    OBJECTPROPERTY(t.object_id, 'TableHasPrimaryKey') = 0
ORDER BY 
    s.name, t.name;

Таблицы с количеством строк

SELECT 
    s.name AS SchemaName,
    t.name AS TableName,
    SUM(p.rows) AS RowCount
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN 
    sys.partitions p ON t.object_id = p.object_id
WHERE 
    t.is_ms_shipped = 0
    AND p.index_id IN (0, 1)  -- Heap or clustered index (Куча или кластеризованный индекс)
GROUP BY 
    s.name, t.name
ORDER BY 
    s.name, t.name;

Запрос между базами данных

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

DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
UNION ALL SELECT 
    ''' + name + ''' AS DatabaseName,
    s.name AS SchemaName,
    t.name AS TableName
FROM [' + name + '].sys.tables t
INNER JOIN [' + name + '].sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0'
FROM sys.databases 
WHERE database_id > 4 
AND state = 0;

SET @sql = STUFF(@sql, 1, 11, '');
EXEC sp_executesql @sql;

Лучшие практики

Рекомендации по выбору метода

1. Для SQL Server 2005+: Используйте sys.tables - наиболее эффективный и полный метод
2. Для соответствия ANSI: Используйте INFORMATION_SCHEMA.TABLES - переносимо между различными системами баз данных
3. Для смешанных типов объектов: Используйте sys.objects с фильтрацией по типу
4. Для устаревших систем: Используйте sysobjects с осторожностью

Вопросы безопасности

• Убедитесь, что у вас есть соответствующие разрешения на доступ к базе данных
• Представления системного каталога соблюдают правила видимости метаданных
• Пользователи могут видеть метаданные только для объектов, которые они владеют или на которые у них есть разрешения

Обслуживание и документирование

• Документируйте, какие методы используют ваши приложения
• Рассмотрите возможность создания хранимых процедур для распространенных запросов метаданных
• Регулярно проверяйте разрешения при запросах к нескольким базам данных

Метод sys.tables остается наиболее эффективным способом для получения имен таблиц в SQL Server, обеспечивая превосходную производительность и предоставляя полную информацию об объектах базы данных.

Источники

1. Microsoft Learn - sys.tables (Transact-SQL)
2. DevOpsSchool - Difference between INFORMATION_SCHEMA and SYS schema in SQL SERVER
3. Stack Overflow - How do I get list of all tables in a database using TSQL?
4. Atlassian - How to Show All Table Servers in SQL
5. My Tec Bits - INFORMATION_SCHEMA.TABLES vs SYS.TABLES in SQL Server
6. SQLBlog.org - The case against INFORMATION_SCHEMA views

Заключение

Получение имен таблиц в SQL Server является простой задачей с наличием нескольких эффективных методов. Представление sys.tables в сочетании с sys.schemas обеспечивает наиболее производительное решение, предоставляя прямой доступ к оптимизированным представлениям системного каталога, исключая системные таблицы с помощью фильтра is_ms_shipped = 0. Для соответствия стандарту ANSI INFORMATION_SCHEMA.TABLES остается жизнеспособной альтернативой, несмотря на несколько более низкую производительность. При реализации этих запросов всегда учитывайте разрешения безопасности, требования к производительности и долгосрочную поддержку для ваших приложений баз данных.