sql查询表的大小
--1、查看表空间的名称及大小
select
t.tablespace_name,
round(sum(bytes/(1024*1024)),0)
ts_size
from
dba_tablespaces
t,
dba_data_files
d
where
t.tablespace_name
=
d.tablespace_name
group
by
t.tablespace_name;
--2、查看表空间物理文件的名称及大小
select
tablespace_name,
file_id,
file_name,
round(bytes/(1024*1024),0)
total_space
from
dba_data_files
order
by
tablespace_name;
3.查看所有表空间使用情况
select
b.file_id
文件ID号,
b.tablespace_name
表空间名,
b.bytes/1024/1024||'M'字节数,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024||'M'
已使用,
sum(nvl(a.bytes,0))/1024/1024||'M'
剩余空间,
round(100
-
sum(nvl(a.bytes,0))/(b.bytes)*100,2)||
'%'
占用百分比
from
dba_free_space
a,dba_data_files
b
where
a.file_id=b.file_id
group
by
b.tablespace_name,b.file_id,b.bytes
order
by
b.file_id;
总有一款适合你！
❷ sql server怎么查看表占用的空间大小
select tabname as '表名',rowsNum as '表数据行数',reserved as '保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'
from #tabName
--where tabName not like 't%'
order by cast(rowsNum as int) desc
❸ 有没有语句能查询SQL数据库中每一个表的大小
--得到数据库中所有表的空间/记录情况
exec sp_MSForEachTable
@precommand=N'
create table ##(
id int identity,
表名 sysname,
字段数 int,
记录数 int,
保留空间 Nvarchar(10),
使用空间 varchar(10),
索引使用空间 varchar(10),
未用空间 varchar(10))',
@command1=N'insert ##(表名,记录数,保留空间,使用空间,索引使用空间,未用空间) exec sp_spaceused ''?''
update ## set 字段数=(select count(*) from syscolumns where id=object_id(''?''))
where id=scope_identity()', @postcommand=N'select * from ## order by id drop table ##'
❹ mysql怎么查看数据库中表的大小
1、查询整个mysql数据库，整个库的大小；单位转换为MB。
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES
❺ sqlserver中如何查询某几个表目前的空间大小
创建存储过程：
CREATE PROCEDURE [dbo].[sys_viewTableSpace]
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE [dbo].#tableinfo(
表名 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
记录数 [int] NULL,
预留空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
使用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
索引占用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
未用空间 [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
)
insert into #tableinfo(表名, 记录数, 预留空间, 使用空间, 索引占用空间, 未用空间)
exec sp_MSforeachtable "exec sp_spaceused '?'"
select * from #tableinfo
order by 记录数 desc
drop table #tableinfo
END
使用的时候直接 ：exec sys_viewtablespace
❻ SQL SERVER里面如何查看一个表已经占据的容量
用如下方法查看（以sqlserver2008r2为例）：
1、登录SQL Server Managment Studio。
2、在左侧的树中找到要查询的表名，如dbo.dept表。
❼ MySQL中查询所有数据库占用磁盘空间大小和单个库中所有表的大小的sql语句
查询所有数据库占用磁盘空间大小的SQL语句：
复制代码
代码如下:
select
TABLE_SCHEMA,
concat(truncate(sum(data_length)/1024/1024,2),'
MB')
as
data_size,
concat(truncate(sum(index_length)/1024/1024,2),'MB')
as
index_size
from
information_schema.tables
group
by
TABLE_SCHEMA
order
by
data_length
desc;
查询单个库中所有表磁盘占用大小的SQL语句：
复制代码
代码如下:
select
TABLE_NAME,
concat(truncate(data_length/1024/1024,2),'
MB')
as
data_size,
concat(truncate(index_length/1024/1024,2),'
MB')
as
index_size
from
information_schema.tables
where
TABLE_SCHEMA
=
'TestDB'
group
by
TABLE_NAME
order
by
data_length
desc;
以上语句测试有效，注意替换以上的TestDB为数据库名
❽ 如何查看SQL2000数据库中所有表的数据量大小
直接在查询分析器运行即可：
declare @id int
declare @type character(2)
declare @pages
int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @id
while @@fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
where objid = @id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
where objid = @id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
where objid = @id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
and objid = @id
fetch next from c_tables
into @id
end
select TableName = (select left(name,60) from sysobjects where id = objid),
Rows = convert(char(11), rows),
ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
DataKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables