SQL-Ex blog

Теперь давайте опишем метаданные в контексте SQL Server. SQL Server хранит данные, и эти данные служат различным бизнес-целям. Помимо этого в SQL Server имеется информация об этих данных, и эта информация называется метаданными. SQL Server предоставляет несколько системных функций, которые вернут метаданные. В SQL Server имеется более 30 функций метаданных.

Здесь рассматриваются следующие функции метаданных:

• SERVERPROPERTY


• DATABASEPROPERTYEX


• DB_NAME и DB_ID


• FILE_NAME, FILE_ID и FILE_IDEX


• SCHEMA_NAME и SCHEMA_ID


• OBJECT_NAME и OBJECT_ID


• STATS_DATE

Больше обо всех функциях метаданных можно почитать здесь.

Функция SERVERPROPERTY

Давайте начнем с одной из наиболее популярных функций метаданных, которые большинство профессионалов SQL Server используют, чтобы получить подробную информацию о версии SQL Server. Эта функция SERVERPROPERTY очень популярна среди администраторов и разработчиков, она позволяет получить различные свойства экземпляра SQL Server. Она возвращает такую информацию, как имя сервера, редакцию SQL Server, версию продукта, является ли он кластеризованным и т.д. Есть еще одна статья, которая более подробно описывает функцию SERVERPROPERTY.

Ниже приведен пример этой функции, возвращающей различные характеристки.

SELECT 

      SERVERPROPERTY('MachineName') AS ComputerName,

      SERVERPROPERTY('ServerName') AS InstanceName,

      SERVERPROPERTY('Edition') AS Edition,

      SERVERPROPERTY('ProductVersion') AS ProductVersion,

      SERVERPROPERTY('ProductLevel') AS ProductLevel,

      SERVERPROPERTY('Collation') AS Collation,

      SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,

      SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition,

      SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,

      SERVERPROPERTY('IsClustered') AS IsClustered,

      SERVERPROPERTY('IsBigDataCluster') AS IsBigDataCluster,

      SERVERPROPERTY('InstanceDefaultDataPath') AS InstanceDefaultDataPath,

      SERVERPROPERTY('InstanceDefaultLogPath') AS InstanceDefaultLogPath,

      SERVERPROPERTY('InstanceDefaultBackupPath') AS InstanceDefaultBackupPath

GO

Вывод представляет следующую информацию.

Функция DATABASEPROPERTYEX

Функция DATABASEPROPERTYEX возвращает информацию уровня базы данных, включая такие свойства базы данных, как восстановление, статус, конфигурацию автосжатия, репликацию и т.д. Нам потребуется указать имя базы данных, а также название свойства.

Этот запрос T-SQL даст модель восстановления, текущий статус базы данных и пользовательский доступ к указанной базе данных (TESTDB).

SELECT  

      DATABASEPROPERTYEX('TESTDB', 'RECOVERY') AS [Recovery Model],

      DATABASEPROPERTYEX('TESTDB', 'Status') AS [DB Status],

      DATABASEPROPERTYEX('TESTDB', 'UserAccess') AS [UserAccess];

GO

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

Функции DB_NAME и DB_ID

Функции DB_NAME и DB_ID используются для получения имени базы данных по ID базы данных или ID базы данных по имени. Если вы не укажете ID или имя базы данных, выходные данные будут содержать информацию о базе данных, для которой выполнялся запрос.

В примере ниже показывается, как указать ID пользовательской базы данных, чтобы вернуть ее имя, и, напротив, указать имя базы данных, чтобы получить ее ID.

USE TESTDB

GO

SELECT DB_NAME() AS [DB Name], DB_ID() AS [DB ID]

GO

SELECT DB_NAME(7) AS [DB Name], DB_ID('TESTDB') AS [DB ID]

Результат обоих операторов одинаков.



Мы можем использовать эту функцию в сочетании с другими функциями метаданных. Здесь я использовал вместе функции DB_NAME и DATABASEPROPERTYEX, чтобы получить информацию о базе данных TESTDB.

USE TESTDB

GO

SELECT 

      DB_NAME() AS [DB Name], 

      DATABASEPROPERTYEX(DB_NAME(), 'RECOVERY') AS [Recovery Model],

      DATABASEPROPERTYEX(DB_NAME(), 'Status') AS [DB Status],

      DATABASEPROPERTYEX(DB_NAME(), 'UserAccess') AS [UserAccess],

      DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS [Collation]

GO

