Interview questions: SQL statement: Find the table size in the database and sort the list

A few days to a company in Pudong to interview, feel that there is a database query questions did not answer well, self-summary, the next attention.

Title: The size of tables in the database, and arranged out. Write the corresponding SQL statement.

The code is as follows:

CREATE PROCEDURE [dbo].[Gettablespace]@dbNamesysname= "',--database name, default current database@tableNamesysname= "',--table name, default all tables@columnName varchar( -)= "',--column names, sorting with@sort       varchar(4)= "'     --ASC Ascending, desc descending asIF(@dbName = "')SET @dbName = db_name()ELSE IF(CHARINDEX('M0A2_DB2',@dbName)> 0)SET @dbName = 'm0a2_db2.' + @dbNameIF(@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 is used > data then (used-data) ELSE 0 End) * 8) Index_size, (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_p Age_count + Row_overflow_used_page_count End) Data,sum (case if (index_id < 2) then Row_count ELSE 0 end) Rowsfrom /c4>' + @dbName + '. Sys.dm_db_partition_stats GROUP by object_id) BON a.object_id = b.object_id WHERE a.name like" " + @tableName + " "ORDER by' + @columnName + ' ' + @sort)GOexecGettablespace"',"','Data','desc'

Interview questions: SQL statement: Find the table size in the database and sort the list