【SQLserver】sqlserver常用命令_sqlserver查询数据库命令

--查询数据库中所有的数据库名
select * from sys.sysdatabases
 
--查询某个数据库中所有的表名
select name  from sys.sysobjects where xtype='U'  
--统计整个数据库的用户表总数
select count(1) from sys.sysobjects where xtype='U'  
--统计整个数数据库用户表的数据行数
select a.name,b.rows from sys.sysobjects as a 
inner join sys.sysindexes as b on a.id=b.id where a.xtype='U'  and b.indid in(0,1)
order by b.rows  desc
 
---查询所有的表以及空间占用量情况
select OBJECT_NAME(id) tablename ,8*reserved/1024 reserved,
RTRIM(8*dpages)/1024 as 'used(M)'
,8*(reserved-dpages)/1024 unused
,8*dpages/1024-rows/1024*minlen/1024 free
from sys.sysindexes where indid in(0,1) order by reserved desc
 
 
---数据行数+空间占用语句
select a.name,b.rows ,OBJECT_NAME(b.id) tablename ,8*b.reserved/1024 reserved,
RTRIM(8*b.dpages)/1024 as 'used(M)'
,8*(b.reserved-b.dpages)/1024 unused
,8*b.dpages/1024-rows/1024*b.minlen/1024 free
from sys.sysobjects as a 
inner join sys.sysindexes as b on a.id=b.id where xtype='U'  and b.indid in(0,1)
order by b.rows  desc
 
 
---查询数据文件大小M
select *,convert(float,size)*(8192/1024)/1024 from dbo.sysfiles
 
---查看当前数据库磁盘使用情况
EXEC sp_spaceused
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35