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