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

SuperCoder 136

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

3 answers

Yitzhak Khabinsky 25,866 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';
Please sign in to rate this answer.

3 people found this answer helpful.
SuperCoder 136 Reputation points

2023-11-02T16:06:50.81+00:00

I added the table name in front of the code column, but I still NULL results.

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

Yitzhak Khabinsky 25,866 Reputation points

2023-11-02T20:55:50.2433333+00:00

@SuperCoder,

Its seems to be a problem with your attempt.

You need to add (prepend) a schema name in front of the table name:

'dbo.revenue' or 'whateverSchema.revenue'

Erland Sommarskog 111.8K Reputation points MVP

2023-11-02T21:52:40.23+00:00

Here is an example to illustrate what Yithzak is talking about:

CREATE SCHEMA MySchema go CREATE TABLE MySchema.MyTable (MyColumn varchar(22)) go SELECT COLUMN_NAME, COL_LENGTH ('MyTable', COLUMN_NAME) AS 'Column Length' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'; SELECT COLUMN_NAME, COL_LENGTH ('MySchema.MyTable', COLUMN_NAME) AS 'Column Length' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable'; go DROP TABLE MySchema.MyTable DROP SCHEMA MySchema

If you don't specify the schema when referring to table, SQL Server will assume the dbo schema, but apparently your table is another schema.

SuperCoder 136 Reputation points

2023-11-06T17:04:47.2366667+00:00

Don't think this solution is workable for our customers and I will find another way for them to determine the length of columns in some of the data tables. Thank you for your response.

Erland Sommarskog 111.8K Reputation points MVP

2023-11-06T21:17:12.9133333+00:00

Hm, exatly what is not workable? If the table is not in default schema, you need to specify the schema. There is no way around that.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Vahid Ghafarpour 21,710 Reputation points

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?
Please sign in to rate this answer.
SuperCoder 136 Reputation points

2023-11-01T18:24:08.1366667+00:00

I can see the table structure in SSMS and can modify the table. I will try to run the script while longed into SSMS using the sa account.

SuperCoder 136 Reputation points

2023-11-01T20:50:42.9733333+00:00

I logged into SSMS as SA and there result was the same. Any ideas?

Vahid Ghafarpour 21,710 Reputation points

2023-11-04T05:22:26.81+00:00

It is weird. I tested it again in my instance, and no issue! As Percy mentioned, I only have the same error with permissions.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
PercyTang-MSFT 12,501 Reputation points Microsoft Vendor

2023-11-02T02:03:44.7066667+00:00

Hi @SuperCoder

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
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

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

3 answers

Your answer