SQL Server数据库使用情况

 

查询数据总条数SQL

SELECT
    SUM (ROWS) [总条数]
FROM
    (
        SELECT
            a.name,
            b. ROWS
        FROM
            sysobjects AS a
        INNER JOIN sysindexes AS b ON a.id = b.id
        WHERE
            (a.type = 'u')
        AND (b.indid IN(0, 1))
    ) a;
 

 

查询空间占用情况

sp_spaceused ;

 

SQL server 统计数据库表数量和列出所有表名称

SELECT count(*) FROM sys.objects WHERE type='U'   --统计表数量

SELECT NAME  FROM sys.objects WHERE type='U'      --列出表名称

或者

SELECT COUNT(*) FROM SysObjects Where XType='U'   --统计表数量

SELECT Name FROM SysObjects Where XType='U'       --列出表名称

 

 

sqlserver查看表占用内存大小SQL

 

--定义表变量
--定义表变量
DECLARE @T TABLE
(
[Name] VARCHAR(max),
[Rows] INT,
[Reserved] VARCHAR(max),
[Data_size] VARCHAR(max),
[Index_size] VARCHAR(max),
[Unused_size] VARCHAR(max)
)

--将表占用情况存放到表变量
INSERT INTO @T
EXEC sp_MSforeachtable "exec sp_spaceused '?'"

SELECT [Name] AS [表名],
[Rows] AS [行数],
[Reserved] AS [全部大小_kb],
[Data_size] AS [数据大小_kb],
[Index_size] AS [索引大小_kb],
[Unused_size] AS [剩余大小_kb],
CAST(REPLACE([Reserved],'KB','') AS INT)/1024 AS [全部大小_mb],
CAST(REPLACE([Data_size],'KB','') AS INT)/1024 AS [数据大小_mb],
CAST(REPLACE([Index_size],'KB','') AS INT)/1024 AS [索引大小_mb],
CAST(REPLACE([Unused_size],'KB','') AS INT)/1024 AS [剩余大小_mb]
FROM @T
order by CAST(REPLACE(reserved,'KB','') AS INT) desc

 

查看 SQL Server 数据库中每个表的占用空间大小，并按从大到小的顺序排序

SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.NAME, p.Rows
ORDER BY 
    TotalSpaceKB DESC;