Досліджуємо бази даних за допомогою T-SQL

Як dba і консультант по оптимізації продуктивності SQL Server Ambient Consulting, я часто стикаюся з необхідністю аналізу вузьких місць продуктивності на примірниках SQL Server, які бачу перший раз в житті. Це може бути складним завданням. Як правило, у більшості компаній немає документації по їх баз даних. А якщо є, то вона застаріла, або ж її пошук займає кілька днів.

У цій статті я поділюся базовим набором скриптів, раскапывающим інформацію про метаданих за допомогою системних функцій, збережених процедур, таблиць, dmv. Разом вони розкривають всі секрети баз даних на потрібному примірнику — їх розмір, розташування файлів, їх дизайн, включаючи стовпці, типи даних, значення за замовчуванням, ключі та індекси.

Якщо ви коли-небудь намагалися отримати частину цієї інформації, з допомогою GUI, я думаю ви будете приємно здивовані кількістю тієї інформації, яка, з допомогою цих скриптів, виходить мнгновенно.

Як і з будь-якими скриптами, спочатку перевірте їх в тестовому оточенні, перш ніж запускати в продакшені. Я б рекомендував вам поганяти їх на тестових базах MS, таких як AdventureWorks або pubs.

Ну, вистачить слів, давайте я покажу скрипти!

Вивчаємо сервераПочнемо з запитів, надають інформацію про ваших серверах.

Базова інформація
По-перше, кілька простих @@Функцій, які нададуть нам базову інформацію.

-- Імена сервера і примірника 
Select @@SERVERNAME as [Server\Instance]; 

-- версія SQL Server 
Select @@VERSION as SQLServerVersion; 

-- екземпляр SQL Server 
Select @@ServiceName AS ServiceInstance;

-- Поточна БД (БД, в контексті якої виконується запит)
Select DB_NAME() AS CurrentDB_Name;

Як довго ваш SQL Server працює після останнього перезавантаження? Пам'ятайте, що системна база даних tempdb пересоздается при кожному перезапуску SQL Server. Ось один з методів визначення часу останнього перезавантаження сервера.

SELECT @@Servername AS ServerName ,
create_date AS ServerStarted ,
DATEDIFF(s, create_date, GETDATE()) / 86400.0 AS DaysRunning ,
DATEDIFF(s, create_date, GETDATE()) AS SecondsRunnig
FROM sys.databases
WHERE name = 'tempdb'; 

GO

Пов'язані сервера
Пов'язані сервера — це з'єднання, що дозволяють SQL Server'mssql у звертатися до інших серверів з даними. Розподілені запити можуть бути запущені на різних пов'язаних серверах. Корисно знати — чи є ваш сервер баз даних ізольованим від інших, або він пов'язаний з іншими серверами.

EXEC sp_helpserver; 

OR -- 

EXEC sp_linkedservers; 

OR -- 

SELECT @@SERVERNAME AS Server ,
Server_Id AS LinkedServerID ,
name AS LinkedServer ,
Product ,
Provider ,
Data_Source ,
Modify_Date
FROM sys.servers
ORDER BY name; 

GO

Список всіх баз даних
По-перше, одержимо список всіх баз даних на сервері. Пам'ятайте, що на будь-якому сервері є чотири або п'ять системних баз даних (master, model, msdb, tempdb і distribution, якщо ви користуєтеся реплікацією). Ви, ймовірно, захочете виключити ці бази в наступних запитах. Дуже просто побачити список баз даних в SSMS, але, ці запити будуть нашими «будівельними блоками» для більш складних запитів.

Є кілька шляхів для отримання списку всіх БД на T-SQL і нижче ви побачите деякі з них. Кожен метод повертає схожий результат, але з деякими відмінностями.

EXEC sp_helpdb; 

OR -- 

EXEC sp_Databases; 

OR -- 

SELECT @@SERVERNAME AS Server ,
name AS DBName ,
recovery_model_Desc AS RecoveryModel ,
Compatibility_level AS CompatiblityLevel ,
create_date ,
state_desc
FROM sys.databases
ORDER BY Name; 

OR -- 

SELECT @@SERVERNAME AS Server ,
d.name AS DBName ,
create_date ,
compatibility_level ,
m.physical_name FileName AS
FROM sys.databases d
JOIN sys.master_files m ON d.database_id = m.database_id
WHERE m.[type] = 0 -- data files only
ORDER BY d.name; 

GO

Останній бекап?
Стоп! Перш ніж рухатися далі, кожен хороший dba повинен дізнатися чи є у нього свіжий бекап.

SELECT @@Servername AS ServerName ,
d.Name AS DBName ,
MAX(b.backup_finish_date) AS LastBackupCompleted
FROM sys.databases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.[type] = 'D'
GROUP BY d.Name
ORDER BY d.Name;

Буде краще, якщо ви відразу дізнаєтеся шлях до файлу з останнім резервних копій.

SELECT @@Servername AS ServerName ,
d.Name AS DBName ,
b.Backup_finish_date ,
bmf.Physical_Device_name
FROM sys.databases d
INNER JOIN msdb..backupset b ON b.database_name = d.name
AND b.[type] = 'D'
INNER JOIN msdb.dbo.backupmediafamily bmf ON b.media_set_id = bmf.media_set_id
ORDER BY d.NAME ,
b.Backup_finish_date DESC; 

GO

Активні користувальницькі з'єднання
Добре було б розуміти які БД зараз використовуються, особливо, якщо ви збираєтеся розбиратися з проблемами продуктивності.

Примітка перекладача: це буде працювати тільки в SQL Server 2012 і вище, в попередніх редакціях, в dmv sys.dm_exec_sessions відсутній стовпець database_id. Щоб дізнатися в яких БД в даний момент працюють користувачі, можна скористатися sp_who.

-- Схожа інформація може бути отримана з допомогою sp_who 

SELECT @@Servername AS Server ,
DB_NAME(database_id) AS DatabaseName ,
COUNT(database_id) AS Connections ,
Login_name AS LoginName ,
MIN(Login_Time) AS Login_Time ,
MIN(COALESCE(last_request_end_time, last_request_start_time))
AS Last_Batch
FROM sys.dm_exec_sessions
WHERE database_id > 0
AND DB_NAME(database_id) NOT IN ( 'master', 'msdb' )
GROUP BY database_id ,
login_name
ORDER BY DatabaseName;

