Query Shorcuts SQL Server SSMS – Get Max Data Length

There are multiple articles on internet regarding Query Shortcuts in SQL Server Management Studio. So I won’t cover details of SSMS Query Shortcuts. But to increase your productivity, I will show you how to get length of maximum values in character fields of any table with query shortcuts.

Background: If you are tuning a tsql view or query, you might want to take a look at underlying table structure design to identify oversized fields. So this article will help you to set up SSMS Query Shortcut [Ctrl]+[6] so that pressing the key will show you all columns with character data with their defined width and actual width of longest value.

Output

As shown in above image Name, ProductNumber, Color, Size etc are all fields of table Production.Product representing length of longest character value on that field, and columns with name like *_SCHM represent its defined width in table design structure.

So let’s get started. Copy/paste below code to query window and execute it. Note that you need to be sysadmin for executing below code. This will create a procedure [dbo].[sp_GetMaximumDataSizeInTable] that returns single line multi-column resultset for table name passed as parameter.

Now, go to Tools > Option. Then navigate to Environment -> Keyboard -> Query Shortcuts. You will see something like below:-

Paste above created proc name sp_GetMaximumDataSizeInTable against Ctrl+6 shortcut key (check above screenshot). Press OK button.

That’s it. We are finished with hard work. To use the procedure open new query window and execute below query

For tables with dbo schema, just highlight the table name and press Ctrl+6 keyboard keys as shown in below screenshot.

For tables other than dbo schema, place <schema name>.<table name> within single quotes ', and press Ctrl+6 keyboard keys by highlighting the whole text along with single quotes.

Conclusion: SSMS Query Shortcuts can let you same significant amount of time by letting you eliminate your repetitive code writing. Happy Coding.

Please feel free to correct me by comments. If you like the article, do Like & Share.

Leave a Reply