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