Вивчаємо бази данихДавайте заглянемо глибше і подивимося, як ми можемо зібрати інформацію про об'єкти у всіх ваших БД, використовуючи різні представлення каталогу і dmv. Більшість запитів, поданих у цьому розділі, дивляться «всередину» лише одній БД, тому не забувайте вибирати потрібну БД в SSMS або за допомогою команди use database. Також пам'ятайте, що ви завжди можете подивитися в контексті якої БД буде виконано запит, за допомогою select db_name().

Системна таблиця sys.objects одна з ключових для збору інформації про об'єкти, які становлять вашу модель даних.

-- У прикладі U - таблиці
-- Спробуйте підставити інші значення type в WHERE

USE MyDatabase;
GO

SELECT *
FROM sys.objects
WHERE type = 'U';

Нижче представлений список типів об'єктів, інформацію про яких ми можемо отримати (дивіться документацію на sys.objects MSDN

sys.objects.typeAF = статистична функція (середовище CLR);
C = обмеження CHECK;
D = DEFAULT (обмеження або ізольований);
F = обмеження FOREIGN KEY;
PK = обмеження PRIMARY KEY;
P = збережена процедура SQL;
PC = збережена процедура складання (середовище CLR);
FN = скалярна функція SQL;
FS = скалярна функція складання (середовище CLR);
FT = повертає табличне значення функція складання (середовище CLR);
R = правило (старий стиль, ізольований);
RF = процедура фільтра реплікації;
S = системна базова таблиця;
SN = синонім;
SQ = чергу обслуговування;
TA = тригер DML збірки (середовище CLR);
TR = тригер DML SQL;
IF = вбудована повертає табличне значення функція SQL;
TF = повертає табличне значення функція SQL;
U = таблиця (користувача);
UQ = обмеження UNIQUE;
V = уявлення;
X = розширена збережена процедура;
IT = внутрішня таблиця.

Інші представлення каталогу, такі як sys.tables і sys.views, звертаються до sys.objects і надають інформацію про конкретному типі об'єктів. З цими уявленнями, плюс функцією OBJECTPROPERTY, ми можемо отримати величезну кількість інформації по кожному з об'єктів, що становлять нашу схему БД.

Розташування файлів баз даних
Фізичне розташування обраної БД, включаючи основний файл даних (mdf), і файл журналу транзакцій (ldf), можуть бути отримані з допомогою цих запитів.

EXEC sp_Helpfile; 

OR -- 

SELECT @@Servername AS Server ,
DB_NAME() AS DB_Name ,
File_id ,
Type_desc ,
Name ,
LEFT(Physical_Name, 1) AS Drive ,
Physical_Name ,
RIGHT(physical_name, 3) AS Ext ,
Size ,
Growth
FROM sys.database_files
ORDER BY File_id; 

GO

Таблиці
Звичайно, Object Explorer SSMS показує повний список таблиць у вибраній БД, але частина інформації з допомогою GUI отримати складніше, ніж з допомогою скриптів. Стандарт ANSI передбачає звернення до уявленням INFORMATION_SCHEMA, але вони не нададуть інформацію про об'єкти, які не є частиною стандарту (такі як тригери, extended procedures тощо), тому краще використовувати представлення каталогу SQL Server.

EXEC sp_tables; -- Пам'ятаєте, що цей метод поверне і таблиці, і подання 

OR -- 

SELECT @@Servername AS ServerName ,
TABLE_CATALOG ,
TABLE_SCHEMA ,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME ;

OR --

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS 'TableName' ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.Type = 'U' -- User table 
ORDER BY o.name;

OR -- 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
t.Name AS TableName,
t.[Type],
t.create_date
FROM sys.tables t
ORDER BY t.Name;

GO

Кількість записів в таблиці
Якщо ви нічого не знаєте про таблиці, всі таблиці однаково важливі. Чим більше ви дізнаєтеся про таблицях, тим більше ви їх розділяєте на умовно більш важливі і умовно менш важливі. В цілому, таблиці з величезною кількістю записів частіше роблять серйозний вплив на продуктивність.

У SSMS ми можемо натиснути правою кнопкою миші на будь-яку таблицю, відкрити властивості на вкладці Storage та побачити кількість записів у таблиці.



Досить важко зібрати вручну цю інформацію про всіх таблицях. Знову ж таки, якщо ми будемо писати SELECT COUNT(*) FROM TABLENAME для кожної таблиці, нам доведеться дуже багато друкувати.

Набагато зручніше використовувати T-SQL для генерування скрипта. Скрипт, наведений нижче, згенерує набір інструкцій T-SQL для отримання кількості рядків у кожній таблиці в поточній базі даних. Просто виконайте його, скопіювати результат в нове вікно і запустіть.

SELECT 'Select' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
+ LEFT(o.name, 128) + "' as DBName, count(*) as Count From ' + o.name
+ ';' AS ' Script generator to get counts for all tables'
FROM sys.objects o
WHERE o.[type] = 'U'
ORDER BY o.name;

GO



Примітка перекладача: у мене запит не працював, додав схему до імені таблиці.
SELECT 'Select' + DB_NAME() + '.' + SCHEMA_NAME(SCHEMA_ID) + '.'
+ LEFT(o.name, 128) + "' as DBName, count(*) as Count From ' + SCHEMA_NAME(SCHEMA_ID) + '.' + o.name
+ ';' AS ' Script generator to get counts for all tables'
FROM sys.objects o
WHERE o.[type] = 'U'
ORDER BY o.name;

sp_msForEachTable
Sp_msforeachtable — це недокументированная функція, яка «проходить» по всіх таблиць в БД і виконує запит, підставляючи замість '?' ім'я поточної таблиці. Так само існує схожа функція sp_msforeachdb, що працює на рівні баз даних.

Відомо кілька проблем з цією недокументованій функцією, наприклад, використання спецсимволов в іменах об'єктів. Тобто якщо ім'я таблиці або бази даних містить знак '-', збережена процедура, лістинг якої нижче, завершиться з помилкою.

