Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
raul11
Starting Member
48 Posts |
 Posted - 2011-09-23 : 09:24:47
|
| can anyone let me know or have a script which will provide me details example database name, it size,unallocated space etc means all information about all the databases currently on that instance |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2011-09-23 : 09:28:56
|
sp_spaceused ?
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
raul11
Starting Member
48 Posts |
Posted - 2011-09-23 : 09:55:00
|
| does sp_spaceused provides information for all the databases on that instance? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-09-23 : 11:51:30
|
| Run it per-database. You can use sp_MSForEachDB to do so.--Gail ShawSQL Server MVP |
|
|
|
Cindyaz
Yak Posting Veteran
73 Posts |
Posted - 2011-09-30 : 11:26:36
|
| this is a nice script by Jeff Moden. Works only on 2005 and later versions. Alternatively you can use sysaltfiles as well (or sysfiles for getting information per database).SELECT DB_NAME(database_id) AS DatabaseName, CAST([Name] AS varchar(20)) AS NameofFile,CAST(physical_name AS varchar(100)) AS PhysicalFile,type_desc AS FileType,((size * 8)/1024) AS FileSize,MaxFileSize = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'WHEN max_size = 0 THEN 'NO_GROWTH' WHEN max_size <> -1 OR max_size <> 0 THEN CAST(((max_size * 8) / 1024) AS varchar(15))ELSE 'Unknown'END,SpaceRemainingMB = CASE WHEN max_size = -1 OR max_size = 268435456 THEN 'UNLIMITED'WHEN max_size <> -1 OR max_size = 268435456 THEN CAST((((max_size - size) * 8) / 1024) AS varchar(10))ELSE 'Unknown'END,Growth = CASE WHEN growth = 0 THEN 'FIXED_SIZE'WHEN growth > 0 THEN ((growth * 8)/1024)ELSE 'Unknown'END,GrowthType = CASE WHEN is_percent_growth = 1 THEN 'PERCENTAGE'WHEN is_percent_growth = 0 THEN 'MBs'ELSE 'Unknown'ENDFROM master.sys.master_filesWHERE state = 0AND type_desc IN ('LOG', 'ROWS')ORDER BY database_id, file_id |
|
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-03 : 10:28:18
|
| hi ,try using SELECT * FROM master..sysdatabasesgood luckpaul Tech |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
|
|
|
|
|
db size