1、能不能求出数据库中的所有表的大小并按降序排列，
2、能不能求出每个表的最后更新时间。
------解决方案--------------------
http://blog.csdn.net/beirut/article/details/7753199
每个表的最后更新时间2000的话不好办
------解决方案--------------------
- SQL code
--from beirut's blog--用游标处理 查询每个表的大小并排序DECLARE @TABLENM SYSNAME, @CNT INT, @TOPN INTDECLARE TABLE_SPACE CURSOR FAST_FORWARD FOR SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'SELECT @CNT = 0, @TOPN = 0CREATE TABLE #TMPUSAGE ( NAME SYSNAME, ROWS INT, RESERVED VARCHAR(20), DATA VARCHAR(20), INDEX_SIZE VARCHAR(20), UNUSED VARCHAR(20) )OPEN TABLE_SPACEFETCH NEXT FROM TABLE_SPACE INTO @TABLENMWHILE @@FETCH_STATUS = 0 AND @CNT <= @TOPNBEGIN INSERT INTO #TMPUSAGE EXEC SP_SPACEUSED @TABLENM, 'TRUE' IF @TOPN <> 0 SELECT @CNT = @CNT +1 FETCH NEXT FROM TABLE_SPACE INTO @TABLENM ENDCLOSE TABLE_SPACEDEALLOCATE TABLE_SPACESELECT *FROM #TMPUSAGE ORDER BY CONVERT(INT,LEFT(RESERVED, LEN(RESERVED)- 3)) DESCIF (SELECT OBJECT_ID('TEMPDB..#TMPUSAGE') ) IS NOT NULLDROP TABLE #TMPUSAGE
------解决方案--------------------
- SQL code
DECLARE @table_name VARCHAR(500) DECLARE @schema_name VARCHAR(500) DECLARE @tab1 TABLE( tablename VARCHAR (500) collate database_default ,schemaname VARCHAR(500) collate database_default ) CREATE TABLE #temp_Table ( tablename sysname ,row_count INT ,reserved VARCHAR(50) collate database_default ,data VARCHAR(50) collate database_default ,index_size VARCHAR(50) collate database_default ,unused VARCHAR(50) collate database_default ) INSERT INTO @tab1 SELECT Table_Name, Table_Schema FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' DECLARE c1 CURSOR FOR SELECT Table_Schema + '.' + Table_Name FROM information_schema.tables t1 WHERE TABLE_TYPE = 'BASE TABLE' OPEN c1 FETCH NEXT FROM c1 INTO @table_name WHILE @@FETCH_STATUS = 0 BEGIN SET @table_name = REPLACE(@table_name, '[',''); SET @table_name = REPLACE(@table_name, ']',''); -- make sure the object exists before calling sp_spacedused IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name)) BEGIN INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false; END FETCH NEXT FROM c1 INTO @table_name END CLOSE c1 DEALLOCATE c1 SELECT t1.* ,t2.schemaname FROM #temp_Table t1 INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename ) ORDER BY schemaname,t1.tablename; DROP TABLE #temp_Table