YgV84MRU

1/* Удаление базы данных. */
2USE master;
3DROP DATABASE IF EXISTS lab10
4GO
5/* Создание базы данных. */
6CREATE DATABASE lab10
7GO
8USE lab10;
9GO
10
11DROP TABLE IF EXISTS Product
12GO
13
14CREATE TABLE Product (
15    ProductId int IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED(ProductId),
16	NameProduct Nvarchar(128) NOT NULL,
17    PriceProduct Money NOT NULL,
18    InfoProduct Nvarchar(128) NULL,
19)
20TRUNCATE TABLE Product
21
22INSERT INTO Product(NameProduct, PriceProduct, InfoProduct)
23VALUES
24    (N'Картина', 100, N'Из дерева'),
25    (N'Корзина', 10, N'Натуральные материалы'),
26    (N'Картонка', 5, N' Картон'),
27    (N'Маленькая собачонка', 1000, N'Шерсть')
28SELECT * FROM Product
29GO
30
31/* Получение подробной информации о блокировках. */
32
33DROP VIEW IF EXISTS GetLocksInfo
34GO
35
36CREATE VIEW GetLocksInfo AS
37SELECT 
38    CASE locks.resource_type
39		WHEN N'OBJECT' THEN OBJECT_NAME(locks.resource_associated_entity_id)
40		WHEN N'KEY'THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
41		WHEN N'PAGE' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
42		WHEN N'HOBT' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
43		WHEN N'RID' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
44		ELSE N'Unknown'
45    END AS objectName,
46    CASE locks.resource_type
47		WHEN N'KEY' THEN (SELECT indexes.name 
48							FROM sys.partitions JOIN sys.indexes 
49								ON partitions.object_id = indexes.object_id AND partitions.index_id = indexes.index_id
50							WHERE partitions.hobt_id = locks.resource_associated_entity_id)
51		ELSE N'Unknown'
52    END AS IndexName,
53    locks.resource_type,
54	DB_NAME(locks.resource_database_id) AS database_name,
55	locks.resource_description,
56	locks.resource_associated_entity_id,
57	locks.request_mode
58FROM sys.dm_tran_locks AS locks
59	WHERE locks.resource_database_id = DB_ID(N'lab10')
60GO