People love taking shortcuts because it’s the easiest way to do things faster; computers are no exception. Shortcuts, particularly ones performed by keyboard, can save you hours of time once applied properly. Learning keyboard shortcut saves you a lot of time; you’ll definitely notice a boost the productivity because you’re not unnecessarily reaching for a mouse all the time.

人们喜欢捷径，因为这是更快地做事的最简单方法。 电脑也不例外。 快捷方式（尤其是通过键盘执行的快捷方式）在正确应用后可以节省数小时的时间。 学习键盘快捷键可以节省大量时间； 您肯定会注意到生产率的提高，因为您不必一直不必要地伸手去拿鼠标。

I’ve rounded up some of the most common DBA customized keyboard shortcuts in SSMS. The same set of rules can be applied to define one which you feel is most important in your day to day activities. Stay on board and you’ll be mastering these tricks in no time!

我已经总结了SSMS中一些最常见的DBA自定义键盘快捷键。 可以应用同一组规则来定义您认为在日常活动中最重要的规则。 留在船上，您将立即掌握这些技巧！

Keyboard shortcuts are vital and important for anyone who wants to perform an effective work. It’s vital to understand the ins and outs of the IDE as it saves a lot of time. SQL server Management Studio bundled with lots of keyboard shortcuts and I’ve added a useful reference go through in reference section.

键盘快捷键对于任何想要执行有效工作的人都至关重要。 了解IDE的来龙去脉至关重要，因为它可以节省大量时间。 SQL Server Management Studio捆绑了许多键盘快捷键，我在参考部分中添加了有用的参考。

In this article, we will explore the most essential user defined and customized shortcuts which will make our daily life more easy and intuitive. The use of SQL Server Management Studio let the users define and design the customized short key to perform various day to day activities. This article outlines the tips and tricks that one can apply to perform effective work with minimal efforts

在本文中，我们将探索最基本的用户定义和自定义快捷方式，这些快捷方式将使我们的日常生活更加轻松和直观。 使用SQL Server Management Studio，用户可以定义和设计自定义的快捷键，以执行各种日常活动。 本文概述了一些技巧和窍门，人们可以用这些技巧和窍门以最少的努力执行有效的工作。

We are going to see how the custom queries can be used as query shortcuts in SSMS. There are plenty of the resource are available to discuss the default shortcuts that are bundled with SSMS IDE but the following section talks about customization and parameter passing through keyboard shortcuts under various scenarios is discussed in detail.

我们将看到如何将自定义查询用作SSMS中的查询快捷方式。 有大量资源可用来讨论与SSMS IDE捆绑在一起的默认快捷方式，但以下部分将详细讨论在各种情况下通过键盘快捷方式进行自定义和参数传递的问题。

If you are a Developer/DBA we always tend to achieve the results in simple and smarter way. This is where we are going for keyboard shortcuts. We do have an option available in SSMS to set and define the rules to use the keyboard shortcuts.

如果您是开发人员/ DBA，我们总是倾向于以简单，智能的方式获得结果。 这是我们要使用键盘快捷键的地方。 我们确实在SSMS中提供了一个选项来设置和定义使用键盘快捷键的规则。

The below the steps to create customized key board shortcuts

下面是创建自定义键盘快捷方式的步骤

Open SSMS->Go to Tools -> Select Options.

打开SSMS->转到工具->选择选项。

Go to Environment -> Keyboard -> Query Shortcuts

转到环境->键盘->查询快捷方式

On the right, in the query shortcuts pane, we can see some shortcuts which are by default in SSMS. Now, if you need to add a new one, just click on any column under Stored Procedure column. In this case we have added ctrl+3 for sp_helptext. The sp_helptext is a procedure that displays the definition of objects.

在右侧的查询快捷方式窗格中，我们可以看到SSMS中默认设置的一些快捷方式。 现在，如果需要添加一个新的，只需单击“存储过程”列下的任何列。 在这种情况下，我们为sp_helptext添加了ctrl + 3。 sp_helptext是显示对象定义的过程。

Click OK. Now go to a query window and select the stored procedure then press CTRL+3, it will show the script of the selected stored procedure aka DDL script which is shown below

单击确定。 现在转到查询窗口并选择存储过程，然后按CTRL + 3，它将显示所选存储过程的脚本，也称为DDL脚本，如下所示

Let’s consider a scenario to display the definition of non default schema objects.

让我们考虑一种显示非默认架构对象的定义的方案。

The below section talks about viewing the definitions of non default schemas, other than dbo schema object, is explained. In such cases, you need to select the object and schema within a single quote to make this work.

