常用SQLSERVER语句(死锁、表记录数、外键、表备注)

----------------------死锁
select  request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName from sys.dm_tran_locks where resource_type='OBJECT'
dbcc inputbuffer(进程号)

-----------查表记录数
select a.name as 表名,max(b.rows) as 记录条数 from sysobjects a ,sysindexes b
where a.id=b.id and a.xtype='u'
group by a.name
order by max(b.rows) desc

----------- 查询外键
select object_name(a.parent_object_id) 'tables'
from sys.foreign_keys a
where a.referenced_object_id=object_id('[表名]')


----- 查询表备注
SELECT
convert(varchar(500), A.name) AS table_name,
convert(varchar(500),B.name) AS column_name,
ISNULL(convert(varchar(500), C.value),'') AS Description,
'Column' as DescType
FROM sys.tables A
Inner JOIN sys.columns B ON B.object_id = A.object_id
LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id

 

---- 检查列被其它表引用

SELECT '【'+WMB.name+'】中的（'+WML.name+'）列引用了【'+ZMB.name+'】中的（'+ZML.name+'）列。'
FROM SYS.foreign_key_columns FK JOIN SYS.objects WMB ON FK.parent_object_id=WMB.object_id
JOIN SYS.columns WML ON WMB.object_id=WML.object_id AND parent_column_id=WML.column_id
JOIN SYS.objects ZMB ON FK.referenced_object_id=ZMB.object_id
JOIN SYS.columns ZML ON ZMB.object_id=ZML.object_id AND referenced_column_id=ZML.column_id