Using SQL script, how to determine the length of a column

SuperCoder 236 Reputation points
2023-11-01T16:19:50.74+00:00

Windows Copilot generated this code but it returns NULL for the length of each column. Any ideas?

SELECT COLUMN_NAME, COL_LENGTH ('customers', COLUMN_NAME) AS 'Column Length'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'customers';

SQLResults

SQL Server Other
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 26,586 Reputation points
    2023-11-01T21:05:44.6233333+00:00

    Hi @SuperCoder,

    It looks like COL_LENGTH() function table parameter needs schema prefix for the table name.

    Check it out below.

    USE AdventureWorks2022;
    GO
    -- not working
    SELECT COLUMN_NAME, COL_LENGTH ('Person', COLUMN_NAME) AS 'Column Length'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Person';
    -- working
    SELECT COLUMN_NAME, COL_LENGTH ('Person.Person', COLUMN_NAME) AS 'Column Length'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'Person';
    
    3 people found this answer helpful.

  2. Vahid Ghafarpour 23,385 Reputation points Volunteer Moderator
    2023-11-01T17:57:22.7766667+00:00

    There is no issue with the query! You might not have the necessary permissions to access the column length information. Ensure you have the appropriate permissions to access the schema and tables.

    Can you see the table structure in SSMS?


  3. Anonymous
    2023-11-02T02:03:44.7066667+00:00

    Hi @SuperCoder

    User's image

    In the detailed description of the COL_LENGTH, there are such Exceptions.

    https://learn.microsoft.com/en-us/sql/t-sql/functions/col-length-transact-sql?view=sql-server-ver16#exceptions

    Best regards,

    Percy Tang

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.