select object_name(id) as 表名, rows as 使用行数, rtrim(8*reserved/1024)+'Mb' as 分配空间, rtrim(8*dpages/1024) as 使用空间, rtrim(8*(reserved-dpages)/1024) as 未使用空间, rtrim(8*dpages/1024-rows/1024*minlen/1024) as 空闲空间 from sysindexes order by dpages desc 试一试这个.
---------------创建表记录表记录 create table tableRecord ( tableName varchar(50) not null, countRecord int null, identRecord int null )--------------------------------创建存储过程,接受数据库名称参数,并返回所有有关该数据库的记录信息 create procedure getTableRecord(@databaseName varchar(50)) as set nocount on declare @sql varchar(8000) declare @getrecordSql varchar(8000) declare @tableName varchar(80)delete from tableRecord set @sql = 'declare tableRecord1 cursor for select name from '+@databaseName+'.dbo.sysobjects where xtype = ''U''' exec (@sql) open tableRecord1 fetch next from tableRecord1 into @tableName while(@@fetch_status=0) begin
set @getrecordSql ='select '''+@tableName+'''as ''表名'' ,count(*) as ''表记录总数'' ,ident_current('''+@databaseName+'.dbo.'+@tableName+''') as ''最大标识符'' from '+@databaseName+'.dbo.'+@tableName insert into tableRecord exec (@getrecordSql) fetch next from tableRecord1 into @tableName end close tableRecord1 deallocate tableRecord1 select * from tableRecord go
rows as 使用行数,
rtrim(8*reserved/1024)+'Mb' as 分配空间,
rtrim(8*dpages/1024) as 使用空间,
rtrim(8*(reserved-dpages)/1024) as 未使用空间,
rtrim(8*dpages/1024-rows/1024*minlen/1024) as 空闲空间
from sysindexes
order by dpages desc
试一试这个.
---------------创建表记录表记录
create table tableRecord
(
tableName varchar(50) not null,
countRecord int null,
identRecord int null
)--------------------------------创建存储过程,接受数据库名称参数,并返回所有有关该数据库的记录信息
create procedure getTableRecord(@databaseName varchar(50))
as
set nocount on
declare @sql varchar(8000)
declare @getrecordSql varchar(8000)
declare @tableName varchar(80)delete from tableRecord
set @sql = 'declare tableRecord1 cursor for select name from '+@databaseName+'.dbo.sysobjects where xtype = ''U'''
exec (@sql)
open tableRecord1
fetch next from tableRecord1 into @tableName
while(@@fetch_status=0)
begin
set @getrecordSql ='select '''+@tableName+'''as ''表名'' ,count(*) as ''表记录总数'' ,ident_current('''+@databaseName+'.dbo.'+@tableName+''') as ''最大标识符'' from '+@databaseName+'.dbo.'+@tableName
insert into tableRecord
exec (@getrecordSql)
fetch next from tableRecord1 into @tableName
end
close tableRecord1
deallocate tableRecord1
select * from tableRecord
go