sp_spaceused (Transact-SQL)   

系统表  

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 = 0select * from sys.indexes WHERE type_desc != 'HEAP'

查看某个数据库所有表

USE [dbTest]
GOSELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME

查看某个数据库所有索引

USE [dbTest]
GOSELECTi.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

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

-- 方式 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) DESCDROP TABLE #TableSizes
go-- 方式4 存储过程
-- unallocated space 未分配空间，可用空间
-- reserved          预留空间
EXEC sp_spaceused;EXEC sp_spaceused 'test';go

*
*
*