CREATE TABLE #rowcount
( Tablename VARCHAR(128) ,
Rowcnt INT ); 

EXEC sp_MSforeachtable 'insert into #rowcount select "?", count(*) from ?' 

SELECT *
FROM #rowcount
ORDER BY Tablename ,
Rowcnt; 

DROP TABLE #rowcount;

найшвидший спосіб отримання кількості записів — кластерний індекс
Всі попередні методу використовували COUNT(*), який повільно відпрацьовує, якщо в таблиці більше ніж 500K записів.

Найшвидший спосіб отримання кількості записів у таблиці — одержувати кількість записів в кластерному індексі або купі. Пам'ятайте, що хоч цей метод і найшвидший, MS каже, що інформація про кількість записів індексу і реальна кількість рядків у таблицях може не співпадати із-за того, що на оновлення інформації потрібно хоч і невелике, але час. У більшості ж випадків ці значення або однакові, або дуже-дуже близькі і незабаром стануть однаковими.

-- найшвидший шлях отримання кількості записів
-- Hint: отримуйте з індексу, а не таблиці

SELECT @@ServerName AS Server ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(p.object_id) AS SchemaName ,
OBJECT_NAME(p.object_id) AS TableName ,
i.Type_Desc ,
i.Name AS IndexUsedForCounts ,
SUM(p.Rows) AS Rows
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id = p.object_id
AND i.index_id = p.index_id
WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' )
-- This is key (1 index per table) 
AND OBJECT_SCHEMA_NAME(p.object_id) <> 'sys'
GROUP BY p.object_id ,
i.type_desc ,
i.Name
ORDER BY SchemaName ,
TableName; 

OR -- 

-- Схожий метод отримання кількості записів, але з використанням DMV dm_db_partition_stats 
SELECT @@ServerName AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
OBJECT_NAME(ddps.object_id) AS TableName ,
i.Type_Desc ,
i.Name AS IndexUsedForCounts ,
SUM(ddps.row_count) AS Rows
FROM sys.dm_db_partition_stats ddps
JOIN sys.indexes i ON i.object_id = ddps.object_id
AND i.index_id = ddps.index_id
WHERE i.type_desc IN ( 'CLUSTERED', 'HEAP' )
-- This is key (1 index per table) 
AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'
GROUP BY ddps.object_id ,
i.type_desc ,
i.Name
ORDER BY SchemaName ,
TableName;

GO

Пошук куп (таблиць без кластерних індексів)
Робота з купами — це робота з плоским файлом, замість бази даних. Якщо ви хочете гарантовано отримувати повне сканування таблиці при виконанні будь-якого запиту, використовуйте купи. Зазвичай я рекомендую додавати primary key до всіх таблиць-купах.

-- Купи (метод 1)

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
t.Name AS HeapTable ,
t.Create_Date
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
AND i.type_desc = 'HEAP'
ORDER BY t.Name 

OR -- 
-- Купи (Метод 2) 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
t.Name AS HeapTable ,
t.Create_Date
FROM sys.tables t
WHERE OBJECTPROPERTY(OBJECT_ID, 'TableHasClustIndex') = 0
ORDER BY t.Name; 

OR -- 
-- Купи (Метод 3) + кількість записів

SELECT @@ServerName AS Server ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(ddps.object_id) AS SchemaName ,
OBJECT_NAME(ddps.object_id) AS TableName ,
i.Type_Desc ,
SUM(ddps.row_count) AS Rows
FROM sys.dm_db_partition_stats AS ddps
JOIN sys.indexes i ON i.object_id = ddps.object_id
AND i.index_id = ddps.index_id
WHERE i.type_desc = 'HEAP'
AND OBJECT_SCHEMA_NAME(ddps.object_id) <> 'sys'
GROUP BY ddps.object_id ,
i.type_desc
ORDER BY TableName;

Розбираємося з активністю в таблиці
При роботах по оптимізації продуктивності, дуже важливо знати, які таблиці активно читаються, а в які йде активний запис. Раніше ми дізналися скільки записів у наших таблицях, зараз подивимося як часто в них пишуть і читають.

Пам'ятайте, що ця інформація з dmv, очищається при кожному перезапуску SQL Server. Чим довше сервер працює, тим більш надійна статистика. Я відчуваю себе набагато впевненіше зі статистикою, зібраної за 30 днів, ніж зі статистикою, зібраної за тиждень.

-- Читання/запис таблиці
-- Купи не розглядаються, у них немає індексів
-- Тільки ті таблиці, до яких зверталися після запуску SQL Server

SELECT @@ServerName AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_NAME(ddius.object_id) AS TableName ,
SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups)
AS Reads ,
SUM(ddius.user_updates) AS Writes ,
SUM(ddius.user_seeks + ddius.user_scans + ddius.user_lookups
+ ddius.user_updates) AS [Reads&Writes] ,
( SELECT DATEDIFF(s, create_date, GETDATE()) / 86400.0
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleDays ,
( SELECT DATEDIFF(s, create_date, GETDATE()) AS SecoundsRunnig
FROM master.sys.databases
WHERE name = 'tempdb'
) AS SampleSeconds
FROM sys.dm_db_index_usage_stats ddius
INNER JOIN sys.indexes i ON ddius.object_id = i.object_id
AND i.index_id = ddius.index_id
WHERE OBJECTPROPERTY(ddius.object_id, 'IsUserTable') = 1
AND ddius.database_id = DB_ID()
GROUP BY OBJECT_NAME(ddius.object_id)
ORDER BY [Reads&Writes] DESC;

GO

Набагато більш просунута версія цього запиту представлена курсором, що збирає інформацію по всіх таблиць всіх баз даних на сервері. Взагалі, я не фанат курсорів з-за їх невисокої продуктивності, але переміщення по різних баз даних — це відмінне застосування для них.

-- Операції читання і запису
-- Купи пропущені, у них немає індексів
-- Тільки таблиці, що використовувалися після перезапуску SQL Server
-- У запиті використовується курсор для отримання інформації в усіх БД
-- Єдиний звіт, що зберігається в tempdb

DECLARE DBNameCursor CURSOR
FOR
SELECT Name
FROM sys.databases
WHERE Name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
'distribution' )
ORDER BY Name; 

