Querying Column Definition for Data Length - SQL Server

user_3618940 0
Viewed 39

I am looking to generate a list of all the tables, columns, and datatypes for a database.

And this query will give me all information except for the length of the data types:

SELECT t.name AS TableName,
       c.name AS ColumnName,
       y.name AS DataType
FROM sys.tables AS t
    JOIN sys.columns AS c ON t.object_id = c.object_id
    JOIN sys.types AS y ON y.user_type_id = c.user_type_id
WHERE t.name = 'Tablename';

But for the columns with data types of varchar or nvarchar, I would like to also see the max limit for each.

For instance, if ColumnA is varchar(100) - where can I find the 100 limit in the database? So far, I cannot find where that information is stored.

Thanks,

sql-server-2016 tsql
1 Answers

sys.columns has a property called max_length:

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    c.max_length,
    y.name AS DataType
FROM
    sys.tables AS t
JOIN 
    sys.columns AS c ON t.object_id = c.object_id
JOIN 
    sys.types AS y ON y.user_type_id = c.user_type_id
WHERE 
    t.name = 'Tablename';

See the official MS documentation on sys.columns for more details

user_133020
Related