Monthly Archives: September 2011
Typing the Backtick key on non-US Keyboards
You may be surprised to know that not all keyboard layouts include the backtick key, and if you happen to live in a country with such a layout and want to do some PowerShell coding, you’re in big trouble.
For many years all major programming languages took this layout mismatch into consideration and avoided the use of US-only keys in the language definition. Now, with PowerShell, serious issues arise for those that want to wrap their code on multiple lines and reach for the backtick key, staring hopelessly at an Italian keyboard.
See? No backtick.
The only way to type a key not present in your keyboard layout is using the numeric pad with the ALT key, so that, for instance, backtick becomes ALT+Numpad9+Numpad6.
This method is painful enough itself, but quickly becomes a nightmare when working on a laptop, where probably there’s no hardware numeric pad. This way, backtick becomes NumLock+ALT+Numpad9+Numpad6+NumLock.
OMG! 5 keys instead of 1! No way: we need to find a workaround!
If I can’t type the backtick directly, I can always build a small application that types that key for me. Even better, I could unleash my google-fu and find a ready-made one, such as Independent SendKeys.
This small application is able to send keystrokes to any application running on Windows, found by window title. When a blank window title is specified, the SendKeys interacts with the current active window and can send the backtick keystroke. When invoked with no arguments, it displays a help window, which allowed me to come out with this syntax:
sendkeys.exe 0 2 “” “`”
Now I just need to associate this command with one of the keys on my keyboard.
Some fancy keyboards come with special keys to open the web browser or the e-mail client, such as this one:
I have always found those keys nearly useless and I would happily barter one for the backtick key. The good news is that it can be done, even without one of those special keys on the keyboard
To change the behaviour of one of those keys, you just have to open the registry and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\AppKey. Typically, you should find 4 or 5 subkeys, that identify what Windows will do when one of those keys gets pressed.
In this case, I chose to replace the e-mail key with backtick, using the SendKeys application. It’s very easy: you just have to rename the “Association” REG_SZ into “_Association” (you can leave it there in case you decide to restore the original behaviour) and add a new string value ShellExecute = 0 2 “” “`”
With this registry hack in place, whenever you press the e-mail key, SendKeys types a backtick on the active window. Mission accomplished? Not completely: the e-mail key is not easy to use, because it was not meant for typing and you will probably find it slightly out of reach.
To type the backtick more easily, you need to immolate one of the other keys and make it act as if it was the e-mail key. In my case, the perfect candidate for the sacrifice is the ScrollLock key, which I don’t remember having used in 20 years. I’m sure I won’t miss it.
To teach Windows to swap the keys I would need to apply another registry hack, but it’s too complicated to use and explain, especially because there’s a nice little application that can do that for me.
SharpKeys is an application that can remap the keyboard and make a key act as if another key was pressed instead. It does not need to run in background or start with windows, because it’s just a user friendly interface for a registry hack. Once the hack is active, you can even uninstall it if you like.
In this screen capture, I set the ScrollLock key to act as the e-mail key. As you can see, SharpKeys always assumes you are using the US layout and displays the key accordingly, but the important thing is that it can recognize the correct scancode and remap it to the e-mail key.
The nice thing about this hack is that you can use it even if you don’t have the mapped key in your keyboard. In fact, on my laptop there’s no e-mail key at all.
After rempapping the keys, Windows will start typing backticks whenever you press the ScrollLock key.
Now you can focus on Powershell itself, not on memorizing ASCII codes!
EDIT 20/09/2011: In the first version of this post I suggested remapping the e-mail key to the § symbol (which is probably the most useless key on my keyboard), but, actually that would have mapped the WHOLE key, thus loosing the ability to type the “ù” char. That’s why I changed this post and decided to remap the ScrollLock key instead. My apologies to those who followed my advice and lost their “ù”.
EDIT 21/03/2019: The Microsoft Keyboard Layout Creator can help you create a custom keyboard layout that contains the backtick character mapped to any key combination you find appropriate. Go give it a try.
Jeff Moden is the Exceptional DBA 2011!
The contest is over and the winner is Jeff Moden!
Champagne!
I won’t repeat all the reasons why I think that Jeff deserves the award, I just want to say that I could not agree more with this result.
To say it with Grant Fritchey’s (blog|twitter) words: “Of the year? I think I’d put it down to of the decade, but that’s not what the contest was.”
The exceptional DBA 2011 Jeff Moden will receive:
- a trophy at PASS Summit 2011
- a full conference registration for theSummit
- four nights’ accommodations and $300 in travel expenses
- a copy of Red Gate’s SQL Toolbelt
Congratulations Jeff, well deserved.
A better sp_MSForEachDB
Though undocumented and unsupported, I’m sure that at least once you happened to use Microsoft’s built-in stored procedure to execute a statement against all databases. Let’s face it: it comes handy very often, especially for maintenance tasks.
Some months ago, Aaron Bertand (blog|twitter) came up with a nice replacement and I thought it would be fun to code my own.
The main difference with his (and Microsoft’s) implementation is the absence of a cursor. While flagged correctly (LOCAL FORWARD_ONLY STATIC READ_ONLY) and run against a temporary table, nevertheless I was a bit disturbed by that tiny little cursor, so I decided to get rid of it.
Basically, my code relies on a dynamic SQL pushed down three levels:
- sp_executesql
- sp_executesql
- EXEC
This trick can be used as many times as you like, given that you keep on declaring and passing all the parameters you need to the lower levels.
I didn’t provide ad-hoc parameters to implement complex filters on sysdatabases, as I’m convinced that they would not be useful enough in a day to day use. If you like this code and want to use it, feel free to change it to incorporate any kind of filter.
Here is the code:
| -- https://spaghettidba.com/2011/09/09/a-better-sp_msforeachdb/ | |
| -- | |
| -- Author: Gianluca Sartori @spaghettidba | |
| -- Date: 2011/09/09 | |
| -- | |
| -- Description: Executes a statement against multiple databases | |
| -- Parameters: | |
| -- @statement: The statement to execute | |
| -- @replacechar: The character to replace with the database name | |
| -- @name_pattern: The pattern to select the databases | |
| -- It can be: | |
| -- * NULL - Returns all databases | |
| -- * [USER] - Returns users databases only | |
| -- * [SYSTEM] - Returns system databases only | |
| -- * A pattern to use in a LIKE predicate against the database name | |
| CREATE PROCEDURE [dba_ForEachDB] | |
| @statement nvarchar(max), | |
| @replacechar nchar(1) = N'?', | |
| @name_pattern nvarchar(500) = NULL | |
| AS | |
| BEGIN | |
| SET NOCOUNT ON | |
| DECLARE @sql nvarchar(max) | |
| -- LEVEL 3: | |
| -- Build an intermediate statement that replaces the '?' char | |
| SET @sql = 'SET @statement = REPLACE(@statement,'''+ @replacechar +''',DB_NAME()); EXEC(@statement);' | |
| SET @sql = REPLACE(@sql, '''', '''''') | |
| SET @sql = 'N''' + @sql + '''' | |
| -- LEVEL 2: | |
| -- Build a statement to execute on each database context | |
| ;WITH dbs AS ( | |
| SELECT *, | |
| system_db = CASE WHEN name IN ('master','model','msdb','tempdb') THEN 1 ELSE 0 END | |
| FROM sys.databases | |
| WHERE DATABASEPROPERTY(name, 'IsSingleUser') = 0 | |
| AND HAS_DBACCESS(name) = 1 | |
| AND state_desc = 'ONLINE' | |
| ) | |
| SELECT @sql = ( | |
| SELECT | |
| 'EXEC ' + QUOTENAME(name) + '.sys.sp_executesql ' + | |
| @sql + ',' + | |
| 'N''@statement nvarchar(max)'',' + | |
| '@statement;' AS [text()] | |
| FROM dbs | |
| WHERE 1 = | |
| CASE | |
| -- No filter? Return all databases | |
| WHEN @name_pattern IS NULL THEN 1 | |
| -- User databases | |
| WHEN @name_pattern = '[USER]' THEN system_db + 1 | |
| -- System databases | |
| WHEN @name_pattern = '[SYSTEM]' THEN system_db | |
| -- LIKE filter | |
| WHEN name LIKE @name_pattern THEN 1 | |
| END | |
| ORDER BY name | |
| FOR XML PATH('') | |
| ) | |
| -- LEVEL 1: | |
| -- Execute multi-db sql and pass in the actual statement | |
| EXEC sp_executeSQL @sql, N'@statement nvarchar(max)', @statement | |
| END |
Let’s see some examples of its use:
Print the database name for each user database:
EXEC [dba_ForEachDB] @statement = 'PRINT DB_NAME()', @replacechar = '?', @name_pattern = '[USER]'
Display the file path of each database file of system databases:
EXEC [dba_ForEachDB] @statement = 'SELECT physical_name, size FROM sys.database_files', @replacechar = '?', @name_pattern = '[SYSTEM]'
I hope you like it and find it useful.
Happy coding.
spaghettidba 