DECLARE @DBName NVARCHAR(128) 

DECLARE @cmd VARCHAR(4000) 

IF OBJECT_ID('tempdb..TempResults') IS NOT NULL
BEGIN 
DROP TABLE tempdb..TempResults 
END 

CREATE TABLE tempdb..TempResults
(
ServerName NVARCHAR(128) ,
DBName NVARCHAR(128) ,
TableName NVARCHAR(128) ,
Reads INT ,
Writes INT ,
ReadsWrites INT ,
SampleDays DECIMAL(18, 8) ,
SampleSeconds INT
) 

OPEN DBNameCursor 

FETCH NEXT FROM DBNameCursor INTO @DBName 
WHILE @@fetch_status = 0
BEGIN 

---------------------------------------------------- 
-- Print @DBName 

SELECT @cmd = 'Use ' + @DBName + '; ' 
SELECT @cmd = @cmd + ' Insert Into tempdb..TempResults 
SELECT @@ServerName AS ServerName, 
DB_NAME() AS DBName, 
object_name(ddius.object_id) AS TableName , 
SUM(ddius.user_seeks 
+ ddius.user_scans 
+ ddius.user_lookups) AS Reads, 
SUM(ddius.user_updates) as Writes, 
SUM(ddius.user_seeks 
+ ddius.user_scans 
+ ddius.user_lookups 
+ ddius.user_updates) as ReadsWrites, 
(SELECT datediff(s,create_date, GETDATE()) / 86400.0 
FROM sys.databases WHERE name = "tempdb") AS SampleDays, 
(SELECT datediff(s,create_date, GETDATE()) 
FROM sys.databases WHERE name = "tempdb") as SampleSeconds 
FROM sys.dm_db_index_usage_stats ddius 
INNER JOIN sys.indexes i
ON ddius.object_id = i.object_id 
AND i.index_id = ddius.index_id 
WHERE objectproperty(ddius.object_id,"IsUserTable") = 1 --True 
AND ddius.database_id = db_id() 
GROUP BY object_name(ddius.object_id) 
ORDER BY ReadsWrites DESC;' 

--PRINT @cmd 
EXECUTE (@cmd) 

----------------------------------------------------- 

FETCH NEXT FROM DBNameCursor INTO @DBName 
END 

CLOSE DBNameCursor 

DEALLOCATE DBNameCursor 

SELECT *
FROM tempdb..TempResults
ORDER BY DBName ,
TableName; 
--DROP TABLE tempdb..TempResults;

Примітка перекладача: курсор не відпрацює, якщо у вас в списку є бази даних з станом, відмінним від ONLINE.

Подання
Уявлення — це, умовно кажучи, запити, що зберігаються в БД. Ви можете думати про них, як про віртуальних таблицях. Дані не зберігаються в уявленнях, але в наших запитах ми посилаємося на них точно так само, як і на таблиці.

У SQL Server, в деяких випадках, ми можемо оновлювати дані з використанням представлення. Щоб отримати уявлення «тільки для читання», можна використовувати SELECT DISTINCT при його створенні. Дані «через» уявлення можна змінювати тільки в тому випадку, якщо кожного рядка відповідає тільки один рядок в «базовій» таблиці. Будь-яке подання, що не відповідає цьому критерію, тобто побудована на кількох таблицях, або з використанням угруповань, агрегатних функцій і обчислень, буде доступно тільки для читання.

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS ViewName ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.[Type] = 'V' -- View 
ORDER BY o.NAME 

OR -- 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
Name AS ViewName ,
create_date
FROM sys.Views
ORDER BY Name 

OR --

SELECT @@Servername AS ServerName ,
TABLE_CATALOG ,
TABLE_SCHEMA ,
TABLE_NAME ,
TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
ORDER BY TABLE_NAME 

OR -- 

-- CREATE VIEW Code 
SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'ViewName' ,
o.Type ,
o.create_date ,
sm.[DEFINITION] AS 'View script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'V' -- View 
ORDER BY o.NAME;

GO

Синоніми
Кілька разів у моїй кар'єрі я стикався з ситуацією, коли не міг зрозуміти до якої ж таблиці звертається запит. Уявіть простий запит SELECT * FROM Client. Я шукаю таблицю під ім'ям Client, але я не можу знайти її. Добре, думаю я, повинно бути це подання, шукаю подання з ім'ям Client і все одно не можу знайти. Може бути я помилився базою даних? У результаті з'ясовується, що Client — це синонім для покупців і таблиця, насправді, називається Customer. Відділ маркетингу хотів звертатися до цієї таблиці як до Client і з-за цього був створений синонім. На щастя, використання синонімів — це рідкість, але розгляди можуть викликати певні труднощі, якщо ви до них не готові.

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS ViewName ,
o.Type ,
o.create_date
FROM sys.objects o
WHERE o.[Type] = 'SN' -- Synonym 
ORDER BY o.NAME;

OR -- 
-- додаткова інформація про синоніми

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
s.name AS synonyms ,
s.create_date ,
s.base_object_name
FROM sys.synonyms s
ORDER BY s.name;

GO

Збережені процедури
Збережені процедури — це група скриптів, які компілюються в єдиний план виконання. Ми можемо використовувати представлення каталогу, щоб визначити які ХП створені, які дії вони виконують і над якими таблицями.

-- Збережені процедури 
SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
o.name AS StoredProcedureName ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.[Type] = 'P' -- Stored Procedures 
ORDER BY o.name

OR -- 
-- Додаткова інформація про ХП 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'ViewName' ,
o.[type] ,
o.Create_date ,
sm.[definition] AS 'Stored Procedure script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id
WHERE o.[type] = 'P' -- Stored Procedures 
-- AND sm.[definition] LIKE '%insert%'
-- AND sm.[definition] LIKE '%update%'
-- AND sm.[definition] LIKE '%delete%'
-- AND sm.[definition] LIKE '%tablename%'
ORDER BY o.name;

GO

