SQL Server 查看数据库表使用空间系统表

原创已于 2025-08-04 14:25:45 修改 · 682 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

于 2025-01-13 10:26:54 首次发布

SQL Server 专栏收录该内容

36 篇文章

订阅专栏

sp_spaceused (Transact-SQL)

系统表

select * from sys.databases

select * from sys.sysdatabases

select * from sys.tables where [name] = 'sys_car'

select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'sys_car'

select * from sys.objects where type = 'U' and type_desc = 'USER_TABLE'

select * from sys.syscolumns where id=object_id('sys_car') and name='CarName'

select * from sys.extended_properties where minor_id = 0

select * from sys.indexes WHERE type_desc != 'HEAP'

-- 被锁表
SELECT  
    tl.resource_type, 
    tl.resource_database_id, 
    OBJECT_NAME(p.OBJECT_ID) AS ObjectName, 
    p.index_id, 
    tl.request_mode, 
    tl.request_status, 
    tl.request_session_id,
	OBJECT_NAME(tl.resource_associated_entity_id) AS tableName
FROM sys.dm_tran_locks tl 
INNER JOIN sys.partitions p ON tl.resource_associated_entity_id = p.hobt_id 
WHERE tl.resource_type = 'OBJECT' -- 可以根据需要更改资源类型，如 KEY, PAGE, RID 等 
ORDER BY tl.request_session_id;


USE [数据库名称]
GO

-- 数据库文件路径
select * from sys.database_files
select * from sys.master_files

select * from sys.database_mirroring

select * from sys.database_recovery_status

查看某个数据库所有表

USE [dbTest]
GO

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

查看某个数据库所有索引

USE [dbTest]
GO

SELECT
    i.name AS IndexName,
    OBJECT_NAME(i.object_id) AS TableName,
    i.index_id,
    i.type_desc,
    i.is_primary_key,
    i.is_unique,
    i.is_disabled
FROM sys.indexes AS i
INNER JOIN sys.tables AS t ON i.object_id = t.object_id
WHERE i.type_desc != 'HEAP'
ORDER BY TableName,IndexName;

-- 查询某个表的所有索引
-- exec sp_helpindex [表名]
exec sp_helpindex test

查看【字段描述】属于那个【表】

SELECT 
    SCHEMA_NAME(t.schema_id) AS schema_name,
    t.name AS table_name,
    c.name AS column_name,
    ty.name AS data_type,
    c.max_length,
    c.is_nullable,
    convert(nvarchar,ep.[value]) AS column_description
FROM 
    sys.columns c
INNER JOIN 
    sys.tables t ON c.object_id = t.object_id
INNER JOIN 
    sys.types ty ON c.user_type_id = ty.user_type_id
LEFT JOIN 
    sys.extended_properties ep ON ep.major_id = t.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
WHERE 1=1
    and convert(nvarchar,ep.[value]) like '%工号%'
    --and t.name = 'YourTableName' -- 替换YourTableName为你的表名

查看【字段】属于那个【表】；查看那些【表】包含某个【字段】；

SELECT t.*,t.name AS TableName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name = 'CostFee';

SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE c.name = 'CostFee';

查看【数据库】总大小和可用空间

-- 方式 1：查看数据库文件的大小和使用情况
SELECT 
    name AS [FileName],
	type_desc AS [File Type],
    size/128 AS [SizeInMB],
	size/128.0 AS [Current Size in MB 当前大小(MB)],
    FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [Space Used In MB 已用空间(MB)],
    (size - FILEPROPERTY(name, 'SpaceUsed'))/128.0 AS [Available Space In MB 可用空间(MB)]
FROM sys.database_files
WHERE 1=1 
-- AND type_desc = 'LOG';
go

-- 方式 2：查看数据库文件的大小和使用情况
SELECT 
    name AS 'File Name', 
    type_desc AS 'File Type',
	size/128 AS [SizeInMB],
    size/128.0 AS 'Current Size in MB 当前大小(MB)',
	FILEPROPERTY(name, 'SpaceUsed')/128.0 AS [Space Used In MB 已用空间(MB)],
    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB 可用空间(MB)],
	database_id
FROM sys.master_files
WHERE database_id = DB_ID() 
--AND type_desc = 'LOG';
go

-- 查看数据库文件路径，当前大小
SELECT DB_NAME(database_id) AS [Database Name],
[Name] AS [Logical Name],
[Physical_Name] AS [Physical Name],
((size * 8) / 1024) AS [Size(MB)]
FROM sys.master_files
ORDER BY [Size(MB)] DESC
go

-- 存储过程
EXEC sp_spaceused; 
-- unallocated space 未分配空间，可用空间
-- reserved          预留空间

-- 返回值
-- database_name：数据库名称。
-- database_size：数据库大小 (MB)。 database_size 包括数据和日志文件。
-- unallocated space：数据库中未为数据库对象保留的空间。

-- reserved：由数据库中对象分配的空间总量。
-- data：数据使用的空间总量。
-- index_size：索引使用的空间总量。
-- unused：为数据库中的对象保留但尚未使用的空间总量。
-- rows：表中现有的行数。 如果指定的对象是 Service Broker 队列，则此列指示队列中的消息数。
go

每个表的已用空间

-- 方式 1
SELECT db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    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
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB DESC;
go
 
-- 方式 2
SELECT db_name() as DbName,
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 0
GROUP BY t.Name, s.Name, p.Rows
ORDER BY 总共占用空间MB desc;
go
 
-- 方式 3 临时表，索引空间
CREATE TABLE #TableSizes
(
    TableName NVARCHAR(128),     -- 表名称
    RowCounts BIGINT,            -- 表中的总行数
    ReservedSpace NVARCHAR(128), -- 预留空间（总的）
    DataSpace NVARCHAR(128),     -- 数据占用的空间
    IndexSpace NVARCHAR(128),    -- 索引占用的空间
    UnusedSpace NVARCHAR(128)    -- 未使用的空间
)
 
EXEC sp_MSforeachtable @command1="INSERT INTO #TableSizes EXEC sp_spaceused '?'"
 
SELECT TableName
,RowCounts
,ReservedSpace
,UnusedSpace
,DataSpace
,IndexSpace
FROM #TableSizes ORDER BY CAST(REPLACE(ReservedSpace, ' KB', '') AS INT) DESC
 
DROP TABLE #TableSizes
go
 
-- 方式4 存储过程
-- unallocated space 未分配空间，可用空间
-- reserved          预留空间
EXEC sp_spaceused;
 
EXEC sp_spaceused 'test';
 
go

*
*
*