I'm not sure how the optimizer would handle them. It would be easy enough to come up with a test query and then execute it via xp_execresultset and a dynamic sql method.

The main advantage of xp_execresultset is that is allows a query to be run in the context of another database without having to switch the current connection to that context. In the article I used FILEPROPERTY which only runs against the current database. I could do this in dynamic SQL (and someone in the forums of Boris Baliner’s article on “Quickly Viewing Available Space” (http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp) did just that) However, the result was quite long and required a lot of context switching.

I don't know if xp_execresultset can take advantage of caching but I wouldn't use it in any situation where that might be necessary in any case. As I stated, this is an undocumented procedure and Microsoft dropped it in SQL 2005. Using this procedure in an application would be ill-advised. I presented it as a tool for administrators as an alternative to writing long dynamic sql-based scripts.

Yes, it executes under the same SPID. To determine this add a ,@@SPID to the end of the query in the script, as shown below:

           SET @SQLCmd='SELECT ''SELECT name AS [File],

                  filename as [File Name],

                  CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB],

                  CAST(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0 as DECIMAL(10,2)) as [Space Used],

                  CAST(size/128.0-(FILEPROPERTY(name, ''''SpaceUsed'''')/128.0) AS DECIMAL(10,2)) AS [Available Space] ,@@SPID

                  FROM SYSFILES'''