5 полезных SQL-запросов для инженеров и исследователей данных

Задачи инженеров данных не ограничиваются только манипулированием данными на уровне таблиц с помощью select, update, delete и т.д.. Чаcир требуются исследования и операции на уровне метаданных. Ниже приведены 5 полезных SQL-запросов, которые помогут вам исследовать глубины базы данных с помощью каталогов метаданных SQL Server. Внимание, здесь мы собираемся обсудить все, что относится к SQL Server.

Объекты sys VS объекты INFORMATION_SCHEMA

Прежде всего, давайте разберемся, что такое объекты sys и INFORMATION_SCHEMA, ведь мы будем использовать их очень часто.

Вкратце, объекты INFORMATION_SCHEMA и sys – это каталоги метаданных в SQL Server.

Объекты sys – это исходные метаданные каталога таблиц и представлений, созданных SQL Server. Они включают все важные метаданные, такие как имена таблиц, имена столбцов, типы данных и так далее. Они создаются для сбора метаданных базы данных для использования системой и пользователями.

SELECT OBJECT_DEFINITION(OBJECT_ID('INFORMATION_SCHEMA.TABLES'))   
-- You get
CREATE VIEW INFORMATION_SCHEMA.TABLES  
AS   
SELECT   
  DB_NAME()   AS TABLE_CATALOG,   
  s.name    AS TABLE_SCHEMA,   
  o.name    AS TABLE_NAME,   
  CASE o.type    
    WHEN 'U' THEN 'BASE TABLE'    
    WHEN 'V' THEN 'VIEW'   
  END    AS TABLE_TYPE  
FROM   sys.objects o 
LEFT JOIN sys.schemas s   ON s.schema_id = o.schema_id  
WHERE   o.type IN ('U', 'V')

Объекты INFORMATION_SCHEMA – это набор представлений, которые указывают на объекты sys. Они позволяют пользователям легче получать метаданные. Они лучше организованы и проще в использовании. Вы можете использовать INFORMATION_SCHEMA для доступа к большинству метаданных. Если вы видите определение INFORMATION_SCHEMA, его исходные таблицы являются объектами sys.

Получить все таблицы/представления схемы
Когда вас просят предоставить список таблиц каждой схемы, что бы вы сделали? Я бы сделал следующее с помощью sql-запроса, потому что я недостаточно умен, чтобы найти кнопку в пользовательском интерфейсе SSMS.

Следующие запросы предоставляют два варианта, используя объекты sys и используя INFORMATION_SCHEMA. INFORMATION_SCHEMA выглядит более простой в использовании. Однако INFORMATION_SCHEMA по умолчанию включает в себя как таблицы, так и представления, вам нужно указать ее, когда вам нужна только одна из них.

/*** Use sys  ***/
select
	schema_name(t.schema_id) as table_schema,
	t.name as table_name
from sys.tables t -- use sys.views for views
where schema_name(t.schema_id) = 'schema name' -- put your schema name here
order by t.name;


/*** Use INFORMATION_SCHEMA  ***/
select
	table_schema,
	table_name
from INFORMATION_SCHEMA.TABLES 
where table_schema='schema name' -- put your schema name here
	and table_type = 'BASE TABLE' -- default are tables and views. Use 'VIEW' for views
order by table_name;

Усечение всех таблиц в схеме

Вам может понадобиться перезагрузить все таблицы схемы. Но у вас нет сценария для воссоздания всех таблиц. Это означает, что вы не можете просто очистить схему, вам нужно усечь все таблицы.

При использовании объектов sys для получения имени схемы требуется дополнительное соединение. Но INFORMATION_SCHEMA уже объединила схему, представление и таблицу.

