sql server查询数据库总数据条数和占用空间

https://www.cnblogs.com/luxj/p/9766405.html

https://blog.csdn.net/nopicture/article/details/141722694

1：总条数

select  

        sum(c.row_count) as datacount  
from    sys.indexes a ,  
        sys.objects b ,  
        sys.dm_db_partition_stats c  
where   a.[object_id] = b.[object_id]  
        AND b.[object_id] = c.[object_id]  
        AND a.index_id = c.index_id  
        AND a.index_id < 2  

        AND b.is_ms_shipped = 0 

 

2：每个表的条数

select  b.name as tablename ,  
        a.rowcnt as datacount  
from    sysindexes a ,  
        sysobjects b  
where   a.id = b.id  
        and a.indid < 2  
        and objectproperty(b.id, 'IsMSShipped') = 0 

3、查询某数据库中的所有数据表及其数据总条数所占用的空间大小

SELECT OBJECT_NAME(id) 数据表,
RTRIM(8 * dpages) + 'KB' 占用空间大小,
rows 数据总条数
FROM sysindexes
WHERE indid = 1
ORDER BY rows DESC ,
数据表 ,
reserved DESC;

4、查询数据库文件路径信息

select * from sys.database_files

5、查询数据库文件大小

--当前数据库服务器所有数据库文件大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8.0)/1024 SizeMB
FROM sys.master_files
GO

--查看某一个数据库的文件大小
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8.0)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'database数据库名'
GO