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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介:

 最近在做一些数据库管理维护方面的开发,需要了解一些有关数据库的管理信息,比如本机上运行了哪些数据库服务器实例,局域网内运行了哪些数据库服务器实例及每个数据库服务器下有多少数据库,每个数据库的物理文件大小及保存位置等等。结合了网上的一些资料和本人的多次实践,总结写出本篇。在这里要感谢一篇文章《sql server系统表详细说明

首先是一些与系统表记录对应的实体类(注意代码中ColumnNameAttribute类是来自于《C#打造自己的通用数据访问类库()》中的类,在周公处它们位于同一namespace下):

(由于51cto博客篇幅限制,全部代码在本文最后提供下载)

 

对外提供访问接口的类代码如下(注意代码中DbUtility类是来自于《C#打造自己的通用数据访问类库()》中的类,在周公处它们位于同一namespace下):

 
  1. using System; 
  2. using System.Collections.Generic; 
  3. using System.Text; 
  4. using System.Data; 
  5. using System.Data.Sql; 
  6. using Microsoft.Win32; 
  7.  
  8. namespace NetSkycn.Data 
  9.     /// <summary> 
  10.     /// 对外提供数据库管理信息的类 
  11.     /// 作者:周公(zhoufoxcn,转载请注明出处) 
  12.     /// 创建日期:2011-12-21 
  13.     /// 博客地址:http://blog.csdn.net/zhoufoxcn 或 http://zhoufoxcn.blog.51cto.com 
  14.     /// 新浪微博地址:http://weibo.com/zhoufoxcn 
  15.     /// </summary> 
  16.     public class SqlServerManager 
  17.     { 
  18.         private static readonly string SQL_GetSysAltFiles = "use master;select * from SysAltFiles"
  19.         private static readonly string SQL_GetSysColumns = "select * from SysColumns"
  20.         private static readonly string SQL_GetSysDatabases = "use master;select * from SysDatabases"
  21.         private static readonly string SQL_GetSysFiles = "select * from SysFiles"
  22.         private static readonly string SQL_GetSysLogins = "use master;select * from SysLogins"
  23.         private static readonly string SQL_GetSysObjects = "select * from SysObjects"
  24.         private static readonly string SQL_GetSysTypes = "select * from SysTypes"
  25.         private static readonly string SQL_GetSysUsers = "select * from SysUsers"
  26.  
  27.         private DbUtility dbUtility = null
  28.         public string ConnectionString { getset; } 
  29.         public SqlServerManager() 
  30.         { 
  31.  
  32.         } 
  33.         /// <summary> 
  34.         /// 创建SqlServerManager的实例 
  35.         /// </summary> 
  36.         /// <param name="connectionString"></param> 
  37.         public SqlServerManager(string connectionString) 
  38.         { 
  39.             this.ConnectionString = connectionString; 
  40.             dbUtility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  41.         } 
  42.         /// <summary> 
  43.         /// 从主数据库中保存数据库的文件信息 
  44.         /// </summary> 
  45.         /// <returns></returns> 
  46.         public List<SysAltFile> GetSysAltFiles() 
  47.         { 
  48.             dbUtility.ConnectionString = ConnectionString; 
  49.             return dbUtility.QueryForList<SysAltFile>(SQL_GetSysAltFiles, null); 
  50.         } 
  51.         /// <summary> 
  52.         /// 从当前连接的数据库中获取所有列的信息 
  53.         /// </summary> 
  54.         /// <returns></returns> 
  55.         public List<SysColumn> GetSysColumns() 
  56.         { 
  57.             dbUtility.ConnectionString = ConnectionString; 
  58.             return dbUtility.QueryForList<SysColumn>(SQL_GetSysColumns, null); 
  59.         } 
  60.         /// <summary> 
  61.         /// 从主数据库中获取服务器中所有数据库的信息 
  62.         /// </summary> 
  63.         /// <returns></returns> 
  64.         public List<SysDatabase> GetSysDatabases() 
  65.         { 
  66.             dbUtility.ConnectionString = ConnectionString; 
  67.             return dbUtility.QueryForList<SysDatabase>(SQL_GetSysDatabases, null); 
  68.         } 
  69.         /// <summary> 
  70.         /// 获取当前连接的数据库的数据库物理文件信息 
  71.         /// </summary> 
  72.         /// <returns></returns> 
  73.         public List<SysFile> GetSysFiles() 
  74.         { 
  75.             dbUtility.ConnectionString = ConnectionString; 
  76.             return dbUtility.QueryForList<SysFile>(SQL_GetSysFiles, null); 
  77.         } 
  78.         /// <summary> 
  79.         /// 从主数据库中查询登陆帐号信息 
  80.         /// </summary> 
  81.         /// <returns></returns> 
  82.         public List<SysLogin> GetSysLogins() 
  83.         { 
  84.             dbUtility.ConnectionString = ConnectionString; 
  85.             return dbUtility.QueryForList<SysLogin>(SQL_GetSysLogins, null); 
  86.         } 
  87.         /// <summary> 
  88.         /// 获取当前连接的数据库中所有数据库对象 
  89.         /// </summary> 
  90.         /// <returns></returns> 
  91.         public List<SysObject> GetSysObjects() 
  92.         { 
  93.             dbUtility.ConnectionString = ConnectionString; 
  94.             return dbUtility.QueryForList<SysObject>(SQL_GetSysObjects, null); 
  95.         } 
  96.         /// <summary> 
  97.         /// 获取当前连接的数据库中用户定义数据类型 
  98.         /// </summary> 
  99.         /// <returns></returns> 
  100.         public List<SysType> GetSysTypes() 
  101.         { 
  102.             dbUtility.ConnectionString = ConnectionString; 
  103.             return dbUtility.QueryForList<SysType>(SQL_GetSysTypes, null); 
  104.         } 
  105.         /// <summary> 
  106.         /// 获取当前连接的数据中的用户信息 
  107.         /// </summary> 
  108.         /// <returns></returns> 
  109.         public List<SysUser> GetSysUsers() 
  110.         { 
  111.             dbUtility.ConnectionString = ConnectionString; 
  112.             return dbUtility.QueryForList<SysUser>(SQL_GetSysUsers, null); 
  113.         } 
  114.         /// <summary> 
  115.         ///  
  116.         /// </summary> 
  117.         /// <param name="connectionString"></param> 
  118.         /// <returns></returns> 
  119.         public static List<SysAltFile> GetSysAltFiles(string connectionString) 
  120.         { 
  121.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  122.             return utility.QueryForList<SysAltFile>(SQL_GetSysAltFiles, null); 
  123.         } 
  124.         /// <summary> 
  125.         ///  
  126.         /// </summary> 
  127.         /// <param name="connectionString"></param> 
  128.         /// <returns></returns> 
  129.         public static List<SysColumn> GetColumns(string connectionString) 
  130.         { 
  131.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  132.             return utility.QueryForList<SysColumn>(SQL_GetSysColumns, null); 
  133.         } 
  134.         /// <summary> 
  135.         ///  
  136.         /// </summary> 
  137.         /// <param name="connectionString"></param> 
  138.         /// <returns></returns> 
  139.         public static List<SysDatabase> GetSysDatabases(string connectionString) 
  140.         { 
  141.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  142.             return utility.QueryForList<SysDatabase>(SQL_GetSysDatabases, null); 
  143.         } 
  144.         /// <summary> 
  145.         ///  
  146.         /// </summary> 
  147.         /// <param name="connectionString"></param> 
  148.         /// <returns></returns> 
  149.         public static List<SysFile> GetSysFiles(string connectionString) 
  150.         { 
  151.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  152.             return utility.QueryForList<SysFile>(SQL_GetSysFiles, null); 
  153.         } 
  154.         /// <summary> 
  155.         ///  
  156.         /// </summary> 
  157.         /// <param name="connectionString"></param> 
  158.         /// <returns></returns> 
  159.         public static List<SysLogin> GetSysLogins(string connectionString) 
  160.         { 
  161.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  162.             return utility.QueryForList<SysLogin>(SQL_GetSysLogins, null); 
  163.         } 
  164.         /// <summary> 
  165.         ///  
  166.         /// </summary> 
  167.         /// <param name="connectionString"></param> 
  168.         /// <returns></returns> 
  169.         public static List<SysObject> GetSysObjects(string connectionString) 
  170.         { 
  171.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  172.             return utility.QueryForList<SysObject>(SQL_GetSysObjects, null); 
  173.         } 
  174.         /// <summary> 
  175.         ///  
  176.         /// </summary> 
  177.         /// <param name="connectionString"></param> 
  178.         /// <returns></returns> 
  179.         public static List<SysType> GetSysTypes(string connectionString) 
  180.         { 
  181.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  182.             return utility.QueryForList<SysType>(SQL_GetSysTypes, null); 
  183.         } 
  184.         /// <summary> 
  185.         ///  
  186.         /// </summary> 
  187.         /// <param name="connectionString"></param> 
  188.         /// <returns></returns> 
  189.         public static List<SysUser> GetSysUsers(string connectionString) 
  190.         { 
  191.             DbUtility utility = new DbUtility(connectionString, DbProviderType.SqlServer); 
  192.             return utility.QueryForList<SysUser>(SQL_GetSysUsers, null); 
  193.         } 
  194.          
  195.         /// <summary> 
  196.         /// 检索局域网内(但不包括本机)包含有关所有可见 SQL Server 2000 或 SQL Server 2005 实例的信息的 
  197.         /// </summary> 
  198.         /// <returns></returns> 
  199.         public static List<SqlServerDataSource> GetDataSources() 
  200.         { 
  201.             DataTable data = SqlDataSourceEnumerator.Instance.GetDataSources(); 
  202.             foreach (DataColumn column in data.Columns) 
  203.             { 
  204.                 Console.WriteLine(column.ColumnName); 
  205.             } 
  206.             return EntityReader.GetEntities<SqlServerDataSource>(data); 
  207.         } 
  208.         /// <summary> 
  209.         /// 获取本地及当前局域网内所有的SQL Server数据库服务器实例的名称 
  210.         /// </summary> 
  211.         /// <returns></returns> 
  212.         public static List<string> EnumerateAllDbInstance() 
  213.         { 
  214.             List<string> allInstances = EnumerateLocalDbInstance(); 
  215.             allInstances.AddRange(EnumerateRemoteDbInstance()); 
  216.             return allInstances; 
  217.         } 
  218.         /// <summary> 
  219.         ///查询本机的SQL Server服务器实例 
  220.         /// </summary> 
  221.         /// <returns></returns> 
  222.         public static List<string> EnumerateLocalDbInstance() 
  223.         { 
  224.             List<string> serverInstances = new List<string>(); 
  225.             RegistryKey registryKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\Microsoft SQL Server"); 
  226.             string[] keyValue = (string[])registryKey.GetValue("InstalledInstances"); 
  227.             if (keyValue != null && (int)keyValue.Length > 0) 
  228.             { 
  229.                 string[] strArrays = keyValue; 
  230.                 foreach (string instanceName in strArrays) 
  231.                 { 
  232.                     //采用默认实例名的数据库服务器,其默认实例名就是电脑名 
  233.                     if (string.Compare(instanceName, "MSSQLSERVER", StringComparison.InvariantCultureIgnoreCase) == 0) 
  234.                     { 
  235.                         serverInstances.Add(Environment.MachineName); 
  236.                     } 
  237.                     else//采用电脑名+实例名的数据库服务器(通常见于一台Server上安装了多个SQL Server) 
  238.                     { 
  239.                         serverInstances.Add(string.Format("{0}\\{1}", Environment.MachineName, instanceName)); 
  240.                     } 
  241.                 } 
  242.             } 
  243.             return serverInstances; 
  244.         } 
  245.  
  246.         /// <summary> 
  247.         ///查询当前局域网中正在运行的SQL Server数据库服务器实例 
  248.         /// </summary> 
  249.         /// <returns></returns> 
  250.         public static List<string> EnumerateRemoteDbInstance() 
  251.         { 
  252.             DataTable dataServer = SqlDataSourceEnumerator.Instance.GetDataSources(); 
  253.             List<string> listServer = new List<string>(dataServer.Rows.Count); 
  254.             string serverName, instanceName; 
  255.             foreach (DataRow row in dataServer.Rows) 
  256.             { 
  257.                 serverName = row["ServerName"].ToString(); 
  258.                 instanceName = row["InstanceName"].ToString(); 
  259.                 if (!string.IsNullOrEmpty(instanceName)) 
  260.                 { 
  261.                     listServer.Add(string.Format("{0}\\{1}", serverName, instanceName)); 
  262.                 } 
  263.                 else 
  264.                 { 
  265.                     listServer.Add(serverName); 
  266.                 } 
  267.  
  268.             } 
  269.             return listServer; 
  270.  
  271.         } 
  272.     } 

 

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

 

 

 
  1. using System; 
  2. using System.Collections.Generic; 
  3. using System.Linq; 
  4. using System.Text; 
  5. using NetSkycn.Data; 
  6. using NUnit.Framework; 
  7.  
  8. namespace netskycnNUnitTest 
  9.     [TestFixture] 
  10.     public class SqlServerManagerTest 
  11.     { 
  12.         private static string connectionString = "Data Source=testServer;Initial Catalog=testDB;User ID=sa;Password=test;"
  13.         private SqlServerManager manager = null
  14.         public static void Main() 
  15.         { 
  16.             int i = SqlServerManager.GetDataSources().Count; 
  17.             Console.WriteLine(typeof(int?)); 
  18.             Console.WriteLine(typeof(int?).GetGenericArguments()[0]); 
  19.             Console.WriteLine(typeof(int?).BaseType); 
  20.             Console.WriteLine(typeof(int?).BaseType.DeclaringType); 
  21.             Console.WriteLine(typeof(int?).BaseType.BaseType); 
  22.             Console.ReadLine(); 
  23.         } 
  24.  
  25.         [TestFixtureSetUp] 
  26.         public void Initialize() 
  27.         { 
  28.             manager = new SqlServerManager(connectionString); 
  29.         } 
  30.  
  31.         [Test] 
  32.         public void InstanceGetSysAltFiles() 
  33.         { 
  34.             Assert.Greater(manager.GetSysAltFiles().Count, 0); 
  35.         } 
  36.         [Test] 
  37.         public void InstanceGetSysColumns() 
  38.         { 
  39.             Assert.Greater(manager.GetSysColumns().Count, 0); 
  40.         } 
  41.         [Test] 
  42.         public void InstanceGetSysDatabases() 
  43.         { 
  44.             Assert.Greater(manager.GetSysDatabases().Count, 0); 
  45.         } 
  46.         [Test] 
  47.         public void InstanceGetSysFiles() 
  48.         { 
  49.             Assert.Greater(manager.GetSysFiles().Count, 0); 
  50.         } 
  51.         [Test] 
  52.         public void InstanceGetSysLogins() 
  53.         { 
  54.             Assert.Greater(manager.GetSysLogins().Count, 0); 
  55.         } 
  56.         [Test] 
  57.         public void InstanceGetSysObjects() 
  58.         { 
  59.             Assert.Greater(manager.GetSysObjects().Count, 0); 
  60.         } 
  61.         [Test] 
  62.         public void InstanceGetSysTypes() 
  63.         { 
  64.             Assert.Greater(manager.GetSysTypes().Count, 0); 
  65.         } 
  66.         [Test] 
  67.         public void InstanceGetSysUsers() 
  68.         { 
  69.             Assert.Greater(manager.GetSysUsers().Count, 0); 
  70.         } 
  71.         [Test] 
  72.         public void StaticGetSysAltFiles() 
  73.         { 
  74.             Assert.Greater(SqlServerManager.GetSysAltFiles(connectionString).Count, 0); 
  75.         } 
  76.         [Test] 
  77.         public void StaticGetColumns() 
  78.         { 
  79.             Assert.Greater(SqlServerManager.GetColumns(connectionString).Count, 0); 
  80.         } 
  81.         [Test] 
  82.         public void StaticGetSysDatabases() 
  83.         { 
  84.             Assert.Greater(SqlServerManager.GetSysDatabases(connectionString).Count, 0); 
  85.         } 
  86.         [Test] 
  87.         public void StaticGetSysFiles() 
  88.         { 
  89.             Assert.Greater(SqlServerManager.GetSysFiles(connectionString).Count, 0); 
  90.         } 
  91.         [Test] 
  92.         public void StaticGetSysLogins() 
  93.         { 
  94.             Assert.Greater(SqlServerManager.GetSysLogins(connectionString).Count, 0); 
  95.         } 
  96.         [Test] 
  97.         public void StaticGetSysObjects() 
  98.         { 
  99.             Assert.Greater(SqlServerManager.GetSysObjects(connectionString).Count, 0); 
  100.         } 
  101.         [Test] 
  102.         public void StaticGetSysTypes() 
  103.         { 
  104.             Assert.Greater(SqlServerManager.GetSysTypes(connectionString).Count, 0); 
  105.         } 
  106.         [Test] 
  107.         public void StaticGetSysUsers() 
  108.         { 
  109.             Assert.Greater(SqlServerManager.GetSysUsers(connectionString).Count, 0); 
  110.         } 
  111.     } 

单元测试结果:

证明测试通过。限于篇幅,其中某些方法可以提供更多参数的展开,但这不是本篇的重点。















本文转自周金桥51CTO博客,原文链接:http://blog.51cto.com/zhoufoxcn/838158 ,如需转载请自行联系原作者

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
相关文章
|
1天前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
17 1
|
1天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之当 SQL Server 源数据库中的数据更新后,CDC 吐出的操作(op)是怎样的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
11 0
|
3天前
|
存储 数据库
sqlserver------数据库的存储过程(练习)
sqlserver------数据库的存储过程(练习)
8 1
|
3天前
|
数据库
sqlserver数据库学习感悟(1)----关于group by
sqlserver数据库学习感悟(1)----关于group by
7 0
|
3天前
|
SQL Oracle 关系型数据库
数据库sqlserver-----触发器的插入,更新和删除
数据库sqlserver-----触发器的插入,更新和删除
12 3
|
3天前
|
SQL XML Linux
SQL Server的版本
【5月更文挑战第14天】SQL Server的版本
18 3
|
3天前
|
SQL 关系型数据库 数据管理
Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
【5月更文挑战第14天】Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
16 2
|
3天前
|
SQL 存储 数据库连接
LabVIEW与SQL Server 2919 Express通讯
LabVIEW与SQL Server 2919 Express通讯
|
3天前
|
SQL Windows
安装SQL Server 2005时出现对性能监视器计数器注册表值执行系统配置检查失败的解决办法...
安装SQL Server 2005时出现对性能监视器计数器注册表值执行系统配置检查失败的解决办法...
14 4
|
3天前
|
SQL 数据可视化 Oracle
这篇文章教会你:从 SQL Server 移植到 DM(上)
这篇文章教会你:从 SQL Server 移植到 DM(上)