sql语句

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 #