SQLServer常用查询(sql执行状态、表、字段注释等)_查询sqlserver语句执行情况

当前库数据量

SELECT DB_NAME() AS [dbname], SUM(convert(bigint,B.ROWS)) AS [rows] 
FROM sys.sysobjects  A 
JOIN sys.sysindexes B ON A.id = B.id
WHERE A.xtype = 'U' AND B.indid IN (0, 1) AND B.rows>0
1
2
3
4

当前库每个表数据量

SELECT DB_NAME() AS [dbname], A.name, SUM(convert(bigint,B.ROWS)) AS [rows] 
FROM sys.sysobjects  A 
JOIN sys.sysindexes B ON A.id = B.id
WHERE A.xtype = 'U' AND B.indid IN (0, 1) AND B.rows>0
GROUP BY A.name
1
2
3
4
5

当前库所有表

select * from sys.sysobjects where xtype = 'U'
1

当前库注释查询

SELECT
    表名       = CASE WHEN a.colorder=1 THEN d.name ELSE '' END,
    表说明     = CASE WHEN a.colorder=1 THEN ISNULL(f.value,'') ELSE '' END,
    字段序号   = a.colorder,
    字段名     = a.name,
    标识       = CASE WHEN COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 THEN '√'ELSE '' END,
    主键       = CASE WHEN EXISTS(SELECT 1 FROM sysobjects WHERE xtype='PK' AND parent_obj=a.id AND NAME IN (
                 SELECT NAME FROM sysindexes WHERE indid IN( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) THEN '√' ELSE '' END,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    小数位数   = ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
    允许空     = CASE WHEN a.isnullable=1 THEN '√'ELSE '' END,
    默认值     = ISNULL(e.text,''),
    字段说明   = ISNULL(g.[VALUE],'')
FROM syscolumns a LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects  d ON a.id = d.id AND d.xtype in ('U', 'V') AND d.name <> 'dtproperties'
LEFT  JOIN syscomments e ON a.cdefault = e.id
LEFT  JOIN sys.extended_properties g ON a.id = G.major_id AND a.colid = g.minor_id
LEFT  JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
WHERE d.name = '表名'
ORDER BY a.id, a.colorder

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

正在运行的语句

SELECT DISTINCT
    s.spid                     AS SPID,       -- SQLServer会话ID
    s.open_tran                AS OpenTran,   -- 进程的打开事务数
    s.status                   AS Status,     -- 进程ID状态(正在运行, 背景, rollback, 挂起, 可运行, spinloop, 已挂起)
    s.loginame                 AS LogiName,   -- 登录名
    st.text,
    OBJECT_NAME(st.objectid)   AS ObjectName
FROM sys.sysprocesses s            
OUTER APPLY sys.dm_exec_sql_text(s.sql_handle)  st  -- 进程信息表
WHERE s.status != 'background' 
AND s.cmd != 'TASK MANAGER'
AND s.loginame != ''
AND s.status != 'sleeping'
1
2
3
4
5
6
7
8
9
10
11
12
13

下面是s.status代表的意思：