/* -----------------------------------------------------------------
Script : ListTableInBase.sql
Author : Dariusz 'DBSoft' Brejnak (c) 2010
Created : 2010-09-01
Skrypt wyświetla listę tabel zawartych w aktualnej bazie danych.
Za pomocą procedura sp_spaceused
zwracane są informacje o liczbie wierszy, wielkości plików bazy danych
w KB lub obiektów bazodanowych i liczbie pustych stron w
poszczególnych plikach.
wynik:
servername basename name rows reserved data index_size unsed
MSSQLEXPRESS AdventureWorks Address 19614 4984 2224 2472 288
MSSQLEXPRESS AdventureWorks AddressType 6 48 8 40 0
MSSQLEXPRESS AdventureWorks AWBuildVersion 1 16 8 8 0
........
*/
--use ala
CREATE TABLE #tmp
(
[name] varchar(50),
[rows] int,
[reserved] varchar (20),
[data] varchar (20),
[index_size]varchar(20),
[unsed] varchar(20)
)
CREATE TABLE #tmprows
(
[servername] varchar(50),
[basename] varchar(50),
[name] varchar(50),
[rows] int,
[reserved] varchar (20),
[data] varchar (20),
[index_size]varchar(20),
[unsed] varchar(20)
)
INSERT #tmp EXEC sp_MSforeachtable 'exec sp_spaceused ''?'''
INSERT #tmprows SELECT @@SERVERNAME,
DB_NAME(),
[name],
[rows],
replace(reserved,'KB',''),
replace(data,'KB',''),
replace(index_size,'KB',''),
replace(unsed,'KB','')
FROM #tmp
SELECT [servername],
[basename],
[name],
[rows],
cast([reserved] as int) / 1024.0 [Reserved MB],
cast([data] as int) / 1024.0 [Data MB],
[index_size],
[unsed]
FROM #tmprows
ORDER BY [rows] desc
SELECT [servername],
[basename],
[name],
[rows],
cast([reserved] as int) / 1024.0 [Reserved MB],
cast([data] as int) / 1024.0 'data MB',
[index_size],
[unsed]
FROM #tmprows
ORDER BY cast([data] as int) desc
DROP TABLE #tmp
DROP TABLE #tmprows
Was this helpful?
0 / 0