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!
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.
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
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.
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.
The below the steps to create customized key board shortcuts
Open SSMS->Go to Tools -> Select Options.
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.
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
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.
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
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.
The below SQL is used to fetch rowcount and index details of the object
|
1 2 3 4 5 6 7 8 |
EXEC sp_executesql N'SELECT so.name as TableName, ddps.row_count as [RowCount],si.type_desc,si.is_unique FROM sys.objects so JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID JOIN sys.dm_db_partition_stats AS ddps ON si.OBJECT_ID = ddps.OBJECT_ID AND si.index_id = ddps.index_id WHERE si.index_id < 2 AND so.is_ms_shipped = 0 and so.name = @objname ORDER BY ddps.row_count DESC',N'@objname nvarchar(776)',@objname = configuration_properties |
After successful execution, the SQL is ready for the customization
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
- Copy the SQL text until the “=” sign
The actual SQL is copied under Ctrl+9. The ctrl+3 are a similar SQL. The idea is just to show you an example.
|
1 2 3 4 |
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).
This section deals with getting the fragmentation details using DMF . The @objname parameter is passed as an input to DMF.
The simple SQL is given below
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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.
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
The most commonly used SQL to fetch the database detail using Keyboard short in SSMS discussed below
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)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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 cmptlevel WHEN 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 1 CASE 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 DBS ORDER 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
There are few more examples that you can try using Keyboard shortcuts and are given below
To Get SQL File details
In the below example, PowerSQL is the database name and input parameter. Change the parameter as per your requirement
|
1 2 3 4 5 |
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
|
1 2 3 4 |
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
|
1 2 3 4 |
EXEC sp_executesql N'sp_spaceused @objname',N'@objname nvarchar(776)',@objname =DiskVolume |
For non-default schema object
|
1 2 3 4 |
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
|
1 2 3 |
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
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.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021