/* Use sys objects */
DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = (
    SELECT 'TRUNCATE TABLE [' + s.name + '].[' + o.name + ']' + CHAR(13)
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U'
        AND s.name = 'schema_name' -- put shcema name here
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL

--EXEC sys.sp_executesql @SQL


/* Use INFORMATION_SCHEMA */
DECLARE @SQL NVARCHAR(MAX) = ''

SELECT @SQL = (
    SELECT 'TRUNCATE TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + CHAR(13)
    FROM INFORMATION_SCHEMA.TABLES WITH (NOWAIT)
    WHERE TABLE_SCHEMA = 'schema_name' -- put shcema name here
		AND TABLE_TYPE = 'BASE TABLE'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL

--EXEC sys.sp_executesql @SQL

Поиск таблиц по имени столбца

Когда вы присоединяетесь к новой команде или начинаете новый проект, вы не знакомы с таблицами в базе данных. Вас просят создать запрос, в котором необходимо соединить различные объекты данных. Наконец, вы нашли столбец в таблице A и хотите соединиться с другой таблицей по этому столбцу, чтобы выполнить эту задачу. Но вы не знаете, какая таблица является правильной, и у вас нет списка таблиц, в который вы могли бы заглянуть.

Конечно, вы всегда можете прочитать ER-диаграмму или какую-нибудь документацию. Но если вы работаете с хранилищем данных или озером данных, это означает, что вам придется выуживать эту небольшую информацию из информации тысяч таблиц. Это сложная задача.

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

SELECT
    t.TABLE_TYPE AS [Type],
    t.TABLE_SCHEMA AS [Schema],
    c.TABLE_NAME AS [Object],
    c.COLUMN_NAME AS [Column]
FROM
    INFORMATION_SCHEMA.COLUMNS AS c
LEFT JOIN
	INFORMATION_SCHEMA.TABLES AS t 
	ON
		t.TABLE_CATALOG = c.TABLE_CATALOG 
		AND t.TABLE_SCHEMA = c.TABLE_SCHEMA 
		AND t.TABLE_NAME = c.TABLE_NAME
WHERE
    c.COLUMN_NAME LIKE '%enter column name keyword here%'  -- column keyword
ORDER BY
    [Type],
    [Schema],
    [Object],
    [Column]

Максимальное и минимальное значения столбцов всех таблиц

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

/* Use sys object */
DECLARE @SQL NVARCHAR(MAX) = ''

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
	DROP TABLE #Temp
END;

CREATE TABLE #Temp(
TableName VARCHAR(200),
max_CreatedON DATETIME,
min_CreatedON DATETIME
)

select 
@SQL = (
    SELECT 'INSERT INTO #TEMP SELECT ' + '''' + o.name + '''' + ' as TableName, MAX(CreatedON) as max_CreatedON, MIN(CreatedON) as min_CreatedON FROM [' + s.name + '].[' + o.name + ']' + CHAR(13)
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE o.[type] = 'U'
		AND s.name = 'schema_name'  -- put schema name here
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

--PRINT @SQL
EXEC sys.sp_executesql @SQL
SELECT *
FROM #Temp
ORDER BY TableName


/* Use INFORMATION_SCHEMA */
DECLARE @SQL NVARCHAR(MAX) = ''

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
	DROP TABLE #Temp
END;

CREATE TABLE #Temp(
TableName VARCHAR(200),
max_CreatedOn INT,
min_CreatedOn INT
)

select 
@SQL = (
    SELECT 'INSERT INTO #TEMP SELECT ' + '''' + TABLE_NAME + '''' + ' as TableName, MAX(CreatedOn) as max_CreatedOn, MIN(CreatedOn) as min_CreatedOn FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' + CHAR(13)
    FROM INFORMATION_SCHEMA.TABLES WITH (NOWAIT)
    WHERE 
		TABLE_SCHEMA = 'schema_name'  -- put schema name here
		AND TABLE_TYPE='BASE TABLE'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

--PRINT @SQL
EXEC sys.sp_executesql @SQL
SELECT *
FROM #Temp
ORDER BY TableName

Список последнего времени выполнения задания агента SQL Server Agent

Последний запрос позволяет вывести список последнего времени выполнения всех заданий SQL Server Agent. Это эффективный способ анализа запланированных заданий. Вам может понадобиться запросить доступ к таблицам msdb.dbo.sysjobhistory и msdb.dbo.sysjobs.

WITH CTE AS
(SELECT 
	SJ.name AS [Job Name],
	CONVERT(VARCHAR, 
		DATEADD(S, 
			(SJH.run_time / 10000 * 60 * 60 + (SJH.run_time - SJH.run_time / 10000 * 10000) / 100 * 60) 
			 + (SJH.run_time - SJH.run_time / 100 * 100), 
			 CONVERT(DATETIME, RTRIM(SJH.run_date), 113)), 100) 
  AS [Time Run]
 FROM msdb.dbo.sysjobhistory AS SJH
 INNER JOIN msdb.dbo.sysjobs AS SJ ON SJH.job_id = SJ.job_id
 WHERE 
	(SJH.step_id = 1) 
	AND (SJH.run_status = 1) 
	AND (SJ.name LIKE '%job name%'))  -- put your job names here

SELECT 
	[Job Name], 
	MAX(CONVERT(datetime, [Time Run])) AS [Time Run]
FROM CTE AS CTE_1
GROUP BY [Job Name]

Надеемся, что вам понравилось.