Created
April 8, 2025 19:25
-
-
Save kevinmartintech/96446ee2ab9cd85958f600ac573dd219 to your computer and use it in GitHub Desktop.
This script help find the longest data value in each of the text columns. Helpful for creating ETL tables.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @TableName sysname = '<SchemaName, , dbo>.<TableName, , MyTable>'; | |
| SET NOCOUNT ON; | |
| DECLARE | |
| @column_id varchar(36) | |
| ,@StringToExecute nvarchar(MAX); | |
| DROP TABLE IF EXISTS #Receiver; | |
| CREATE TABLE #Receiver (LongestDataLength int NULL); | |
| DROP TABLE IF EXISTS #Results; | |
| CREATE TABLE #Results ( | |
| column_id varchar(36) NOT NULL | |
| ,TableName sysname NOT NULL | |
| ,ColumnName sysname NOT NULL | |
| ,DataType sysname NOT NULL | |
| ,MaxColumnLength varchar(250) NULL | |
| ,LongestDataLength varchar(250) NULL | |
| ,StringToExecute nvarchar(MAX) NULL | |
| ); | |
| INSERT INTO #Results ( | |
| column_id, TableName, ColumnName, DataType, MaxColumnLength, LongestDataLength, StringToExecute | |
| ) | |
| SELECT | |
| column_id = C.column_id | |
| ,TableName = OBJECT_NAME(C.object_id) | |
| ,ColumnName = C.name | |
| ,DataType = T.name | |
| ,MaxColumnLength = CASE WHEN T.name NOT IN ('varchar', 'nvarchar', 'char', 'nchar', 'sysname') | |
| THEN 'NA' | |
| WHEN C.max_length = -1 | |
| THEN 'MAX' | |
| ELSE CAST(CASE WHEN T.name IN ('nvarchar', 'nchar', 'sysname') THEN C.max_length / 2 ELSE C.max_length END AS varchar(250)) | |
| END | |
| ,LongestDataLength = 'NA' | |
| ,StringToExecute = N'SELECT MAX(LEN(' + QUOTENAME(C.name) + ')) FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(C.object_id)) + '.' + QUOTENAME(OBJECT_NAME(C.object_id)) + N';' | |
| FROM | |
| sys.columns AS C | |
| INNER JOIN sys.types AS T ON C.user_type_id = T.user_type_id | |
| WHERE | |
| C.object_id = OBJECT_ID(@TableName); | |
| DECLARE LengthCursor CURSOR LOCAL FAST_FORWARD FOR | |
| SELECT column_id, StringToExecute FROM #Results WHERE MaxColumnLength <> 'NA' ORDER BY column_id ASC; | |
| OPEN LengthCursor; | |
| FETCH NEXT FROM LengthCursor | |
| INTO | |
| @column_id | |
| ,@StringToExecute; | |
| WHILE @@FETCH_STATUS = 0 | |
| BEGIN | |
| INSERT INTO #Receiver (LongestDataLength) EXEC sys.sp_executesql @stmt = @StringToExecute; | |
| UPDATE | |
| #Results | |
| SET | |
| LongestDataLength = (SELECT LongestDataLength FROM #Receiver) | |
| WHERE | |
| column_id = @column_id; | |
| DELETE FROM #Receiver; | |
| FETCH NEXT FROM LengthCursor | |
| INTO | |
| @column_id | |
| ,@StringToExecute; | |
| END; | |
| CLOSE LengthCursor; | |
| DEALLOCATE LengthCursor; | |
| SELECT TableName, ColumnName, DataType, MaxColumnLength, LongestDataLength FROM #Results; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment