常用sqlserver 查询DB状态的语句

1. 查各个表占用空间以及记录数

 

select object_name(id) as tablename,8*reserved/1024 as reserved,
rtrim(8*dpages/1024)+'Mb' as used,8*(reserved-dpages)/1024 as unused,
8*dpages/1024-rows/1024*minlen/1024 as free, rows from sysindexes
where indid=1 and object_name(id) like 'dnt%' order by sysindexes.rows desc

 

2. 把各个表的所有char/varchar列找出来 

nchar(type=47 and xtype=239),

char(type=47 and xtype=175)

nvarchar(type=39 and xtype=231)

varchar(type=39 and xtype=167)

 

select object_name(id) as tablename, name,
case xtype
when 239 then 'nchar'
when 175 then 'char'
when 231 then 'nvarchar'
when 167 then 'varchar'
end as chartype from sys.syscolumns
where object_name(id) like 'dnt_%' and ((type=47 and xtype=175)
or (type=39 and xtype=167))
order by object_name(id),colorder