在.NET中根据SQL Server系统表获取数据库管理信息

whitek · 发表于 2018-10-12 10:30:18

　　最近在做一些数据库管理维护方面的开发，需要了解一些有关数据库的管理信息，比如本机上运行了哪些数据库服务器实例，局域网内运行了哪些数据库服务器实例及每个数据库服务器下有多少数据库，每个数据库的物理文件大小及保存位置等等。结合了网上的一些资料和本人的多次实践，总结写出本篇。在这里要感谢一篇文章《sql server系统表详细说明》。
　　首先是一些与系统表记录对应的实体类（注意代码中ColumnNameAttribute类是来自于《用C#打造自己的通用数据访问类库(续)》中的类，在周公处它们位于同一namespace下）：
　　(由于51cto博客篇幅限制，全部代码在本文最后提供下载）
　　对外提供访问接口的类代码如下（注意代码中DbUtility类是来自于《用C#打造自己的通用数据访问类库(续)》中的类，在周公处它们位于同一namespace下）：
　　

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using Microsoft.Win32;
namespace NetSkycn.Data
{
///
/// 对外提供数据库管理信息的类
/// 作者：周公(zhoufoxcn,转载请注明出处)
/// 创建日期：2011-12-21
/// 博客地址：http://blog.csdn.net/zhoufoxcn 或 http://zhoufoxcn.blog.51cto.com
/// 新浪微博地址：http://weibo.com/zhoufoxcn
///
public class SqlServerManager
{
private static readonly string SQL_GetSysAltFiles = "use master;select * from SysAltFiles";
private static readonly string SQL_GetSysColumns = "select * from SysColumns";
private static readonly string SQL_GetSysDatabases = "use master;select * from SysDatabases";
private static readonly string SQL_GetSysFiles = "select * from SysFiles";
private static readonly string SQL_GetSysLogins = "use master;select * from SysLogins";
private static readonly string SQL_GetSysObjects = "select * from SysObjects";
private static readonly string SQL_GetSysTypes = "select * from SysTypes";
private static readonly string SQL_GetSysUsers = "select * from SysUsers";
private DbUtility dbUtility = null;
public string ConnectionString { get; set; }
public SqlServerManager()
{
}
///
/// 创建SqlServerManager的实例
///
///
public SqlServerManager(string connectionString)
{
this.ConnectionString = connectionString;
dbUtility = new DbUtility(connectionString, DbProviderType.SqlServer);
}
///
/// 从主数据库中保存数据库的文件信息
///
///
public List GetSysAltFiles()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysAltFiles, null);
}
///
/// 从当前连接的数据库中获取所有列的信息
///
///
public List GetSysColumns()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysColumns, null);
}
///
/// 从主数据库中获取服务器中所有数据库的信息
///
///
public List GetSysDatabases()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysDatabases, null);
}
///
/// 获取当前连接的数据库的数据库物理文件信息
///
///
public List GetSysFiles()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysFiles, null);
}
///
/// 从主数据库中查询登陆帐号信息
///
///
public List GetSysLogins()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysLogins, null);
}
///
/// 获取当前连接的数据库中所有数据库对象
///
///
public List GetSysObjects()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysObjects, null);
}
///
/// 获取当前连接的数据库中用户定义数据类型
///
///
public List GetSysTypes()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysTypes, null);
}
///
/// 获取当前连接的数据中的用户信息
///
///
public List GetSysUsers()
{
dbUtility.ConnectionString = ConnectionString;
return dbUtility.QueryForList(SQL_GetSysUsers, null);
}
///
///
///
///
///
public static List GetSysAltFiles(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysAltFiles, null);
}
///
///
///
///
///
public static List GetColumns(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysColumns, null);
}
///
///
///
///
///
public static List GetSysDatabases(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysDatabases, null);
}
///
///
///
///
///
public static List GetSysFiles(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysFiles, null);
}
///
///
///
///
///
public static List GetSysLogins(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysLogins, null);
}
///
///
///
///
///
public static List GetSysObjects(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysObjects, null);
}
///
///
///
///
///
public static List GetSysTypes(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysTypes, null);
}
///
///
///
///
///
public static List GetSysUsers(string connectionString)
{
DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer);
return utility.QueryForList(SQL_GetSysUsers, null);
}
///
/// 检索局域网内（但不包括本机）包含有关所有可见 SQL Server 2000 或 SQL Server 2005 实例的信息的
///
///
public static List GetDataSources()
{
DataTable data = SqlDataSourceEnumerator.Instance.GetDataSources();
foreach (DataColumn column in data.Columns)
{
Console.WriteLine(column.ColumnName);
}
return EntityReader.GetEntities(data);
}
///
/// 获取本地及当前局域网内所有的SQL Server数据库服务器实例的名称
///
///
public static List EnumerateAllDbInstance()
{
List allInstances = EnumerateLocalDbInstance();
allInstances.AddRange(EnumerateRemoteDbInstance());
return allInstances;
}
///
///查询本机的SQL Server服务器实例
///
///
public static List EnumerateLocalDbInstance()
{
List serverInstances = new List();
RegistryKey registryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server");
string[] keyValue = (string[])registryKey.GetValue("InstalledInstances");
if (keyValue != null && (int)keyValue.Length > 0)
{
string[] strArrays = keyValue;
foreach (string instanceName in strArrays)
{
//采用默认实例名的数据库服务器，其默认实例名就是电脑名
if (string.Compare(instanceName, "MSSQLSERVER", StringComparison.InvariantCultureIgnoreCase) == 0)
{
serverInstances.Add(Environment.MachineName);
}
else//采用电脑名+实例名的数据库服务器（通常见于一台Server上安装了多个SQL Server)
{
serverInstances.Add(string.Format("{0}\\{1}", Environment.MachineName, instanceName));
}
}
}
return serverInstances;
}
///
///查询当前局域网中正在运行的SQL Server数据库服务器实例
///
///
public static List EnumerateRemoteDbInstance()
{
DataTable dataServer = SqlDataSourceEnumerator.Instance.GetDataSources();
List listServer = new List(dataServer.Rows.Count);
string serverName, instanceName;
foreach (DataRow row in dataServer.Rows)
{
serverName = row["ServerName"].ToString();
instanceName = row["InstanceName"].ToString();
if (!string.IsNullOrEmpty(instanceName))
{
listServer.Add(string.Format("{0}\\{1}", serverName, instanceName));
}
else
{
listServer.Add(serverName);
}
}
return listServer;
}
}
}

　　