下一节将介绍有关查看非默认架构（dbo架构对象除外）的定义的信息。 在这种情况下，您需要在单引号内选择对象和架构以使其起作用。

The enclosing of schema.object name in a single quote is applicable to all scenarios where you tend to perform any manipulation using non default schema objects

将schema.object名称括在单引号中适用于您倾向于使用非默认模式对象执行任何操作的所有情况

键盘快捷键–使用sp_executesql将参数传递给查询 (Keyboard Shortcut – Passing Parameter to Query using sp_executesql)

Let’s prepare a SQL query which accepts input parameter through sp_executesql. The object ‘configuration_properties’ is the input to the SQL. If the input parameter schema is non-default schema then the object should enclose within in a single quote along with a schema name.

让我们准备一个SQL查询，该查询通过sp_executesql接受输入参数。 对象“ configuration_properties”是SQL的输入。 如果输入参数架构为非默认架构，则该对象应与架构名称一起用单引号引起来。

The below SQL is used to fetch rowcount and index details of the object

下面SQL用于获取对象的行数和索引详细信息

 EXEC sp_executesql N'SELECT so.name as TableName, ddps.row_count as [RowCount],si.type_desc,si.is_uniqueFROM sys.objects soJOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_IDJOIN sys.dm_db_partition_stats AS ddps ON si.OBJECT_ID = ddps.OBJECT_ID  AND si.index_id = ddps.index_idWHERE si.index_id < 2  AND so.is_ms_shipped = 0 and so.name = @objnameORDER BY ddps.row_count DESC',N'@objname nvarchar(776)',@objname = configuration_properties 

After successful execution, the SQL is ready for the customization

成功执行后，SQL已准备好进行定制

Now,

现在，

• Prepare the SQL into one single line i.e. is bring all the statement into a single line as the text parameter accepts only single line in the Query Shortcuts pane
  将SQL准备到一行中，即将所有语句放在一行中，因为text参数在“查询快捷方式”窗格中仅接受一行
• Copy the SQL text until the “=” sign
  复制SQL文本，直到“ =”符号

The actual SQL is copied under Ctrl+9. The ctrl+3 are a similar SQL. The idea is just to show you an example.

实际SQL复制在Ctrl + 9下。 ctrl + 3是类似SQL。 这个想法只是向您展示一个例子。

 EXEC sp_executesql N'SELECT * FROM sys.objects so JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID JOIN sys.dm_db_partition_stats ',@objname = 

Now, please go ahead and select the table name from the query window and press CTRL+9 (the key we selected).

现在，请继续从查询窗口中选择表名称，然后按CTRL + 9（我们选择的键）。

This section deals with getting the fragmentation details using DMF . The @objname parameter is passed as an input to DMF.

本节介绍使用DMF获取碎片详细信息。 @objname参数作为输入传递给DMF。

The simple SQL is given below

下面给出了简单SQL

 EXEC sp_executesql N'SELECT 	OBJECT_NAME(OBJECT_ID) indexname,	index_type_desc,	index_level, 	avg_fragmentation_in_percent,	avg_page_space_used_in_percent,	page_count FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@objname), NULL, NULL , ''SAMPLED'') ORDER BY avg_fragmentation_in_percent DESC',N'@objname nvarchar(1000)',@objname =configuration_properties 

Let’s group all the SQL statement into single line and copy the SQL until “=” sign and paste in a query shortcut pane in the available shortcut key.

让我们将所有SQL语句分组为一行，然后复制SQL，直到“ =”符号并粘贴到可用快捷键中的查询快捷方式窗格中。

Now, go ahead and select the table name from the query window and press CTRL+5 (the key we selected) to get the required fragmentation details

现在，继续并从查询窗口中选择表名称，然后按CTRL + 5（我们选择的键）以获取所需的碎片详细信息

The most commonly used SQL to fetch the database detail using Keyboard short in SSMS discussed below

下面讨论的SSMS中使用Keyboard short的最常用SQL来获取数据库详细信息

Prepare the multiple line SQL statements into single line using SSMS Regular Express update or any available tools such as Textpad or Notepad++ to replace carriage return “\r” with blank value will yield the result into single liner. It’s really easy to merge the SQL into a single line using text editor tools. Copy and paste the one liner SQL into the Query Shortcut pane for the available shortcut keys (ctrl+5)

使用SSMS Regular Express更新或将所有可用的工具（例如Textpad或Notepad ++）替换为空白，将回车符“ \ r ”替换为空白值，即可将多行SQL语句准备为单行。 使用文本编辑器工具将SQL合并为一行非常容易。 将一个线性SQL复制并粘贴到“查询快捷方式”窗格中，以获取可用的快捷键（ctrl + 5）

 SELECT @@SERVERNAME Servername,CONVERT(VARCHAR(25), DBS.name) AS dbName,CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DBS.name AND groupid !=0 ) AS DataFiles,(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DBS.name AND groupid!=0) AS [Data MB],(SELECT COUNT(1) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DBS.name AND groupid=0) AS LogFiles,(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DBS.name AND groupid=0) AS [Log MB],(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DBS.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sys.sysaltfiles WHERE DB_NAME(dbid) = DBS.name AND groupid=0) TotalSizeMB,convert(sysname,DatabasePropertyEx(name,'Updateability')) DBUpdateability,convert(sysname,DatabasePropertyEx(name,'UserAccess')) DBUserAccess ,convert(sysname,DatabasePropertyEx(name,'Recovery')) DBRecoveryModel ,convert(sysname,DatabasePropertyEx(name,'Version')) DBVersion ,CASE cmptlevelWHEN 60 THEN '60 (SQL Server 6.0)'WHEN 65 THEN '65 (SQL Server 6.5)'WHEN 70 THEN '70 (SQL Server 7.0)'WHEN 80 THEN '80 (SQL Server 2000)'WHEN 90 THEN '90 (SQL Server 2005)'WHEN 100 THEN '100 (SQL Server 2008)'WHEN 110 THEN '100 (SQL Server 2012)'WHEN 120 THEN '100 (SQL Server 2014)'WHEN 130 THEN '100 (SQL Server 2016)'END AS dbcompatibilitylevel,CONVERT(VARCHAR(20), crdate, 103) + ' ' + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date],ISNULL((SELECT TOP 1CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' +LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) +' (' + CAST(DATEDIFF(second, BKS.backup_start_date,BKS.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)'FROM msdb.dbo.backupset BKS WHERE BKS.database_name = DBS.name ORDER BY backup_set_id DESC),'-') AS [Last backup]FROM sys.sysdatabases DBSORDER BY dbName, [Last backup] DESC, NAME 

Now, open a new window and press CTRL+5 (You can select any key as per the definition from the Query shortcut) to get the below result

现在，打开一个新窗口，然后按CTRL + 5（您可以根据查询快捷方式的定义选择任何键）以得到以下结果

There are few more examples that you can try using Keyboard shortcuts and are given below

您可以尝试使用键盘快捷键尝试更多示例，如下所示

To Get SQL File details

获取SQL文件的详细信息

In the below example, PowerSQL is the database name and input parameter. Change the parameter as per your requirement

在下面的示例中，PowerSQL是数据库名称和输入参数。 根据您的要求更改参数

 EXEC sp_executesql N'select db_name(database_id),database_id,name,physical_name,state,state_desc,* from sys.master_files where database_id=DB_ID(@objname) ',N'@objname nvarchar(776)',@objname =PowerSQL 

Simple query to fetch database information

简单查询以获取数据库信息

 EXEC sp_executesql N'SELECT * FROM sys.databases WHERE name = @db ',N'@db nvarchar(776)',@db ='master' 

Space usage details of a table

表的空间使用详细信息

In this case the DiskVolume is the name of the table. In case of passing non-default schema object then make sure to enclose it in a single quite along with schema.object name

在这种情况下，DiskVolume是表的名称。 如果传递非默认模式对象，请确保将其与schema.object名称一起封装在一个相当的位置

 EXEC sp_executesql  N'sp_spaceused @objname',N'@objname nvarchar(776)',@objname =DiskVolume 

For non-default schema object

对于非默认架构对象

   EXEC sp_executesql  N'sp_spaceused @objname',N'@objname nvarchar(776)',@objname ='test.DiskVolume' 

Space usage details of all the databases

所有数据库的空间使用情况详细信息

The below script doesn’t need any input parameter

下面的脚本不需要任何输入参数

 master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_spaceused' 

Now, create a new query window and press CTRL+4 (the key we selected) to get the sp_spaceused details of databases

现在，创建一个新的查询窗口，然后按CTRL + 4（我们选择的键）以获取sp_spaceused数据库的详细信息

结论 (Conclusion)

Once you learn the art of remembering and using existing and building the customized shortcuts you’ll definitely notice a boost to productivity because you’ll not be unnecessarily reaching out for the same routine tasks.

一旦您学会了记住和使用现有技术以及构建自定义快捷方式的技巧，您肯定会注意到生产力的提高，因为您不必不必要地伸手去完成相同的例行任务。

翻译自: