时间戳

首页 > 解决方案 > Querying Column Definition for Data Length - SQL Server

tsql - Querying Column Definition for Data Length - SQL Server

问题描述

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,

标签： tsqlsql-server-2016

解决方案

sys.columns有一个属性叫做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';

有关详细信息，请参阅官方 MS 文档sys.columns

推荐阅读