sql server运维的一些常用脚本

1.统计数据库的所有表信息。

使用sql语句查看

    SELECT a.name as 表名, b.rows as 总行数, convert(decimal(9,4),(b.rows/10000.00)) as [总行数（万）],b.reserved as '保留大小',b.used as '使用大小',b.dpages as '数据页'
      FROM sysobjects AS a
INNER JOIN sysindexes AS b ON a.id = b.id
     WHERE a.type = 'u' AND b.indid IN (0, 1)
  ORDER BY b.rows DESC

 

 使用存储过程查看

--drop table #test_space
create table #test_space(   
name varchar(255),   
[rows] int,   
reserved varchar(50),   
data varchar(50),   
index_size varchar(50),   
unused varchar(50)   
)  
   
insert into #test_space   
exec sp_MSforeachtable "exec sp_spaceused '?'"  
  
select * from  #test_space     
order by cast(replace(reserved,'KB','') as int) desc 

 

--drop table #test_space
create table #test_space(   
name varchar(255),   
[rows] decimal(18,0),   
reserved varchar(50),   
data varchar(50),   
index_size varchar(50),   
unused varchar(50)   
)  
   
insert into #test_space   
exec sp_MSforeachtable "exec sp_spaceused '?'"  
  
select 
name,
rows 总记录,
ltrim(cast(cast(rows/10000 as decimal(9,2)) as varchar))+'万' as [总记录(万)],
reserved,
ltrim(cast(
cast((cast(replace(reserved,'KB','') as decimal)/1024) as decimal(9,2))
 as varchar))+' MB' 占用空间,
data,index_size,unused from  #test_space     
order by rows desc

 

2.获取存储过程、表、自定义函数的最后修改时间

select [name], create_date, modify_date, type_desc
 FROM sys.all_objects
 where 
 (type_desc = N'SQL_STORED_PROCEDURE' OR 
 type_desc = N'SQL_TABLE_VALUED_FUNCTION' OR 
 type_desc = N'SQL_SCALAR_FUNCTION' OR 
 type_desc = N'USER_TABLE') AND schema_id <> 4
 order by type_desc asc, modify_date desc