[mssql] cheet sheat

컬럼으로 테이블 찾기

특정 컬럼을 갖는 테이블 찾기

컬럼이름으로 테이블 찾기

SELECT T.name AS table_name, C.name AS column_name
FROM sys.tables AS T
INNER JOIN sys.columns AS C
	ON T.object_id = C.object_id
WHERE C.name = '컬럼명'

 

 

 

SQL Server Lock check

락 걸린 디비 서버 정보 확인

DB 디비 교착상태, 디비 락, DB lock, db lock

SELECT
	DB_NAME(tl.resource_database_id) as 'DB Name',
	tl.resource_type,
	tl.resource_associated_entity_id,
	tl.request_mode,
	tl.request_session_id,
	wt.blocking_session_id,
	obj.name as 'Object Name',
	obj.type_desc as 'Object Desc',
	pa.partition_id as 'Partition ID',
	tl.request_status,
	pa.rows as 'Partition rows numbers',
	al.type_desc as 'Index Desc',
	al.container_id as 'Storage container_id'
FROM sys.dm_tran_locks as tl
INNER JOIN sys.dm_os_waiting_tasks as wt
	ON tl.lock_owner_address = wt.resource_address
LEFT JOIN sys.objects obj on obj.object_id = tl.resource_associated_entity_id
LEFT JOIN sys.partitions pa on pa.hobt_id = tl.resource_associated_entity_id
LEFT JOIN sys.allocation_units al on al.allocation_unit_id = tl.resource_associated_entity_id