select datalength(保存图片的字段名) from 表 where 条件
❷ sqlserver数据库文件大小
第一种方法(较简单，看的有些吃力)：
exec sp_MSforeachtable "exec sp_spaceused '?'"
第二种方法(较复杂，但看的比较清楚，原作者不详)：
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table tablespaceinfo --创建结果存储表
(nameinfo varchar(50) ,
rowsinfo int , reserved varchar(20) ,
datainfo varchar(20) ,
index_size varchar(20) ,
unused varchar(20) )
delete from tablespaceinfo --清空数据表
declare @tablename varchar(255) --表名称
declare @cmdsql varchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name
from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1
and o.name not like N'#%%' order by o.name
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
if exists (select * from dbo.sysobjects where id = object_id(@tablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
execute sp_executesql
N'insert into tablespaceinfo exec sp_spaceused @tbname',
N'@tbname varchar(255)',
@tbname = @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注：显示数据库信息
sp_spaceused @updateusage = 'TRUE'
--itlearner注：显示表信息
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
第三种方法：
select object_name(id) tablename,8*reserved/1024 reserved,rtrim(8*dpages/1024)+'Mb' used,8*(reserved-dpages)/1024 unused,8*dpages/1024-rows/1024*minlen/1024 free,
rows,* from sysindexes
where indid=1
order by reserved desc
❸ 如何查看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
❹ sqlserver，不同数据库，两个相同表，表大小差一倍
数据库的初始大小,以及增长率的区别.
数据库的文件的大小并不等同于数据的大小,表也是一样的.
假定原始表,前后累计 insert 一千万条记录有,后来删除了9百万,然后将剩余的1百万导入到 新的表中.
此时,原始表的大小就远大于新的表.当表空间不够的时候,他会自动 扩容,当数据减少时,他并不会自动收缩.
❺ sqlserver sql语句 多条件查询 查询一个表内 单据号最大的记录集。
用子查询吧
select a.门店，b.条码,c.价格 from (select 门店，max(文件号)文件号 from c group by 门店) c left join a on a.条件=c.条件 left join b on b.条件=c.条件 where c.日期 and c.文件号
❻ 如何查询sqlserver数据库中数据的大小
不是很懂你的问题意思，下次提问请描述的更清楚一些；
如果是想知道某一个表占用了多大空间，你可以用下面的语句
useyourDB
go
sp_spaceusedyourTable
你会得到如下结果，各列分别是：
表名；行数；已占用空间；数据占用空间；索引占用空间；未使用空间；
❼ sqlserver怎么看表的大小
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
❽ SQLServer表的大小与表占硬盘空间大小有什么区别，应该怎么去查这两项性能
表的大小一般指表的行数，占用硬盘空间大小就比较复杂了，可以有以下几个参数：
分配空间，数据占用空间及索引占用空间。
执行
EXEC alltablecount
即可显示当前数据库所有表资源占用情况。
执行前，请前建立以下表，和两个存储过程：
1、先建立表：HY_SPACE
CREATE TABLE [HY_SPACE] (
[name] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
[rows] [char] (11) COLLATE Chinese_PRC_CI_AS NULL ,
[reserved] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[data] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[index_size] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,
[unused] [varchar] (18) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
2、建立统计存储过程：
create procere HYspaceused
@objname nvarchar(776) = null,
@updateusage varchar(5) = false
as
declare @idint
declare @typecharacter(2)
declare@pagesint
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpagedec(15,0)
declare @pagesperMBdec(15,0)
create table #spt_space
(
rowsint null,
reserveddec(15) null,
datadec(15) null,
indexpdec(15) null,
unuseddec(15) null
)
if @updateusage is not null
begin
select @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
if @objname IS NOT NULL
begin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end
if @dbname is null
select @dbname = db_name()
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname)
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
if not exists (select * from sysindexes
where @id = id and indid < 2)
if @type in ('P ','D ','R ','TR','C ','RF')
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V '
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ')
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F '
begin
raiserror(15275,-1,-1)
return (1)
end
end
if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
set nocount on
if @id is null
begin
select @dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)
select @bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select database_name = db_name(),
database_size =
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @pagesperMB,15,2)+ ' MB')
print ' '
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
select @pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @pages
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = 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'
end
else
begin
insert into #spt_space (reserved)
select 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
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
INSERT INTO HY_SPACE
select name = object_name(@id),
rows = convert(char(11), rows),
reserved = ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
data = ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
index_size = ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
unused = 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'
RETURN
end
return (0)
GO
3、主存储过程
create procere alltablecount as
declare @name varchar(100)
TRUNCATE TABLE HY_SPACE
declare tablecur cursor for select name from sysobjects where xtype= 'u '
create table #tablecount(tablename varchar(100),reccount int)
open tablecur
fetch next from tablecur into @name
while @@fetch_status!=-1
begin
exec ('insert into #tablecount select tablename='''+@name+''',reccount=count(1) from '+@name+' ')
EXEC HYspaceused @NAME
fetch next from tablecur into @name
end
close tablecur
deallocate tablecur
select * from #tablecount order by reccount desc
select * from HY_SPACE
GO