TSQL Error in sp_MSforeachdb

Transact-SQL
1

Hi experts. I'm trying to loop thru all databases to pull file sizes.

This works fine, but it only returns info for the current database:
Select DB_NAME() as DatabaseName,size, fileproperty(name, 'SpaceUsed') as Used
From dbo.sysfiles

When I add the foreach I have problems:

EXEC sp_MSforeachdb 'USE ? SELECT ''?'',Select DB_NAME() as DatabaseName,size, fileproperty(name, 'SpaceUsed') as Used'
From dbo.sysfiles

error: Incorrect syntax near 'SpaceUsed'

I just can't figure it out.
Thanks for any tips.

2

I would guess that SpaceUsed needs two single quotes on each side. And there shouldn't be a comma after the first SELECT.

EXEC sp_MSforeachdb '
	USE ? 
	SELECT ''?''
	Select DB_NAME() as DatabaseName,size, 
	fileproperty(name, ''SpaceUsed'') as Used
From dbo.sysfiles';
1 Like
3

hi graz

I am not jbates99 = the poster of this post
I am just a casual browser

i tested your solution
Working fine
:+1::+1:

1 Like
4

Thank you, grazz (and harishgg1). I'll try it when I return to the office. Really appreciate the tip!