2 Temmuz 2009 Perşembe

Select table used, reserved,unused data amout and row numbers in a sqlserver 2005 database

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
)

declare @sql varchar(max)
declare cSql CURSOR FOR
select name [SQL]
from sys.tables

OPEN cSql
FETCH NEXT FROM cSql INTO @sql
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @sql, false;
-- select @sql
FETCH NEXT FROM cSql INTO @sql
END
CLOSE cSql
DEALLOCATE cSql
select * from #temp_Table
order by row_count desc
drop TABLE #temp_Table

Hiç yorum yok: