使用系统视图发现SQL Server实例信息

介绍 (Introduction)

Out of the box, SQL Server comes with a substantial and – release by release – ever-growing set of system tables, views, stored procedures and functions. There’s a good chance you’ve never directly used more than a handful of them. That’s certainly the case with me!

SQL Server开箱即用，带有大量且逐个发布的，不断增长的系统表，视图，存储过程和函数集。 您很有可能从未直接使用过少数几个。 我当然是这样！

This is the first article in a series designed to explore this world that lives just below the surface of our everyday interactions with SQL Server through the same objects we create to enable the applications we write and support.

这是旨在探索这个世界的系列的第一篇文章，该世界生活在我们与SQL Server日常交互的表面之下，通过创建与我们创建的相同对象相同的对象来支持我们编写和支持的应用程序。

我在哪里？ (Where in the world am I?)

Supposed you are starting your first day as a new DBA. You’ve been given a laptop to work with that has a current copy of SSMS installed on it. You’ve also been given a server name to connect to and told you have the necessary permissions to work with it. That’s it! So, you connect and begin looking at what you have. However, you’d like to know as much as possible. For example, what version of SQL Server is running? That one’s easy. You just run this query:

假设您以新的DBA开始新的一天。 您已获得一台可与之配合使用的便携式计算机，该便携式计算机上已安装了SSMS的当前副本。 还为您提供了要连接的服务器名称，并告诉您具有使用它的必要权限。 而已！ 因此，您可以连接并开始查看拥有的东西。 但是，您想了解更多。 例如，正在运行哪个版本SQL Server？ 那很容易。 您只需运行以下查询：

SELECT @@VERSION;

You’re rewarded with the following output:

您将获得以下输出：

Great! At least you now know something about the instance you’re connected to. You also have the service pack (SP1) and platform (X64) and youthinkit might be running on Windows Server R2, at least, according to Wikipedia!

大！ 至少您现在对所连接的实例有所了解。 根据Wikipedia的介绍，您还具有Service Pack（SP1）和平台（X64），并且您认为它至少可以在Windows Server R2上运行！

As it turns out, I ran that query on the laptop I’m using to write this article, and it isnotrunning Windows Server. It’s actually running Windows 10! Can I coax that information out of SQL Server somehow?

事实证明，我在写这篇文章的笔记本电脑上运行了该查询，并且它没有运行Windows Server。 它实际上正在运行Windows 10！ 我可以以某种方式从SQL Server哄骗该信息吗？

To find out, I’ll use the SQL Server Dynamic Management View

为了找出答案，我将使用“ SQL Server动态管理”视图

sys.dm_os_windows_info

I simply run:

我只是运行：

SELECT * FROM sys.dm_os_windows_info;

and get this output:

并获得以下输出：

Well, OK, but how do I map the windows_release=6.3 and windows_sku=48 to human-readable information? The references section holds a link to an article that holds just such a mapping. The key is the SKU – the Stock Keeping Unit. Since the article shows the SKUs in hexadecimal, and I know that 48 = 0X30, I can scan down the table and see this row:

好吧，但是，如何将windows_release = 6.3和windows_sku = 48映射到人类可读的信息？ 参考资料部分包含指向包含此类映射的文章的链接。 关键是SKU –库存单元。 由于本文以十六进制显示了SKU，并且我知道48 = 0X30，因此我可以向下扫描表格并看到以下行：

So, now I know I’m running Windows 10 Pro. If I actuallywasrunning on Windows Server R2, I would have seen a SKU of 8.

所以，现在我知道我正在运行Windows 10 Pro。 如果我实际上是在Windows Server 中运行R2，我就已经看到了8 SKU。

我怎么到这里了？ (How did I get here?)

Now that I know I’m running SQL Server on Windows 10 Pro, the next thing I want to know is how the instance was started. Where is the executable that was launched? What options were used? Note that there are many arguments to sqlerver.exe that can affect how the database engine operates. The full list can be found in the references, but some notables are -s for the instance name -T for trace flags and -g for memory to reserve. To see these, you can use the DMV

现在，我知道我正在Windows 10 Pro上运行SQL Server ，接下来我想知道的是实例是如何启动的。 启动的可执行文件在哪里？ 使用了哪些选项？ 请注意，sqlerver.exe有许多参数会影响数据库引擎的运行方式。 完整列表可在参考文献中找到，但值得注意的是-s表示实例名称-T表示跟踪标志，-g表示要保留的内存。 要查看这些，可以使用DMV

sys.dm_server_registry

Running this on my system:

在我的系统上运行：

SELECT * FROM sys.dm_server_registry WHERE registry_key LIKE '%contr%';

yielded (first two rows):

产生（前两行）：

So, I know that the instance name is MSSQLSERVER (the usual default name) and that there are no other arguments. Great! That’s a simple setup. Note that the first two columns are the registry key and value name. You can get the same information from the registry using those as locators, assuming you have the right permissions on the operating system itself.

因此，我知道实例名称是MSSQLSERVER（通常的默认名称），并且没有其他参数。 大！ 这是一个简单的设置。 请注意，前两列是注册表项和值名称。 假设您对操作系统本身具有正确的权限，则可以将这些信息用作定位器从注册表中获取相同的信息。

The next think you might want to know is how long SQL Server has been up or when it was last started to put it another way. There’s more than one way to do that. Here are three of them:

您可能想知道的下一个想法是，SQL Server已经运行了多长时间，或者它从什么时候开始启动，换句话说。 有多种方法可以做到这一点。 这是其中的三个：

SELECT last_startup_time FROM sys.dm_server_services WHERE servicename = 'SQL Server (MSSQLSERVER)';SELECT sqlserver_start_time FROM sys.dm_os_sys_info;SELECT login_time FROM sysprocesses WHERE spid = 1;

你还能告诉我什么？ (What else can you tell me?)

Each of these views yields interesting information besides the last start time. For example:

这些视图中的每一个都除了最后一次启动时间之外还产生有趣的信息。 例如：

SELECT * FROM sys.dm_server_services;

Returns 3 rows and 11 columns on my system, starting with:

从我的系统开始，返回3行11列：

Column 9 is named “filename” and italsoshows the executable launched, with any options:

第9列被命名为“文件名”，它也显示推出，与任何选项可执行文件：

Note that I can also see the service account the process is running under.

请注意，我还可以看到正在运行该进程的服务帐户。

SELECT * FROM sys.dm_os_sys_info;

Gives lots of information about the running environment. Here is just a little of what I get back:

提供有关运行环境的大量信息。 这只是我得到的一小部分：

OK, OK! I’ve just revealed that I only have 8 MB installed on my laptop. Amazing what you can do with just a little, though!

好的好的！ 我刚刚透露我的笔记本电脑上只安装了8 MB。 不过，您仅需一点点就可以完成惊人的工作！

sysprocesses is central to the operation of the server. We’ll be using it again and again in this series. For now, keeping in mind that system processes have spids 1-50, let’s see what I can find out about anythingabove50:

sysprocesses是服务器操作的中心。 在本系列中，我们将一次又一次使用它。 现在，请记住系统进程的spids为1-50，让我们看看我能找到50以上的信息：

SELECT hostname, loginame, cmd FROM sys.sysprocesses WHERE spid > 50;

As I write this, I get:

当我写这篇文章时，我得到：

Which tells me that I have three sessions going (true!) and one of them is running a SELECT. In fact, it’s the query used to produce this output.

这告诉我我要进行三个会话（true！），其中一个正在运行SELECT。 实际上，它是用于产生此输出的查询。

告诉我更多！ (Tell me more!)

sp_server_info is a system stored procedure that can tell you more about how the server was configured. For example, on my test system I can see that I allow mixed-case identifies by that my default collation is case-insensitive and accent sensitive (this can be overridden at the database and column levels):

sp_server_info是一个系统存储过程，可以告诉您更多有关服务器配置的信息。 例如，在我的测试系统上，我可以看到我的默认排序规则不区分大小写和重音（我可以在数据库和列级别将其覆盖），从而允许混合大小写识别：

While we are talking about configuration, we must dig into the system stored procedure sp_configure. It tells us current settings and can also be used to change them. When executed with no parameters, it returns a list of current settings:

在谈论配置时，我们必须深入研究系统存储过程sp_configure。 它告诉我们当前的设置，也可以用来更改它们。 不带参数执行时，它将返回当前设置的列表：

EXEC sp_configure;

Returns:

返回值：

But wait, there’s more! If I now run:

但是，等等，还有更多！ 如果我现在运行：

EXEC sp_configure 'show advanced option', '1';RECONFIGURE;EXEC sp_configure;

I now have 70 rows returned. I won’t dig into all of them here, but want to highlight the last line:

我现在有70行返回。 我不会在这里深入探讨所有这些，但是要强调最后一行：

