問題描述

我需要獲取計(jì)算機(jī)上存在的 SQL 服務(wù)器實(shí)例列表，獲取每個(gè)實(shí)例中的數(shù)據(jù)庫(kù)列表，然后確定每個(gè)數(shù)據(jù)庫(kù)占用多少空間.

I need to get a list of SQL server instances present on a computer, get a list of databases in each instance, and then determine how much space each database is taking up.

我可以輕松地從注冊(cè)表中獲取實(shí)例名稱，但我無(wú)權(quán)查詢表以獲取數(shù)據(jù)庫(kù)名稱.有沒有其他方法可以做到這一點(diǎn)，也許是 WMI?

I can easily grab the instance names from the registry, but I don't have access to query the tables to get the names of the databases. Is there another way of doing this, maybe though WMI?

推薦答案

經(jīng)過一番挖掘，我終于找到了 WMI Class，它可以獲取我需要的信息.在我有 3 個(gè) SQL Server 實(shí)例的服務(wù)器上，我在以下類中找到了我的數(shù)據(jù)

After some digging around, I finally found the WMI Class that will get my the info I need. On a server where I have 3 instances of SQL Server, I found my data in the following classes

Win32_PerfFormattedData_MSSQLINST2_MSSQLINST2Databases
Win32_PerfFormattedData_MSSQLINST3_MSSQLINST3Databases
Win32_PerfFormattedData_MSSQLSERVER_SQLServerDatabases

我的實(shí)例是 MSSQLINST2、MSSQLINST3 和 MSSQLSERVER.我無(wú)法弄清楚命名方案，所以我不得不查看所有類以找出我需要的信息.無(wú)論如何，這是有效的代碼.也許有人會(huì)發(fā)現(xiàn)它很有用.

My instances are MSSQLINST2, MSSQLINST3 and MSSQLSERVER. I couldn't figure out the naming scheme, so I had to look though all the classes to find out the information I needed. Anyway, here's the code that's working. Maybe someone will find it useful.

ManagementObjectSearcher sqlInstancesSearcher = new ManagementObjectSearcher(
    new ManagementScope(@"Root\Microsoft\SqlServer\ComputerManagement10"),
    new WqlObjectQuery("select * from SqlServiceAdvancedProperty where propertyindex = 12"),
    null);

foreach (ManagementObject instance in sqlInstancesSearcher.Get())
{
    string instanceName = instance["ServiceName"].ToString().Replace("$", String.Empty);
    Console.WriteLine("INSTANCE: " + instanceName);

    ManagementObjectSearcher classNameSearcher = new ManagementObjectSearcher(
        new ManagementScope(@"root\cimv2"),
        new WqlObjectQuery("select * from meta_class where __CLASS Like 'Win32_PerfFormattedData_" + instanceName + "%Databases%'"),
        null);

    foreach (ManagementClass wmiClass in classNameSearcher.Get())
    {
        string className = wmiClass["__CLASS"].ToString();
        string query = "select * from " + className;

        ManagementObjectSearcher databaseSearcher = new ManagementObjectSearcher(
            new ManagementScope(@"root\cimv2"),
            new WqlObjectQuery(query),
            null);

        foreach (ManagementObject database in databaseSearcher.Get())
        {
            Console.WriteLine("  " + database["Name"]);
            Console.WriteLine("    Data Files     : " + database["DataFilesSizeKB"]);
            Console.WriteLine("    Log Files      : " + database["LogFilesSizeKB"]);
            Console.WriteLine("    Log Files Used : " + database["LogFilesSizeKB"]);
        }
    }
}

這篇關(guān)于我可以在不編寫 SQL 查詢的情況下找出數(shù)據(jù)庫(kù)列表和 SQL Server 實(shí)例使用的空間嗎?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！