Додавши просте умова WHERE ми можемо отримати інформацію тільки про тих збережених процедурах, які, наприклад, виконують операції INSERT.

WHERE o.[type] = 'P' -- Stored Procedures 
AND sm.definition LIKE '%insert%'
ORDER BY o.name
...

Трохи модифікувавши умова WHERE, ми можемо зібрати інформацію про ХП, що виробляють оновлення, видалення або ж звертаються до певних таблиць.


Функції зберігаються в SQL Server, приймають якісь параметри і виконують певні дії, або обчислення, після чого повертають результат.

-- Функції

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'Functions' ,
o.[Type] ,
o.create_date
FROM sys.objects o
WHERE o.Type = 'FN' -- Function 
ORDER BY o.NAME;

OR -- 
-- Додаткова інформація про функції

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.name AS 'FunctionName' ,
o.[type] ,
o.create_date ,
sm.[DEFINITION] AS 'Function script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.[Type] = 'FN' -- Function 
ORDER BY o.NAME;

GO

Тригери
Тригер — це щось на зразок збереженої процедури, яка виконується у відповідь на певні дії з тією таблицею, в якій цей тригер належить. Наприклад, ми можемо створити INSERT, UPDATE і DELETE тригери.

-- Тригери

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
parent.name AS TableName ,
o.name AS TriggerName ,
o.[Type] ,
o.create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.Type = 'TR' -- Triggers 
ORDER BY parent.name ,
o.NAME 

OR -- 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
Parent_id ,
name AS TriggerName ,
create_date
FROM sys.triggers
WHERE parent_class = 1
ORDER BY name;

OR -- 
-- Додаткова інформація про тригерах

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
OBJECT_NAME(Parent_object_id) AS TableName ,
o.name AS 'TriggerName' ,
o.Type ,
o.create_date ,
sm.[DEFINITION] AS 'Trigger script'
FROM sys.objects o
INNER JOIN sys.sql_modules sm ON o.object_id = sm.OBJECT_ID
WHERE o.Type = 'TR' -- Triggers 
ORDER BY o.NAME;

GO

CHECK-обмеження
CHECK-обмеження — це непоганий засіб для реалізації бізнес-логіки в базі даних. Наприклад, деякі поля повинні бути позитивними, або негативними, або дата в одному стовпці повинна бути більше дати в іншому.
-- Check Constraints 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
parent.name AS 'TableName' ,
o.name AS 'Constraints' ,
o.[Type] ,
o.create_date
FROM sys.objects o
INNER JOIN sys.objects parent
ON o.parent_object_id = parent.object_id
WHERE o.Type = 'C' -- Check Constraints 
ORDER BY parent.name ,
o.name 

OR -- 
--CHECK constriant definitions

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName ,
OBJECT_NAME(parent_object_id) AS TableName ,
parent_column_id AS Column_NBR ,
Name AS CheckConstraintName ,
type ,
type_desc ,
create_date ,
OBJECT_DEFINITION(object_id) AS CheckConstraintDefinition
FROM sys.Check_constraints
ORDER BY TableName ,
SchemaName ,
Column_NBR 

GO

Заглиблюємося в модель данихРаніше, ми використовували скрипти, які дали нам уявлення про «верхньому рівні» об'єктів, що становлять нашу базу даних. Іноді нам потрібно отримати більше даних про таблиці, включаючи стовпці, їх типи даних, які значення за замовчуванням задані, які ключі та індекси існують (або повинні існувати) і т.д.

Запити, подані в цьому розділі, надають кошти майже реверс-інжинірингу існуючої моделі даних.

Стовпці
Наступний скрипт описує таблиці і стовпчики з усієї бази даних. Результат цього запиту, можна скопіювати в Excel, де можна налаштувати фільтри і сортування і добре розібратися з типами даних, що використовуються в БД. Так само, зверніть увагу на стовпців з однаковими іменами, але різними типами даних.

SELECT @@Servername AS Server ,
DB_NAME() AS DBName ,
isc.Table_Name AS TableName ,
isc.Table_Schema AS SchemaName ,
Ordinal_Position AS Ord ,
Column_Name ,
Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length AS LEN , -- -1 means MAX like Varchar(MAX) 
Is_Nullable ,
Column_Default ,
Table_Type
FROM INFORMATION_SCHEMA.COLUMNS isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name 
-- WHERE Table_Type = 'BASE TABLE' -- 'Base Table' or 'View' 
ORDER BY DBName ,
TableName ,
SchemaName ,
Ordinal_position; 

-- Імена стовпців і кількість повторів
-- Використовується для пошуку однойменних стовпців з різними типами даних/довжиною

SELECT @@Servername AS Server ,
DB_NAME() AS DBName ,
Column_Name ,
Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length ,
COUNT(*) AS Count
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
WHERE Table_type = 'BASE TABLE'
GROUP BY Column_Name ,
Data_Type ,
Numeric_Precision ,
Numeric_Scale ,
Character_Maximum_Length; 

-- Інформація по використовуваних типів даних

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length AS [Length] ,
COUNT(*) AS COUNT
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
WHERE Table_type = 'BASE TABLE'
GROUP BY Data_Type ,
Numeric_Precision ,
Numeric_Scale ,
Character_Maximum_Length
ORDER BY Data_Type ,
Numeric_Precision ,
Numeric_Scale ,
Character_Maximum_Length 

-- Large object data types or Binary Large Objects(Краплі) 
-- Пам'ятаєте, що індекси по цим таблицям не можуть бути перебудовані в режимі "онлайн"

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
isc.Table_Name ,
Ordinal_Position AS Ord ,
Column_Name ,
Data_Type AS BLOB_Data_Type ,
Numeric_Precision AS Prec ,
Numeric_Scale AS Scale ,
Character_Maximum_Length AS [Length]
FROM information_schema.columns isc
INNER JOIN information_schema.tables ist
ON isc.table_name = ist.table_name
WHERE Table_type = 'BASE TABLE'
AND ( Data_Type IN ( 'text', 'n-текст', 'image', 'XML' )
OR ( Data_Type IN ( 'varchar', 'nvarchar', 'varbinary' )
AND Character_Maximum_Length = -1
)
) -- varchar(max), nvarchar(max), varbinary(max) 
ORDER BY isc.Table_Name ,
Ordinal_position;