　　单元测试代码如下(注意使用了NUnit作为单元测试工具，如果不会NUnit可以忽略，不影响使用)：
　　

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NetSkycn.Data;
using NUnit.Framework;
namespace netskycnNUnitTest
{
[TestFixture]
public class SqlServerManagerTest
{
private static string connectionString = "Data Source=testServer;Initial Catalog=testDB;User ID=sa;Password=test;";
private SqlServerManager manager = null;
public static void Main()
{
int i = SqlServerManager.GetDataSources().Count;
Console.WriteLine(typeof(int?));
Console.WriteLine(typeof(int?).GetGenericArguments()[0]);
Console.WriteLine(typeof(int?).BaseType);
Console.WriteLine(typeof(int?).BaseType.DeclaringType);
Console.WriteLine(typeof(int?).BaseType.BaseType);
Console.ReadLine();
}
[TestFixtureSetUp]
public void Initialize()
{
manager = new SqlServerManager(connectionString);
}
[Test]
public void InstanceGetSysAltFiles()
{
Assert.Greater(manager.GetSysAltFiles().Count, 0);
}
[Test]
public void InstanceGetSysColumns()
{
Assert.Greater(manager.GetSysColumns().Count, 0);
}
[Test]
public void InstanceGetSysDatabases()
{
Assert.Greater(manager.GetSysDatabases().Count, 0);
}
[Test]
public void InstanceGetSysFiles()
{
Assert.Greater(manager.GetSysFiles().Count, 0);
}
[Test]
public void InstanceGetSysLogins()
{
Assert.Greater(manager.GetSysLogins().Count, 0);
}
[Test]
public void InstanceGetSysObjects()
{
Assert.Greater(manager.GetSysObjects().Count, 0);
}
[Test]
public void InstanceGetSysTypes()
{
Assert.Greater(manager.GetSysTypes().Count, 0);
}
[Test]
public void InstanceGetSysUsers()
{
Assert.Greater(manager.GetSysUsers().Count, 0);
}
[Test]
public void StaticGetSysAltFiles()
{
Assert.Greater(SqlServerManager.GetSysAltFiles(connectionString).Count, 0);
}
[Test]
public void StaticGetColumns()
{
Assert.Greater(SqlServerManager.GetColumns(connectionString).Count, 0);
}
[Test]
public void StaticGetSysDatabases()
{
Assert.Greater(SqlServerManager.GetSysDatabases(connectionString).Count, 0);
}
[Test]
public void StaticGetSysFiles()
{
Assert.Greater(SqlServerManager.GetSysFiles(connectionString).Count, 0);
}
[Test]
public void StaticGetSysLogins()
{
Assert.Greater(SqlServerManager.GetSysLogins(connectionString).Count, 0);
}
[Test]
public void StaticGetSysObjects()
{
Assert.Greater(SqlServerManager.GetSysObjects(connectionString).Count, 0);
}
[Test]
public void StaticGetSysTypes()
{
Assert.Greater(SqlServerManager.GetSysTypes(connectionString).Count, 0);
}
[Test]
public void StaticGetSysUsers()
{
Assert.Greater(SqlServerManager.GetSysUsers(connectionString).Count, 0);
}
}
}

　　

　　单元测试结果：

　　证明测试通过。限于篇幅，其中某些方法可以提供更多参数的展开，但这不是本篇的重点。
　　
　　周公
　　2012-04-19
　　武汉
　　

账号		自动登录	找回密码
密码			立即注册

wirelessnetview好用的无线分析工具

Red Hat RHCE 8 (EX294) Cert Guide

Shell从入门到精通（阿良）

亿图图示专家(EDraw Max) V7.9 中文破解版

zabbix3.4.1安装部署+微信推送信息+大屏显

Red Hat OpenShift I: Containers & Kubern

2025 年，C++ 还能“硬核”多久？

[经验分享] 在.NET中根据SQL Server系统表获取数据库管理信息

浏览过的版块

扫码加入运维网微信交流群