In SQL Server, you can use the COL_LENGTH() function to get the length of a column. More specifically, the function returns the defined length of the column, in bytes.

The function accepts two arguments: the table name, and the column name.

Example 1 – Basic Usage

Here’s an example to demonstrate.

USE Music;
SELECT COL_LENGTH('dbo.Artists', 'ArtistName') AS Result;

Result:

+----------+
| Result   |
|----------|
| 510      |
+----------+

Example 2 – Wrong Database?

If you get a NULL result, check that you’re querying the correct database.

The previous example uses a database called Music and that database does have a table and column of those names. If the database doesn’t have a table/column combination as specified, the result is NULL.

Here’s what happens if I query the wrong database:

USE WideWorldImportersDW;
SELECT COL_LENGTH('dbo.Artists', 'ArtistName') AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

Example 3 – Some More Columns

Here’s an example that returns more columns from the same table.

USE Music;
DECLARE @table_name nvarchar(50) = 'dbo.Artists';
SELECT 
  COL_LENGTH(@table_name, 'ArtistId') AS ArtistId,
  COL_LENGTH(@table_name, 'ArtistName') AS ArtistName,
  COL_LENGTH(@table_name, 'ActiveFrom') AS ActiveFrom,
  COL_LENGTH(@table_name, 'CountryId') AS CountryId;

Result:

+------------+--------------+--------------+-------------+
| ArtistId   | ArtistName   | ActiveFrom   | CountryId   |
|------------+--------------+--------------+-------------|
| 4          | 510          | 3            | 4           |
+------------+--------------+--------------+-------------+

Example 4 – Querying sys.columns

In this example I compare the results to the max_length column of the sys.columns system view.

SELECT 
  OBJECT_NAME(object_id) AS [Table Name],
  name AS [Column Name],
  max_length,
  COL_LENGTH(OBJECT_NAME(object_id), name) AS [COL_LENGTH()]
FROM sys.columns
WHERE name IN ('ArtistId', 'ArtistName', 'ActiveFrom', 'CountryId');

Result:

+-------------------+---------------+--------------+----------------+
| Table Name        | Column Name   | max_length   | COL_LENGTH()   |
|-------------------+---------------+--------------+----------------|
| ufn_AlbumsByGenre | ArtistName    | 510          | 510            |
| Artists           | ArtistId      | 4            | 4              |
| Artists           | ArtistName    | 510          | 510            |
| Artists           | ActiveFrom    | 3            | 3              |
| Artists           | CountryId     | 4            | 4              |
| Albums            | ArtistId      | 4            | 4              |
| Country           | CountryId     | 4            | 4              |
| RockAlbums        | ArtistName    | 510          | 510            |
| JazzAlbums        | ArtistName    | 510          | 510            |
| BluesAlbums       | ArtistName    | 510          | 510            |
+-------------------+---------------+--------------+----------------+

The columns and their respective lengths returned here are from multiple tables. In the case of ArtistId, there’s a primary key of this name in the Artists table and a foreign key of the same name in the Albums table. This query also happens to return columns from three views, as well as a table-valued function.