面试题：求SQL语句:求数据库内各表大小,并排序列出

浏览数：18 / 时间：2015年06月12日

几天去浦东一家公司去面试，感觉有一道关于数据库查询的题目没答好，自我总结下，下次注意。

题目：求数据库内各表大小，并排列出。写出相应的SQL语句。

代码如下：

CREATE PROCEDURE [dbo].[GetTableSpace]
@dbName     sysname = ‘‘,       --数据库名，默认当前数据库
@tableName  sysname = ‘‘,       --表名，默认全部表
@columnName varchar(50) = ‘‘,   --列名，排序用
@sort       varchar(4) = ‘‘     --asc升序，desc降序
AS
IF (@dbName = ‘‘) SET @dbName = DB_Name()
ELSE IF (CHARINDEX(‘M0A2_DB2‘,@dbName) > 0) SET @dbName = ‘M0A2_DB2.‘ + @dbName
IF (@tableName = ‘‘) SET @tableName = ‘%‘
IF (@columnName = ‘‘) SET @columnName = ‘name‘
IF (@sort = ‘‘) SET @sort = ‘asc‘
 
EXEC (‘SELECT  a.name,rows as int,(reserved * 8) reserved,(data * 8)data,
((CASE WHEN used > data THEN (used - data) ELSE 0 END) * 8)index_size,
((CASE WHEN reserved > used THEN (reserved - used) ELSE 0 END) * 8)unused 
 FROM ‘ + @dbName + ‘.sys.tables a INNER JOIN 
(SELECT object_id,SUM (reserved_page_count)reserved,SUM (used_page_count)used,
SUM (CASE
  WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
  ELSE lob_used_page_count + row_overflow_used_page_count END)data,
SUM (CASE WHEN (index_id < 2) THEN row_count ELSE 0    END)rows
FROM ‘ + @dbName + ‘.sys.dm_db_partition_stats GROUP BY object_id)b
ON a.object_id = b.object_id WHERE a.name LIKE ‘‘‘ + @tableName + ‘‘‘ ORDER BY ‘ + @columnName + ‘ ‘ + @sort)
GO
exec GetTableSpace ‘‘,‘‘,‘data‘,‘desc‘