Table Design Optimization : Column Sizes Script

I was called on to help with a data problem today.
The developer concerned had misjudged data sizes and hence defined a table that she couldnt import into.
My approach was to define a table with generous text column sizes with a view to reducing them later.

This script shows defined column sizes along with the size of the largest data for that column.
Change the @SCHEMA and @TABLE variables at the top of the script to point it at the table of your choice >

SET NOCOUNT ON 
SET NOCOUNT ON 
SET ANSI_WARNINGS ON
DECLARE @SCHEMA  VARCHAR(50)
DECLARE @TABLE  VARCHAR(50)

SET @SCHEMA = 'DBO'
SET @TABLE  = 'spt_values'

DECLARE  @CURRENTROW INT
DECLARE  @TOTALROWS INT
DECLARE  @COLUMNMAXSIZE INT
DECLARE  @SQLSTRING NVARCHAR(MAX)
DECLARE  @PARAMETER NVARCHAR(500);
DECLARE  @TABLEDETAILS 
  TABLE(UNIQUEROWID     INT   IDENTITY ( 1,1 ),
     TABLE_SCHEMA    VARCHAR(255),
     TABLE_NAME      VARCHAR(255),
     COLUMN_NAME     VARCHAR(255),
     COLUMN_TYPE     VARCHAR(255),
     MAX_LENGTH      INT,
     MAX_DATA_LENGTH INT)
       
INSERT INTO @TABLEDETAILS
          (TABLE_SCHEMA,
           TABLE_NAME,
           COLUMN_NAME,
           COLUMN_TYPE,
           MAX_LENGTH)

SELECT SCHEMA_NAME(O.SCHEMA_ID)  AS TABLE_SCHEMA,
      OBJECT_NAME(O.OBJECT_ID)  AS TABLE_NAME,
      C.NAME                    AS COLUMN_NAME,
      T.NAME                    AS COLUMN_TYPE,
      C.MAX_LENGTH
FROM   SYS.TABLES O
INNER JOIN SYS.COLUMNS C
       ON C.OBJECT_ID = O.OBJECT_ID
INNER JOIN SYS.TYPES T
       ON C.SYSTEM_TYPE_ID = T.SYSTEM_TYPE_ID
      AND T.NAME IN ('CHAR','VARCHAR','NCHAR','NVARCHAR')
WHERE SCHEMA_NAME(O.SCHEMA_ID)  <> 'sys'
AND OBJECT_NAME(O.OBJECT_ID) = @TABLE
AND SCHEMA_NAME(O.SCHEMA_ID) = @SCHEMA
                                
SELECT @TOTALROWS = COUNT(*) FROM   @TABLEDETAILS
SELECT @CURRENTROW = 1
WHILE @CURRENTROW <= @TOTALROWS   
BEGIN     
SET @COLUMNMAXSIZE = 0      
SELECT @SQLSTRING = 'SELECT @COLUMNSIZEMAX = MAX(LEN([' + COLUMN_NAME + '])) FROM [' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'     FROM   @TABLEDETAILS     WHERE  UNIQUEROWID = @CURRENTROW                               
SET @PARAMETER = N'@COLUMNSIZEMAX INT OUTPUT';          
EXECUTE SP_EXECUTESQL       @SQLSTRING 
     ,       @PARAMETER 
     ,       @COLUMNSIZEMAX = @COLUMNMAXSIZE OUTPUT      
UPDATE @TABLEDETAILS     
SET    MAX_DATA_LENGTH = @COLUMNMAXSIZE     
WHERE  UNIQUEROWID = @CURRENTROW                               

-- DISPLAY PROGRESS (May exceed max results sets if uncommented)     
-- SELECT * FROM @TABLEDETAILS WHERE  UNIQUEROWID = @CURRENTROW       
SET @CURRENTROW = @CURRENTROW + 1   
END      

SELECT   TABLE_SCHEMA
  ,TABLE_NAME
  ,COLUMN_NAME
  ,COLUMN_TYPE
  ,CASE MAX_LENGTH WHEN -1 THEN 'MAX' ELSE CONVERT(CHAR(10),MAX_LENGTH) END AS COLUMN_MAX_LENGTH
  ,MAX_DATA_LENGTH 
FROM @TABLEDETAILS