2,187
edits
Difference between revisions of "MS SQL"
Jump to navigation
Jump to search
→Commands and Maintenance: Added "Database Sizes"
(→Commands and Maintenance: Added Fragmentation) |
(→Commands and Maintenance: Added "Database Sizes") |
||
| Line 144: | Line 144: | ||
SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */ | SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' /* Show base tables only (not views etc) */ | ||
</source> | </source> | ||
=== Database Sizes === | |||
<source lang="sql"> | |||
SELECT | |||
DB_NAME(db.database_id) DatabaseName, | |||
((CAST(mfrows.RowSize AS FLOAT)*8) + (CAST(mflog.LogSize AS FLOAT)*8))/1024 TotalSizeMB, | |||
(CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB, | |||
(CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB, | |||
(CAST(mfstream.StreamSize AS FLOAT)*8)/1024 StreamSizeMB, | |||
(CAST(mftext.TextIndexSize AS FLOAT)*8)/1024 TextIndexSizeMB | |||
FROM sys.databases db | |||
LEFT JOIN (SELECT database_id, SUM(size) RowSize FROM sys.master_files WHERE type = 0 GROUP BY database_id, type) mfrows ON mfrows.database_id = db.database_id | |||
LEFT JOIN (SELECT database_id, SUM(size) LogSize FROM sys.master_files WHERE type = 1 GROUP BY database_id, type) mflog ON mflog.database_id = db.database_id | |||
LEFT JOIN (SELECT database_id, SUM(size) StreamSize FROM sys.master_files WHERE type = 2 GROUP BY database_id, type) mfstream ON mfstream.database_id = db.database_id | |||
LEFT JOIN (SELECT database_id, SUM(size) TextIndexSize FROM sys.master_files WHERE type = 4 GROUP BY database_id, type) mftext ON mftext.database_id = db.database_id | |||
</source> | |||
Source: http://stackoverflow.com/questions/5945360/sql-server-2008-how-to-query-all-databases-sizes | |||
=== Fragmentation === | === Fragmentation === | ||