Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I create a query that will find all the databases on different servers?

I have four servers, PRDSVR, UATSVR, DEVSVR, TRNSVR; with either copies of the same database on each server or standalone databases that may only be on one or two servers.

I would like to create a query that searches for all databases, listing the server, databases on that server, and the size of each of the databases. How can I achieve this?

Thanks in advance for any suggestions.

share|improve this question

3 Answers 3

up vote 0 down vote accepted

Four part naming convention.

Add a linked server that will define the security context for connecting, then refer to PRDSVR.master.sys.databases and the like.

share|improve this answer
    
Using your suggestion I've used the following query; –  Andrew Robertson Mar 20 at 11:52
    
SELECT mf.database_id, mf.name, mf.size FROM [LINKEDSERVER1].master.sys.master_files AS mf WHERE mf.file_id = 1; Would this work or would this give misleading results? –  Andrew Robertson Mar 20 at 12:05
    
That should work just fine. –  Rob Farley Mar 20 at 16:06

I would like to create a query that searches for all databases, listing the server, databases on that server, and the size of each of the databases. How can I achieve this?

The easiest way of doing is using SSMS --> Registered Servers

enter image description here

enter image description here

Below is the code that will give you the database filesize, space used and free space:

DECLARE
    @SqlStatement nvarchar(MAX)
    ,@DatabaseName sysname;

IF OBJECT_ID(N'tempdb..#DatabaseSpace') IS NOT NULL
    DROP TABLE #DatabaseSpace;

CREATE TABLE #DatabaseSpace(
    DATABASE_NAME   sysname
    ,LOGICAL_NAME   sysname
    ,FILE_SIZE_MB   decimal(12, 2)
    ,SPACE_USED_MB  decimal(12, 2)
    ,FREE_SPACE_MB  decimal(12, 2)
    ,FILE_NAME      sysname
    );

DECLARE DatabaseList CURSOR LOCAL FAST_FORWARD FOR
    SELECT name FROM sys.databases;

OPEN DatabaseList;
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM DatabaseList INTO @DatabaseName;
    IF @@FETCH_STATUS = -1 BREAK;
    SET @SqlStatement = N'USE '
        + QUOTENAME(@DatabaseName)
        + CHAR(13)+ CHAR(10)
        + N'INSERT INTO #DatabaseSpace
    SELECT
        [DATABASE_NAME] = DB_NAME()
        ,[LOGICAL_NAME] = f.name
        ,[FILE_SIZE_MB] = CONVERT(decimal(12,2),round(f.size/128.000,2))
        ,[SPACE_USED_MB] = CONVERT(decimal(12,2),round(fileproperty(f.name,''SpaceUsed'')/128.000,2))
        ,[FREE_SPACE_MB] = CONVERT(decimal(12,2),round((f.size-fileproperty(f.name,''SpaceUsed''))/128.000,2))
        ,[FILENAME] = f.name
    FROM sys.database_files f;';

    EXECUTE(@SqlStatement);

END
CLOSE DatabaseList;
DEALLOCATE DatabaseList;

SELECT * FROM #DatabaseSpace;

DROP TABLE #DatabaseSpace;;
GO

Results:

enter image description here

share|improve this answer

First create the query to call sys.databases and sys.dm_io_virtual_file_stats on one server.

Then you can either use SSMS with a http://technet.microsoft.com/en-us/library/bb964743.aspx to run it on multiple servers simultaneously, or use something like linked servers and execute at or openquery or four part naming - ideally, designate one server your "monitoring" server, and set up SQL Server Agent jobs on all four to put information into one table so you can watch trends.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.