This option is used to control whether or not the named system extended stored procedure can be executed. This particular one is quite powerful since, if enabled, it allows a T-SQL query to execute arbitrary operating system commands in a command shell launched from with SQL Server. Many shops keep this one disabled (it is also disabled in my case, as indicated by the 0 in the fourth column, config_value). Some dispute whether disabling this option really provides any protection. See the article by Jen McCown in the references and draw your own conclusions.

此选项用于控制是否可以执行指定的系统扩展存储过程。 这一特定功能非常强大，因为如果启用该功能，它将允许T-SQL查询在从SQL Server启动的命令外壳中执行任意操作系统命令。 许多商店都禁用此功能（在我的情况下也禁用此功能，如第四列config_value中的0所示）。 有人质疑禁用此选项是否真的可以提供任何保护。 请参阅参考文献中Jen McCown的文章，并得出自己的结论。

You can find out more about the options and current settings using the system view

您可以使用系统视图找到有关选项和当前设置的更多信息

sys.configurations

The output includes descriptions of each option, for example:

输出包括每个选项的描述，例如：

SELECT name, description FROM sys.configurations WHERE name = 'xp_cmdshell';

Returns:

返回值：

还有谁呢？ (Who else is here?)

Since you’re the new kid on the block, it’s a good idea to find out who else might be using the server. You’ll likely want to get to know those people! The sys.syslogins view will help you here (also, see sys.server_principals, below):

由于您是新手，因此最好找出谁还会使用该服务器。 您可能想认识那些人！ sys.syslogins视图将在这里为您提供帮助（另请参见下面的sys.server_principals）：

SELECT * FROM sys.syslogins;

This old view, though now deprecated, is useful for viewing logins and permissions at a glance. Even on a brand-new instance, you’ll see lots of logins used by the system. The basic permissions are all there too, as bit columns:

这个旧视图（尽管现在已弃用）对于一眼查看登录名和权限很有用。 即使在全新实例上，您也会看到系统使用的许多登录信息。 基本权限也都存在，如位列所示：

So, you can see at a glance who is there and what they can do at the instance level. Instname, instgroup and instuser indicate if the login is a Widows user or group, a Windows group and a Windows user respectively. A setting of 0 indicates a SQL Server login.

因此，您可以一目了然地看到谁在那里，以及他们在实例级别可以做什么。 Instname，instgroup和instuser分别指示登录名是Widows用户还是组，Windows组和Windows用户。 设置为0表示SQL Server登录。

You will also find a related list of ids in the sys.server_principals view. You can query it based on the type column if you like, and restrict the output to:

您还将在sys.server_principals视图中找到相关的ID列表。 如果愿意，可以根据类型列查询它，并将输出限制为：

You might want to know which id is assigned to which role, especially for your new colleagues. This query should do the trick:

您可能想知道将哪个ID分配给哪个角色，尤其是对于新同事。 这个查询应该可以解决这个问题：

SELECT role.name AS RoleName, member.name AS MemberNameFROM sys.server_role_members rmJOIN sys.server_principals AS roleON rm.role_principal_id = role.principal_idJOIN sys.server_principals AS memberON rm.member_principal_id = member.principal_idWHERE role.type = 'R'ORDER BY RoleName, MemberName;

Armed with this output, you’ll know who to ask when something changes that falls with the responsibility of the various server roles

有了此输出，您将知道在各个服务器角色负责的情况下，什么时候发生变化要问谁

摘要 (Summary)

Using a few of the many system views and stored procedures available in SQL Server, you can get a handle on the setup of a server that is new to you. Try these out on any server to which you currently have access. The results will interest you and may even surprise you!

使用SQL Server中可用的许多系统视图和存储过程中的一些，您可以了解对您来说是新服务器的设置。 在您当前有权访问的任何服务器上尝试这些。 结果会让您感兴趣，甚至可能会让您感到惊讶！

Next articles in this series:

本系列的下一篇文章：

Discovering SQL server instance information using system views使用系统视图发现SQL Server实例信息 Discovering database specific information using built-in functions and dynamic management views (DMVs)使用内置功能和动态管理视图（DMV）发现特定于数据库的信息 How to track SQL Server database space usage with built-in functions and DMVs如何使用内置函数和DMV跟踪SQL Server数据库空间使用情况

翻译自: /discovering-sql-server-instance-information-using-system-views/

如果觉得《使用系统视图发现SQL Server实例信息》对你有帮助，请点赞、收藏，并留下你的观点哦！