create table #
(tb_name nvarchar(100),
tb_rows bigint,
tb_reserved nvarchar(50),
tb_data nvarchar(50),
tb_index_size nvarchar(50),
tb_unused nvarchar(50),
tb_object_id nvarchar(50),
tb_columns_count int,
tb_triggers_count int,
tb_create_date datetime,
tb_modify_date datetime
)
exec sp_msforeachtable @command1 = 'insert into #
(tb_name,tb_rows,tb_reserved,tb_data,tb_index_size,tb_unused)
exec sp_spaceused [?]',
@command2 = 'update # set tb_object_id = object_id(tb_name)',
@postcommand = 'update # set tb_columns_count = (
select COUNT(1) from sys.columns
where object_id = tb_object_id);
update # set tb_triggers_count = t.cnt
from # join (select parent_id,COUNT(1) as cnt from sys.triggers
where is_disabled = 0
group by parent_id) as t on t.parent_id = #.tb_object_id;
update # set tb_create_date=create_date,tb_modify_date=modify_date
from sys.tables as st join # on st.object_id = #.tb_object_id'
select ROW_NUMBER() OVER (ORDER BY tb_rows desc) as row_index,
tb_object_id as 对象编号,
tb_name as 表名,
tb_rows as 行数,
tb_reserved as 保留大小,
tb_data as 空间大小,
tb_index_size as 索引大小,
tb_unused as 未使用,
tb_columns_count as 列数,
tb_triggers_count as 触发器数,
tb_create_date as 创建日期,
tb_modify_date as 修改日期
from #
order by tb_rows desc
drop table #
sql语句
最新推荐文章于 2024-08-17 22:34:52 发布