На выходе имеем полезную информацию о пользовательской базе данных TESTDB, а именно, что она находится в состоянии ONLINE, открыта для многопользовательского доступа (MULTI_USERS), имеет полную модель восстановления и коллацию SQL_Latin1_General_CP1_CI_AS.

Функции FILE_NAME, FILE_ID и FILE_IDEX

Следующие функции метаданных имеют отношение к информации уровня файла:

• FILE_NAME


• FILE_ID


• FILE_IDEX

Функция FILE_NAME возвращает логическое имя файла для заданного ID файла базы данных, в то время как функция FILE_ID вернет ID логического файла для указанного имени файла базы данных.

Microsoft ввела новую функцию с именем FILE_IDEX, которая может вернуть ID логического файла по указанному имени файла базы данных, поскольку функцию FILE_ID планируют удалить в будущих версиях SQL Server. Вам следует использовать FILE_IDEX при разработке вместо функции FILE_ID на будущее.

Этот пример выполняет sp_helpdb на пользовательской базе данных TESTDB, чтобы вернуть всю информацию уровня файла. Мы можем увидеть имена файлов, ID и другие подробности в выводе процедуры. Затем я буду использовать ID из этого вывода в функции FILE_NAME. Я укажу ID файла в этой функции и получу имя файла базы данных.

EXEC sp_helpdb 'TESTDB'

GO

SELECT FILE_NAME(1) AS 'File Name 1',

       FILE_NAME(2) AS 'File Name 2';

GO

Посмотрите вывод. Функция FILE_NAME вернула тот же результат, что и sp_helpdb.



Вот еще один пример.

USE TESTDB

GO

SELECT FILE_ID('TESTDB')AS [Data File ID],

       FILE_ID('TESTDB_log') AS [Log File ID]

GO

USE TESTDB;

GO

SELECT FILE_IDEX('TESTDB') AS [Data File ID],

       FILE_IDEX('TESTDB_log') AS [Log File ID];

GO

Результат этого запроса приводится ниже. Проверьте вывод и сравните его с выводом sp_helpdb выше.

Функции SCHEMA_NAME и SCHEMA_ID

SCHEMA_NAME и SCHEMA_ID используются для получения имени и ID для заданного имени схемы или ID схемы. Если обратиться к sys.schemas, вы получите имя схемы и ее ID.

Вот пример.

SELECT SCHEMA_NAME() AS [Schema Name],     

       SCHEMA_NAME(1) AS [Schema Name using ID],

       SCHEMA_NAME(2) AS [Schema Name using ID],

       SCHEMA_NAME(3) AS [Schema Name using ID]

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



Подобным образом имена этих схем были скопированы из результатов и вставлены в функцию SCHEMA_ID, чтобы получить их ID с помощью следующего запроса.

SELECT SCHEMA_ID() AS [Schema ID],

       SCHEMA_ID('dbo') AS [Schema ID using Name],

       SCHEMA_ID('guest') AS [Schema ID using Name],

       SCHEMA_ID('INFORMATION_SCHEMA') AS [Schema ID using Name]

Ниже для каждой схемы возвращается ее ID.

Функции OBJECT_NAME и OBJECT_ID

Здесь мы покажем, как получить имя объекта, связанного с ID объекта и, наоборот, с помощью OBJECT_NAME и OBJECT_ID.

Чтобы получить ID объекта, в функции OBJECT_ID нужно указать имя объекта, а чтобы получить имя объекта, в функции OBJECT_NAME следует указать ID объекта.

В примере ниже имя таблицы OrderDetails из базы данных TESTDB используется для получения ID этой таблицы. Также мы получаем имя с помощью OBJECT_ID.

USE TESTDB

SELECT OBJECT_ID('TESTDB.dbo.OrderDetails') AS [ObjectID]

GO

SELECT OBJECT_NAME(901578250) AS [ObjectName]

Вы можете увидеть результаты ниже.

Функция STATS_DATE

STATS_DATE вернет дату последнего обновления статистики для таблицы или индексированного представления по object_id и stats_id.

SELECT object_id, name, stats_id FROM sys.stats

WHERE object_id = 901578250GO

SELECT STATS_DATE(901578250, 2) AS [Last stats update date]

Вышеприведенный запрос возвращает список всех имен статистики и их идентификаторы для этого объекта, а функция показывает, когда обновлялась статистика с ID 2.



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

USE TESTDB

SELECT name AS StatsName,

       STATS_DATE(object_id, stats_id) AS [Latest stats update date]

FROM sys.stats

WHERE object_id = OBJECT_ID('dbo.OrderDetails');

GO

Здесь вы можете увидеть всю статистику и соответствующие даты ее обновления.