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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 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
相关文章
|
4月前
|
C#
一个.NET开源、轻量级的运行耗时统计库 - MethodTimer
一个.NET开源、轻量级的运行耗时统计库 - MethodTimer
|
4月前
|
人工智能 自然语言处理 API
适用于 .NET 稳定的官方OpenAI库
适用于 .NET 稳定的官方OpenAI库
|
1月前
|
SQL 存储 关系型数据库
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
86 9
|
2月前
|
SQL Java 数据库连接
【潜意识Java】MyBatis中的动态SQL灵活、高效的数据库查询以及深度总结
本文详细介绍了MyBatis中的动态SQL功能,涵盖其背景、应用场景及实现方式。
160 6
|
3月前
|
SQL Oracle 数据库
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
106 11
|
3月前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
3月前
|
SQL 监控 安全
SQL Servers审核提高数据库安全性
SQL Server审核是一种追踪和审查SQL Server上所有活动的机制,旨在检测潜在威胁和漏洞,监控服务器设置的更改。审核日志记录安全问题和数据泄露的详细信息,帮助管理员追踪数据库中的特定活动,确保数据安全和合规性。SQL Server审核分为服务器级和数据库级,涵盖登录、配置变更和数据操作等事件。审核工具如EventLog Analyzer提供实时监控和即时告警,帮助快速响应安全事件。
|
4月前
|
传感器 人工智能 供应链
.NET开发技术在数字化时代的创新作用,从高效的开发环境、强大的性能表现、丰富的库和框架资源等方面揭示了其关键优势。
本文深入探讨了.NET开发技术在数字化时代的创新作用,从高效的开发环境、强大的性能表现、丰富的库和框架资源等方面揭示了其关键优势。通过企业级应用、Web应用及移动应用的创新案例,展示了.NET在各领域的广泛应用和巨大潜力。展望未来,.NET将与新兴技术深度融合,拓展跨平台开发,推动云原生应用发展,持续创新。
62 4
|
4月前
|
存储 关系型数据库 MySQL
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
查询服务器CPU、内存、磁盘、网络IO、队列、数据库占用空间等等信息
1699 2
|
4月前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
69 2

热门文章

最新文章