How to select the database id with sysfiles using sp_msforeachdb

The following example demonstrates how to gather the database id along with sysfiles metadata using the procedure sp_msforeachdb.   This could be useful to collect both a detail and a summary view of database file footprint.


CREATE TABLE #sysfilesplusdatabaseid
(databaseid int, databasename sysname, fileid int, groupid int, size int, maxsize int, growth int, status int, perf int, name sysname, filename varchar(500))

INSERT #sysfilesplusdatabaseid
EXEC sp_msforeachdb @Command1 = 'SELECT DB_ID(''?'') as databaseid, ''?'' as databasename, * From ?..sysfiles'

This will allow you build a view of the total database size by doing a sum of the file sizes grouped by the database.

SELECT databaseid, databasename, SUM((size*8)/1024) as RawSizeMb
FROM #sysfilesplusdatabaseid
GROUP BY databaseid, databasename

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s