Many times during our day to day operations, a DBA needs to gather info regarding databases that is easy to get for a single database but for multiple database it become s abit of challenge. One similar thing is getting database sizes of all databases on a SQL Server . For a single database sp_helpdb works well but when you have a large number of databases on a instance its time taking and there are chances of error in reporting.Moreover, its not the way DBAs do things – one by one. Here is a short script to give the size of all databases in MBs. A cursor has been used to iterate through all the database and store the output of sp_helpdb into a temprary table. This table is then queried to get the size.
CREATE TABLE #DatabaseInfo (name varchar(100), db_size varchar(255), owner varchar(20), dbid smallint, created smalldatetime, status varchar(250), compatibility_level varchar(250)) INSERT INTO #DatabaseInfo exec SP_HELPDB SELECT * FROM #DatabaseInfo order by name drop table #DatabaseInfo
One can also use sp_MSforEachDB stored procedure for above task.
