MSSQL-数据统计

/*需要导的数据库结构有：表、视图、存储过程、函数、触发器*/

--表（129）
select name from sysobjects where xtype='U' order by name

--视图（57）
select name from sysobjects where xtype='V' order by name

--存储过程（310）
select name from sysobjects where xtype='P' order by name

--函数（1-1）
select name from sysobjects where xtype='FN' order by name

--触发器（25）
select name from sysobjects where xtype='TR' order by name

--////////////////////查询表大小\\\\\\\\\\\\\\\\\\\\\
/*
CREATE TABLE [dbo].[a_lsj](
[name] [nvarchar](50) NULL,
[rows] [int] NULL,
[reserver] [nvarchar](50) NULL,
[data] [nvarchar](50) NULL,
[index_size] [nvarchar](50) NULL,
[unused] [nvarchar](50) NULL
) ON [PRIMARY]
*/
--组装查询表大小语句，并插入临时表
select 'INSERT INTO [dbo].[a_lsj] exec sp_spaceused '+ name from sysobjects where xtype='U' order by name

-- 处理数据格式
update [a_lsj] set reserver = replace(reserver,' KB','')
update [a_lsj] set data = replace(data,' KB','')
update [a_lsj] set index_size = replace(index_size,' KB','')
update [a_lsj] set unused = replace(unused,' KB','')

select * from [a_lsj] order by name
select * from [a_lsj] order by CAST([rows] AS INT) desc
select * from [a_lsj] order by CAST(reserver AS INT) desc
select * from [a_lsj] order by CAST(data AS INT) desc

--DROP TABLE [dbo].[a_lsj]