Значення за замовчуванням
Типове значення — це значення, яке буде збережено, якщо ніякого значення для стовпця не буде задано при вставці. Найчастіше, для стовпців зберігають дату ставлять get_date(). Також, за замовчуванням використовуються для аудиту — вставляється system_user для визначення облікового запису користувача, що вчинила певну дію.

-- Table Defaults 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
parent.name AS TableName ,
o.name AS Defaults ,
o.[Type] ,
o.Create_date
FROM sys.objects o
INNER JOIN sys.objects parent
ON o.parent_object_id = parent.object_id
WHERE o.[Type] = 'D' -- Defaults 
ORDER BY parent.name ,
o.NAME

OR -- 
-- Column Defaults 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName ,
OBJECT_NAME(parent_object_id) AS TableName ,
parent_column_id AS Column_NBR ,
Name AS DefaultName ,
[type] ,
type_desc ,
create_date ,
OBJECT_DEFINITION(object_id) AS Defaults
FROM sys.default_constraints
ORDER BY TableName ,
Column_NBR 

OR -- 
-- Column Defaults 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName ,
t.Name AS TableName ,
c.Column_ID AS Ord ,
c.Name AS Column_Name ,
OBJECT_NAME(default_object_id) AS DefaultName ,
OBJECT_DEFINITION(default_object_id) AS Defaults
FROM sys.Tables t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE default_object_id <> 0
ORDER BY TableName ,
SchemaName ,
c.Column_ID 

GO

Обчислювані стовпці
Обчислювані стовпці — це стовпці, значення яких обчислюються на підставі, як правило, значень в інших стовпцях таблиці.

-- Обчислювані стовпці

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(object_id) AS SchemaName ,
OBJECT_NAME(object_id) AS Tablename ,
Column_id ,
Name AS Computed_Column ,
[Definition] ,
is_persisted
FROM sys.computed_columns
ORDER BY SchemaName ,
Tablename ,
[Definition]; 

Or -- 
-- Columns Computed 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
t.Name AS TableName ,
c.Column_ID AS Ord ,
c.Name AS Computed_Column
FROM sys.Tables t
INNER JOIN sys.Columns c ON t.object_id = c.object_id
WHERE is_computed = 1
ORDER BY t.Name ,
SchemaName ,
c.Column_ID 

GO

Стовпці identity
Стовпці IDENTITY автоматично заповнюються системою унікальними значеннями. Зазвичай використовуються для зберігання порядкового номера запису в таблиці.

SELECT @@Servername AS ServerName ,
DB_NAME() AS DBName ,
OBJECT_SCHEMA_NAME(object_id) AS SchemaName ,
OBJECT_NAME(object_id) AS TableName ,
Column_id ,
Name AS IdentityColumn ,
Seed_Value ,
Last_Value
FROM sys.identity_columns
ORDER BY SchemaName ,
TableName ,
Column_id; 

GO

Ключі та індекси
Як я писав раніше, наявність первинного ключа та відповідного індексу у таблиці — це одна з best practice. Ще одна best practice полягає в тому, що зовнішні ключі так само повинні мати індекс, побудований за стовпцями, що входять у зовнішній ключ. Індекси, побудовані «по зовнішнім джерелам» відмінно підходять для з'єднання таблиць. Ці індекси так само добре позначаються на продуктивності при видаленні записів.

Які індекси у нас є?
Скрипт для пошуку всіх індексів у всіх таблицях поточної БД.

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
o.Name AS TableName ,
i.Name AS IndexName
FROM sys.objects o
INNER JOIN sys.indexes i ON o.object_id = i.object_id
WHERE o.Type = 'U' -- User table 
AND LEFT(i.Name, 1) <> '_' -- Remove hypothetical indexes 
ORDER BY o.NAME ,
i.name; 

GO

Будь індексів не вистачає?
На підставі раніше виконувалися запитів, SQL Server надає інформацію про відсутніх індексах в БД, створення яких може збільшити продуктивність.

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

-- Відсутні індекси з DMV

SELECT @@ServerName AS ServerName ,
DB_NAME() AS DBName ,
t.name AS 'Affected_table' ,
( LEN(ISNULL(ddmid.equality_columns, N")
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE "
END) - LEN(REPLACE(ISNULL(ddmid.equality_columns, N")
+ CASE WHEN ddmid.equality_columns
IS NOT NULL
AND ddmid.inequality_columns
IS NOT NULL
THEN ','
ELSE "
END, ',', ")) ) + 1 AS K ,
COALESCE(ddmid.equality_columns, ")
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE "
END + COALESCE(ddmid.inequality_columns, ") AS Keys ,
COALESCE(ddmid.included_columns, ") AS [include] ,
'Create NonClustered Index IX_' + t.name + '_missing_'
+ CAST(ddmid.index_handle AS VARCHAR(20)) 
+ ' On ' + ddmid.[statement] COLLATE database_default
+ ' (' + ISNULL(ddmid.equality_columns, ")
+ CASE WHEN ddmid.equality_columns IS NOT NULL
AND ddmid.inequality_columns IS NOT NULL THEN ','
ELSE "
END + ISNULL(ddmid.inequality_columns, ") + ')'
+ ISNULL(' Include (' + ddmid.included_columns+ ');', ';')
AS sql_statement ,
ddmigs.user_seeks ,
ddmigs.user_scans ,
CAST(( ddmigs.user_seeks + ddmigs.user_scans )
* ddmigs.avg_user_impact AS BIGINT) AS 'est_impact' ,
avg_user_impact ,
ddmigs.last_user_seek ,
( SELECT DATEDIFF(Second, create_date, GETDATE()) Seconds
FROM sys.databases
WHERE name = 'tempdb'
) SecondsUptime 
FROM sys.dm_db_missing_index_groups ddmig
INNER JOIN sys.dm_db_missing_index_group_stats ddmigs
ON ddmigs.group_handle = ddmig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details ddmid
ON ddmig.index_handle = ddmid.index_handle
INNER JOIN sys.tables t ON ddmid.OBJECT_ID = t.OBJECT_ID
WHERE ddmid.database_id = DB_ID()
ORDER BY est_impact DESC;

GO

Зовнішні ключі
Зовнішні ключі визначають зв'язок між таблицями і використовуються для контролю посилальної цілісності. Діаграма сутність-зв'язок лінії між таблицями позначають зовнішні ключі.

-- Foreign Keys 

SELECT @@Servername AS ServerName ,
DB_NAME() AS DB_Name ,
parent.name AS 'TableName' ,
o.name AS 'ForeignKey' ,
o.[Type] ,
o.Create_date
FROM sys.objects o
INNER JOIN sys.objects parent ON o.parent_object_id = parent.object_id
WHERE o.[Type] = 'F' -- Foreign Keys 
ORDER BY parent.name ,
o.name 

OR -- 

SELECT f.name AS ForeignKey ,
SCHEMA_NAME(f.SCHEMA_ID) AS SchemaName ,
OBJECT_NAME(f.parent_object_id) AS TableName ,
COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName ,
SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName ,
OBJECT_NAME(f.referenced_object_id) AS ReferenceTableName ,
COL_NAME(fc.referenced_object_id, fc.referenced_column_id)
AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id
ORDER BY TableName ,
ReferenceTableName;

GO

Пропущені індекси по зовнішнім джерелам
Як я вже говорив, бажано мати індекс, побудований за стовпцями, що входять у зовнішній ключ. Це значно прискорює з'єднання таблиць, які, зазвичай, все одно з'єднуються зовнішнього ключа. Ці індекси значно пришвидшують операцію видалення. Якщо такого індексу немає, SQL Server буде виробляти table scan пов'язаної таблиці, при кожному видаленні запису з «першої» таблиці.

-- Foreign missing Keys indexes 
-- Пам'ятаєте, що цей скрипт працює тільки для створення індексів по одному стовпцю
-- Зовнішні ключі, що складаються більш ніж з одного стовпця, не відстежуються

SELECT DB_NAME() AS DBName ,
rc.Constraint_Name AS FK_Constraint , 
-- rc.Constraint_Catalog AS FK_Database, 
-- rc.Constraint_Schema AS FKSch, 
ccu.Table_Name AS FK_Table ,
ccu.Column_Name AS FK_Column ,
ccu2.Table_Name AS ParentTable ,
ccu2.Column_Name AS ParentColumn ,
I.Name AS IndexName ,
CASE WHEN I.Name IS NULL
THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes
WHERE object_id = OBJECT_ID(N"'
+ RC.Constraint_Schema + '.' + ccu.Table_Name
+ "') AND name = N"IX_' + ccu.Table_Name + '_'
+ ccu.Column_Name + "') '
+ 'CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_'
+ ccu.Column_Name + ' ON ' + rc.Constraint_Schema + '.'
+ ccu.Table_Name + '( ' + ccu.Column_Name
+ ' ASC ) WITH (PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = ON);'
ELSE "
END AS SQL
FROM information_schema.referential_constraints RC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2
ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
LEFT JOIN sys.columns c ON ccu.Column_Name = C.name
AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)
LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID
AND c.column_id = ic.column_id
AND index_column_id = 1

-- index has found the foreign key
-- as the first column 

LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID
AND ic.index_Id = i.index_Id
WHERE I.name IS NULL
ORDER BY FK_table ,
ParentTable ,
ParentColumn; 

GO

Залежно
Це залежить… Я впевнений, ви чули цей вислів раніше. Я розгляну три різних методи для «реверс-інжинірингу» залежностей в БД. Перший метод — використовувати збережену процедуру sp_msdependecies. Другий — системні таблиці, пов'язані із зовнішніми ключами. Третій метод — використовувати CTE.

sp_msdependencies
Sp_msdependencies — це недокументированная збережена процедура, яка може бути дуже корисна для розбору складних взаємозалежностей таблиць.

EXEC sp_msdependencies '?' -- Displays Help 

sp_MSobject_dependencies name = NULL, type = NULL, flags = 0x01fd
name: name or null (all objects of type)
type: type number (see below) or null
if both null, get all objects in database
flags is a bitmask of the following values:
0x10000 = return multiple parent/child rows per object
0x20000 = descending order return
0x40000 = return children instead of parents
0x80000 = Include object in input output result set
0x100000 = return only firstlevel (immediate) parents/children
0x200000 = return only DRI dependencies
power(2, object type number(s)) to return results in set:
0 (1 - 0x0001) - UDF
1 (2 - 0x0002) - system tables or MS-internal objects
2 (4 - 0x0004) - view
3 (8 - 0x0008) - user table
4 (16 - 0x0010) - procedure
5 (32 - 0x0020) - log
6 (64 - 0x0040) - default
7 (128 - 0x0080) - rule
8 (256 - 0x0100) - trigger
12 (1024 - 0x0400) - uddt
shortcuts:
29 (0x011c) - trig, view user table, procedure
448 (0x00c1) - rule, default, datatype
4606 (0x11fd) - all but systables/objects
4607 (0x11ff) - all

Якщо ми виведемо всі залежності, використовуючи sp_msdependencies, ми отримаємо чотири стовпця: Type, ObjName, Owner(Schema), Sequence.

Зверніть увагу на номер послідовності (Sequence) — він починається з 1 і послідовно збільшується. Sequence — це «порядковий номер» залежності.

Я кілька разів використовував цей метод, коли мені потрібно було виконати архівування або видалення на дуже великий БД. Якщо ви знаєте залежності таблиці, значить у вас є «дорожня карта» — в якому порядку вам потрібно архівувати або видаляти дані. Почніть з таблиці з найбільшим значення в стовпці Sequence і рухайтеся від нього в зворотному порядку — від більшого до меншого. Таблиці з однаковим значенням Sequence можуть бути видалені одночасно. Цей метод не порушує жодного з обмежень зовнішніх ключів і дозволяє перемістити/видалити запис без тимчасового видалення і перестроювання обмежень (constraints).

EXEC sp_msdependencies NULL -- Всі залежності в БД

EXEC sp_msdependencies NULL, 3 -- Залежно певної таблиці



У SSMS, якщо ви клацнете правою кнопкою миші на ім'я таблиці, ви зможете вибрати «View Dependencies» і «Об'єкти, які залежать від TABLENAME»:



Ми також можемо отримати цю інформацію наступним чином:

-- sp_MSdependencies - Тільки верхній рівень
-- Об'єкти, які залежать від зазначеного об'єкта

EXEC sp_msdependencies ' Sales.Customer',null, 1315327 -- Change Table Name



Якщо в SSMS, у вікні перегляду залежностей, вибрати «Об'єкти які залежать від TABLENAME», а потім розкрити всі рівні, ми побачимо наступне:



Ту ж саму інформацію поверне sp_msdependencies.

-- sp_MSdependencies - Всі рівні
-- Об'єкти, які залежать від зазначеного об'єкта

EXEC sp_MSdependencies ' Sales.Customer', NULL, 266751 -- Change Table Name



Так само, в SSMS, ми можемо побачити, від яких об'єктів залежить обрана таблиця.



Наступний запит, з використанням msdependencies, поверне ту ж саму інформацію.

-- Об'єкти, від яких залежить зазначений об'єкт

EXEC sp_MSdependencies ' Sales.Customer', null, 1053183 -- Change Table



Якщо ви хочете отримати список залежностей таблиць, ви можете використовувати тимчасову таблицю, щоб відфільтрувати залежності типу.

CREATE TABLE #TempTable1
(
Type INT ,
ObjName VARCHAR(256) ,
Owner VARCHAR(25) ,
Sequence INT
); 

INSERT INTO #TempTable1
EXEC sp_MSdependencies NULL 

SELECT *
FROM #TempTable1
WHERE Type = 8 --Tables 
ORDER BY Sequence ,
ObjName 

DROP TABLE #TempTable1;

Запити до системним уявленням каталогу
Другий метод «реверс-інжинірингу» залежностей у вашій БД — це запити до системним уявленням каталогу, пов'язаних із зовнішніми ключами.

--Independent tables

SELECT Name AS InDependentTables
FROM sys.tables
WHERE object_id NOT IN ( SELECT referenced_object_id
FROM sys.foreign_key_columns )
-- Check for parents
AND object_id NOT IN ( SELECT parent_object_id
FROM sys.foreign_key_columns )
-- Check for Dependents
ORDER BY Name 

-- Tables with dependencies.

SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS ParentTable ,
OBJECT_NAME(parent_object_id) AS DependentTable ,
OBJECT_NAME(constraint_object_id) AS ForeignKeyName
FROM sys.foreign_key_columns
ORDER BY ParentTable ,
DependentTable 

-- Top level of the pyramid tables. Tables with no parents.

SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS TablesWithNoParent
FROM sys.foreign_key_columns
WHERE referenced_object_id NOT IN ( SELECT parent_object_id
FROM sys.foreign_key_columns )
ORDER BY 1 

-- Bottom level of the pyramid tables. 
-- Tables with no dependents. (These are the leaves on a tree.)

SELECT DISTINCT
OBJECT_NAME(parent_object_id) AS TablesWithNoDependents
FROM sys.foreign_key_columns
WHERE parent_object_id NOT IN ( SELECT referenced_object_id
FROM sys.foreign_key_columns )
ORDER BY 1

-- Tables with both parents and dependents. 
-- Tables in the middle of the hierarchy

SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS MiddleTables
FROM sys.foreign_key_columns
WHERE referenced_object_id IN ( SELECT parent_object_id
FROM sys.foreign_key_columns )
AND parent_object_id NOT IN ( SELECT referenced_object_id
FROM sys.foreign_key_columns )
ORDER BY 1;

-- in rare cases, you might find a self-referencing dependent table.
-- Recursive (self) referencing table dependencies. 

SELECT DISTINCT
OBJECT_NAME(referenced_object_id) AS ParentTable ,
OBJECT_NAME(parent_object_id) AS ChildTable ,
OBJECT_NAME(constraint_object_id) AS ForeignKeyName
FROM sys.foreign_key_columns
WHERE referenced_object_id = parent_object_id
ORDER BY 1 ,
2;

Використання CTE
Третій метод, для отримання ієрархії залежностей — використання рекурсивного CTE.

-- How to find the hierarchical dependencies
-- Solve recursive queries using Common Table Expressions (CTE)

WITH TableHierarchy ( ParentTable, DependentTable, Level )
AS (

-- Anchor member definition (First level group to start the process)
SELECT DISTINCT
CAST(NULL AS INT) AS ParentTable ,
e.referenced_object_id AS DependentTable ,
0 AS Level
FROM sys.foreign_key_columns AS e
WHERE e.referenced_object_id NOT IN (
SELECT parent_object_id
FROM sys.foreign_key_columns )

-- Add filter dependents of only one parent table
-- AND Object_Name(e.referenced_object_id) = 'User'

UNION ALL

-- Recursive member definition (Find all the layers of dependents)
SELECT Distinct --
e.referenced_object_id AS ParentTable ,
e.parent_object_id AS DependentTable ,
Level + 1
FROM sys.foreign_key_columns AS e
INNER JOIN TableHierarchy AS d
ON ( e.referenced_object_id ) = 
d.DependentTable
)

-- Statement that executes the CTE

SELECT DISTINCT
OBJECT_NAME(ParentTable) AS ParentTable ,
OBJECT_NAME(DependentTable) AS DependentTable ,
Level
FROM TableHierarchy
ORDER BY Level ,
ParentTable ,
DependentTable;

ВисновокТаким чином, за годину або два, можна отримати непогане уявлення про нутрощах будь-якої бази даних, використовуючи методи «реверс-інжинірингу», описані вище.

Примітка перекладача: всі запити в тексті (за винятком одного, в тексті він відзначений) будуть працювати на SQL Server 2005 SP3 і в більш пізніх редакціях. Текст досить об'ємний, я старався як міг його вичитати і знайти свої помилки (стилістичні, синтаксичні, змістові та інші), але, напевно, щось не помітив, напишіть мені в лічку, будь ласка, якщо щось буде різати око.

Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.