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.
|
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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
USE master GO -- Proc to return length of maximum value of each character column of table CREATE PROCEDURE [dbo].[sp_GetMaximumDataSizeInTable] @SchematableName sysname AS BEGIN SET NOCOUNT ON; DECLARE @SchemaName sysname = REPLACE(REPLACE(IIF(CHARINDEX('.',@SchematableName,0)<>0,LEFT(@SchematableName,CHARINDEX('.',@SchematableName,0)-1),'dbo'),'[',''),']','') DECLARE @TableName sysname = REPLACE(REPLACE(RIGHT(@SchematableName,LEN(@SchematableName)-CHARINDEX('.',@SchematableName,0)),'[',''),']','') DECLARE @MyColumns VARCHAR(MAX) = NULL ,@Query VARCHAR(MAX); DECLARE @InformationColumns VARCHAR(max) ,@InformationQuery VARCHAR(MAX); DECLARE @DisplayColumns VARCHAR(MAX); SELECT @InformationColumns = COALESCE(@InformationColumns+' ,'+CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+' AS ['+ C.COLUMN_NAME+ '_SCHM]',CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR)+' AS ['+C.COLUMN_NAME+'_SCHM]') FROM INFORMATION_SCHEMA.COLUMNS AS C WHERE C.TABLE_NAME = @TableName AND C.TABLE_SCHEMA = @SchemaName AND C.DATA_TYPE LIKE '%char%'; SET @InformationQuery = 'SELECT '+@InformationColumns; SELECT @MyColumns = COALESCE(@MyColumns+' ,MAX(LEN(['+C.COLUMN_NAME+'])) AS ['+C.COLUMN_NAME+']','MAX(LEN(['+C.COLUMN_NAME+'])) AS ['+C.COLUMN_NAME+']') FROM INFORMATION_SCHEMA.COLUMNS AS C WHERE C.TABLE_NAME = @TableName AND C.TABLE_SCHEMA = @SchemaName AND C.DATA_TYPE LIKE '%char%' SELECT @DisplayColumns = COALESCE(@DisplayColumns+' ,'+'['+C.COLUMN_NAME+'],['+C.COLUMN_NAME+'_SCHM]','['+C.COLUMN_NAME+'],['+C.COLUMN_NAME+'_SCHM]') FROM INFORMATION_SCHEMA.COLUMNS AS C WHERE C.TABLE_NAME = @TableName AND C.TABLE_SCHEMA = @SchemaName AND C.DATA_TYPE LIKE '%char%'; SET @Query = ' SELECT '+@DisplayColumns+' FROM ( SELECT '+@MyColumns + ' FROM '+@SchematableName+' ) AS t CROSS JOIN ( '+@InformationQuery+' ) as i'; IF (@Query IS NOT NULL) EXECUTE (@Query) ELSE SELECT 'Error' AS Status, DB_NAME() AS [Context Database], 'Object '+@SchematableName+' is not found in ['+DB_NAME()+ '] database.' AS [Message]; END GO -- Mark the proc as system object to execute under every user database context EXEC sys.sp_MS_marksystemobject sp_GetMaximumDataSizeInTable GO |
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
|
1 2 |
USE AdventureWorks EXEC dbo.sp_GetMaximumDataSizeInTable @SchematableName = 'Production.Product' |
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.
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.