Sqlserver,数据库文件大小和剩余空间,所有表名和行数及空间占用量(Sqlserver, database file size and remaining space, all table names and rows, and space usage)

在MS Sql Server中可以能过以下的方法查询出磁盘空间的使用情况及各数据库数据文件及日志文件的大小及使用利用率:

1、查询各个磁盘分区的剩余空间:

  Exec master.dbo.xp_fixeddrives

2、查询数据库的数据文件及日志文件的相关信息(包括文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径等)

  select * from [数据库名].[dbo].[sysfiles]

  转换文件大小单位为MB:

  select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

3、查询当前数据库的磁盘使用情况:

  Exec sp_spaceused

4、查询数据库服务器各数据库日志文件的大小及利用率

  DBCC SQLPERF(LOGSPACE)

//查询所有表名

select name from sysobjects where xtype=’u’ –modify_date指表结构最后更新日期,并非数据最后更新日期SELECT    
name, object_id, principal_id, schema_id, parent_object_id, type,
type_desc, create_date, modify_date, is_ms_shipped, is_published,                      
is_schema_published, lob_data_space_id, filestream_data_space_id,
max_column_id_used, lock_on_bulk_load, uses_ansi_nulls, is_replicated,                      
has_replication_filter, is_merge_published, is_sync_tran_subscribed,
has_unchecked_assembly_data, text_in_row_limit,                       large_value_types_out_of_rowFROM         sys.tables ORDER BY modify_date DESC

//查询数据库中所有的表名及行数SELECT     a.name, b.rowsFROM         sys.sysobjects AS a INNER JOIN                      sys.sysindexes AS b ON a.id = b.idWHERE     (b.indid IN (0, 1)) AND (a.type = ‘u’)ORDER BY a.name, b.rows DESC

//查询所有的标明及空间占用量\行数SELECT    
OBJECT_NAME(id) AS tablename, 8 * reserved / 1024 AS reserved, RTRIM(8 *
dpages) + ‘kb’ AS used, 8 * (reserved – dpages) / 1024 AS unused,                       8 * dpages / 1024 – rows / 1024 * minlen / 1024 AS freeFROM         sys.sysindexesWHERE     (indid = 1)ORDER BY tablename, reserved DESC

//查询数据库中的所有数据库名SELECT Name FROM Master..SysDatabases ORDER BY Name

//查询某个数据库中所有的表名SELECT Name FROM SysObjects Where XType=’U’ ORDER BY Name

//获取一个数据库中的所有表的名称、一个表中所有字段的名称

SELECT (case when a.colorder=1 then d.name else null end) 表名,  a.colorder 字段序号,a.name 字段名,(case when COLUMNPROPERTY( a.id,a.name,’IsIdentity’)=1 then ‘√’else ” end) 标识, (case when (SELECT count(*) FROM sysobjects  WHERE (name in (SELECT name FROM sysindexes  WHERE (id = a.id) AND (indid in  (SELECT indid FROM sysindexkeys  WHERE (id = a.id) AND (colid in  (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  AND (xtype = ‘PK’))>0 then ‘√’ else ” end) 主键,b.name 类型,a.length 占用字节数,  COLUMNPROPERTY(a.id,a.name,’PRECISION’) as 长度,  isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as 小数位数,(case when a.isnullable=1 then ‘√’else ” end) 允许空,  isnull(e.text,”) 默认值,isnull(g.[value], ‘ ‘) AS [说明]FROM  syscolumns a left join systypes b on a.xtype=b.xusertype  inner join sysobjects d on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’ left join syscomments e on a.cdefault=e.id  left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_idleft join sys.extended_properties f on d.id=f.class and f.minor_id=0where b.name is not null–WHERE d.name=’要查询的表’ –如果只查询指定表,加上此条件order by a.id,a.colorder

————————

In MS SQL server, you can query the usage of disk space and the size and utilization of each database data file and log file through the following methods:

< strong > 1. Query the remaining space of each disk partition: < / strong >

  Exec master.dbo.xp_fixeddrives

< strong > 2. Query the data files and log files of the database (including file group, current file size, file maximum, file growth setting, file logical name, file path, etc.) < / strong >

Select * from [database name]. [dbo]. [sysfiles]

Converted file size in MB:

  select name, convert(float,size) * (8192.0/1024.0)/1024. from [数据库名].dbo.sysfiles

< strong > 3. Query the disk usage of the current database: < / strong >

  Exec sp_spaceused

< strong > 4. Query the size and utilization of each database log file in the database server < / strong >

  DBCC SQLPERF(LOGSPACE)

< strong > / / query all table names < / strong >

select name from sysobjects where xtype=’u’ –modify_date指表结构最后更新日期,并非数据最后更新日期SELECT    
name, object_id, principal_id, schema_id, parent_object_id, type,
type_desc, create_date, modify_date, is_ms_shipped, is_published,                      
is_schema_published, lob_data_space_id, filestream_data_space_id,
max_column_id_used, lock_on_bulk_load, uses_ansi_nulls, is_replicated,                      
has_replication_filter, is_merge_published, is_sync_tran_subscribed,
has_unchecked_assembly_data, text_in_row_limit,                       large_value_types_out_of_rowFROM         sys.tables ORDER BY modify_date DESC

< strong > / / query all table names and rows in the database < / strong > select      a.name, b.rowsFROM          sys.sysobjects AS a INNER JOIN                       sys.sysindexes AS b ON a.id = b.idWHERE      (b.indid IN (0, 1)) AND (a.type = ‘u’)ORDER BY a.name, b.rows DESC

//查询所有的标明及空间占用量\行数SELECT    
OBJECT_NAME(id) AS tablename, 8 * reserved / 1024 AS reserved, RTRIM(8 *
dpages) + ‘kb’ AS used, 8 * (reserved – dpages) / 1024 AS unused,                       8 * dpages / 1024 – rows / 1024 * minlen / 1024 AS freeFROM         sys.sysindexesWHERE     (indid = 1)ORDER BY tablename, reserved DESC

//查询数据库中的所有数据库名SELECT Name FROM Master..SysDatabases ORDER BY Name

//查询某个数据库中所有的表名SELECT Name FROM SysObjects Where XType=’U’ ORDER BY Name

< strong > / / obtain the names of all tables in a database and the names of all fields in a table < / strong >

SELECT (case when a.colorder=1 then d.name else null end) 表名,  a.colorder 字段序号,a.name 字段名,(case when COLUMNPROPERTY( a.id,a.name,’IsIdentity’)=1 then ‘√’else ” end) 标识, (case when (SELECT count(*) FROM sysobjects  WHERE (name in (SELECT name FROM sysindexes  WHERE (id = a.id) AND (indid in  (SELECT indid FROM sysindexkeys  WHERE (id = a.id) AND (colid in  (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  AND (xtype = ‘PK’))>0 then ‘√’ else ” end) 主键,b.name 类型,a.length 占用字节数,  COLUMNPROPERTY(a.id,a.name,’PRECISION’) as 长度,  isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as 小数位数,(case when a.isnullable=1 then ‘√’else ” end) 允许空,  isnull(e.text,”) 默认值,isnull(g.[value], ‘ ‘) AS [说明]FROM  syscolumns a left join systypes b on a.xtype=b.xusertype  inner join sysobjects d on a.id=d.id and d.xtype=’U’ and d.name<>’dtproperties’ left join syscomments e on a.cdefault=e.id  left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_idleft join sys.extended_properties f on d.id=f.class and f.minor_id=0where b.name is not null–WHERE d.name=’要查询的表’ –如果只查询指定表,加上此条件order by a.id,a.colorder