Query Shorcuts SQL Server SSMS – Get Max Data Length

Spread the love

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.

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 '[email protected];


	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	'[email protected]+'
	FROM	(
				SELECT '[email protected] + '
				FROM '[email protected]+'
			) AS t
	CROSS JOIN
		(
			'[email protected]+'
		) as i';

	IF (@Query IS NOT NULL)
		EXECUTE (@Query)
	ELSE
		SELECT	'Error' AS Status, DB_NAME() AS [Context Database], 'Object '[email protected]+' 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

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.

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.