mssql sqlserver 使用SQL脚本获取 sqlserver 数据表的相关信息的方法分享


摘要:
下文讲述sqlserver中使用sql脚本获取数据表的相关信息的方法分享,如下所示:
实验环境:sql server 2008 R2



一、获取sqlserver数据库下所有数据表及每张数据表所对应的数据行

select a.name as '数据表名称',b.rows as '数据表行术'
from sysobjects a inner join sysindexes b
on a.id = b.id
where a.type = 'u'
and b.indid in (0,1) 
order by b.rows desc


二、获取sqlserver数据库下所有数据表及每张数据表所占用的空间

if exists(select 1 from tempdb..sysobjects where id=object_id('tempdb..#tmp') and xtype='u')
drop table #tmp
go
create table #tmp(
tbName  sysname,
tbRows  bigint,
reserved varchar(100),
data varchar(100),
index_size varchar(100),
unused_size varchar(100)
)

declare @name sys name
declare cur cursor for
select name from sysobjects where xtype='u' order by name
open cur
fetch next from cur into @name
while @@fetch_status=0
begin
insert into #tmp
exec sp_spaceused @name
 

fetch next from cur into @name
end
close cur
deallocate cur

---输出表相关信息

select tbName as '数据表名称',tbRows as '数据表行术',

reserved as '数据表保留大小',data as '数据大小',index_size as '索引大小',unused_size as '未使用大小'
from #tmp