2

I want to find the max number of characters of all varchar columns in DB.

I have a query like this:

SELECT TABLE_NAME,
       COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
      AND DATA_TYPE IN ('varchar', 'nvarchar');

After that I find all varchar and nvarchar columns in db, I want to try every result row in another query:

SELECT LEN(MAX(COLUMN_NAME))
FROM TABLE_SCHEMA;

How can I do that, how can I use a query result rows like another query variables?

2
  • Are you trying to find the maximum posssible length of the column, or the maximum length used in the values of the tables. If you want the first, you don't need any variables, this values are all known in the INFORMATION_SCHEMA.COLUMNS view.
    – Peter
    Commented Nov 29, 2022 at 7:08
  • I seek the maximum length used in the values of the tables.
    – bullmaca
    Commented Nov 29, 2022 at 7:18

1 Answer 1

1

You can use script below to find the length. Be aware that this can be a long running script.

DROP TABLE IF EXISTS ##SizePerTablePerColumn;

/*Create a global temp table to store results*/
CREATE TABLE ##SizePerTablePerColumn
(
   TABLE_NAME sysname,
   COLUMN_NAME sysname,
   MAX_LENGTH_USED BIGINT
);

INSERT INTO ##SizePerTablePerColumn
(
   TABLE_NAME,
   COLUMN_NAME,
   MAX_LENGTH_USED
)
SELECT
      TABLE_NAME,
      COLUMN_NAME,
      NULL AS MAX_LENGTH_USED
FROM  INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
      AND DATA_TYPE IN ( 'varchar', 'nvarchar' );

WHILE EXISTS (
                SELECT 1
                FROM   ##SizePerTablePerColumn AS sptpc
                WHERE  sptpc.MAX_LENGTH_USED IS NULL
             )
BEGIN

   DECLARE @QueryToExecute NVARCHAR(MAX);

   SELECT TOP (1)
          @QueryToExecute = CONCAT(
                                     N'UPDATE ##SizePerTablePerColumn SET MAX_LENGTH_USED = ( SELECT ISNULL(MAX(LEN(',
                                     QUOTENAME(COLUMN_NAME),
                                     ')),0) FROM dbo.',
                                     sptpc.TABLE_NAME,
                                     ') WHERE TABLE_NAME = ''',
                                     TABLE_NAME,
                                     ''' AND COLUMN_NAME = ''',
                                     COLUMN_NAME,
                                     ''' AND MAX_LENGTH_USED IS NULL'
                                  )
   FROM   ##SizePerTablePerColumn AS sptpc
   WHERE  sptpc.MAX_LENGTH_USED IS NULL;

   --PRINT @QueryToExecute;
   EXEC (@QueryToExecute);

   SET @QueryToExecute = NULL;

END;

SELECT *
FROM   ##SizePerTablePerColumn;

DROP TABLE ##SizePerTablePerColumn;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.