Sql-server – Database Files through Linked Servers

@cicik, if you are looping through the databases at your local server like you mentioned, assuming you already have the list of remote databases stored somewhere in a local table, you could loop through the databases (e.g. cursor) and execute,

SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];

This should put the remote database context at the database you specified, and hence it works for FILEPROPERTY function and sys.database_files dmv that return values only for the current database.

Also, you might want to consider extracting the list of databases on the remote server and run the script on each execution at the remote server (client side) so you won't have to create the view on each database, and won't miss any new databases created without your knowledge.

UPDATE If you want to use script to dynamically extract the data from the linked server without adding the view on every database,

DECLARE @remotecmd nvarchar(max);

SET @remotecmd = N'
DECLARE @db     sysname,
        @cmd    nvarchar(max);

CREATE TABLE #dbspace 
(
    DataFileName nvarchar(128),
    DataPhysicalName nvarchar(260),
    DataTotalSizeInMB decimal(17,6),
    DataSpaceUsedInMB decimal(17,6),
    DataAvailableSpaceInMB decimal(17,6)
);

DECLARE dbcur CURSOR
LOCAL FAST_FORWARD
FOR SELECT name
    FROM sys.databases
    WHERE [state] = 0;

OPEN dbcur;
FETCH NEXT FROM dbcur INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = N''USE '' + QUOTENAME(@db) + N''; '';
SET @cmd = @cmd + 
N''
INSERT INTO #dbspace
SELECT 
    name AS [DataFileName], 
    physical_name AS [DataPhysicalName],
    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB], 
    ROUND(CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataSpaceUsedInMB], 
    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB] 
FROM  [sys].[database_files] WHERE type = 0'';

EXEC (@cmd);

FETCH NEXT FROM dbcur INTO @db;
END

CLOSE dbcur;
DEALLOCATE dbcur;

SELECT 
    DataFileName,
    DataPhysicalName,
    DataTotalSizeInMB,
    DataSpaceUsedInMB,
    DataAvailableSpaceInMB
FROM #dbspace;

DROP TABLE #dbspace;'

EXEC (@remotecmd) AT [YourLinkedServer];

Make sure your login has the access to all the databases on the remote server.

Don't use a linked server for this. It will likely be slow. Linked servers are suitable for small queries over remote databases, not for bulk loading data.

I would use SSIS or whichever ETL system you feel more comfortable with.