SQL Server中使用COL_LENGTH检索表时出现报错的技术问询

错误原因 #

你遇到的Cannot call methods on nvarchar报错，是因为：

1. SUBQUERY.TABLE_NAME是字符串类型的表名，不能用.语法去引用列，SQL会误判为调用字符串的方法；
2. COL_LENGTH函数的用法错误，它的正确语法是COL_LENGTH('架构.表名', '列名')，你传入的参数格式完全不符合要求。

正确查询语句 #

这里提供两种可行的写法，都能实现你的需求：

写法一：通过聚合筛选

SELECT 
    TABLE_SCHEMA,
    TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
    TABLE_CATALOG = 'DB_Name'
    AND COLUMN_NAME IN ('Column_1', 'Column_2')
GROUP BY 
    TABLE_SCHEMA,
    TABLE_NAME
HAVING 
    COUNT(DISTINCT COLUMN_NAME) = 2
    AND MAX(CASE WHEN COLUMN_NAME = 'Column_1' THEN COL_LENGTH(TABLE_SCHEMA + '.' + TABLE_NAME, COLUMN_NAME) END) 
        != MAX(CASE WHEN COLUMN_NAME = 'Column_2' THEN COL_LENGTH(TABLE_SCHEMA + '.' + TABLE_NAME, COLUMN_NAME) END)
INTERSECT
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_CATALOG = 'DB_Name'
    AND TABLE_TYPE = 'BASE TABLE'

写法二：通过JOIN关联列信息

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c1 
    ON t.TABLE_CATALOG = c1.TABLE_CATALOG
    AND t.TABLE_SCHEMA = c1.TABLE_SCHEMA
    AND t.TABLE_NAME = c1.TABLE_NAME
    AND c1.COLUMN_NAME = 'Column_1'
JOIN INFORMATION_SCHEMA.COLUMNS c2
    ON t.TABLE_CATALOG = c2.TABLE_CATALOG
    AND t.TABLE_SCHEMA = c2.TABLE_SCHEMA
    AND t.TABLE_NAME = c2.TABLE_NAME
    AND c2.COLUMN_NAME = 'Column_2'
WHERE 
    t.TABLE_CATALOG = 'DB_Name'
    AND t.TABLE_TYPE = 'BASE TABLE'
    AND COL_LENGTH(t.TABLE_SCHEMA + '.' + t.TABLE_NAME, 'Column_1') 
        != COL_LENGTH(t.TABLE_SCHEMA + '.' + t.TABLE_NAME, 'Column_2')

说明 #

• 两种写法都会先筛选出同时包含Column_1和Column_2的基表，再比较两列的存储长度（COL_LENGTH返回的结果）；
• 如果你的需求是比较字符类型的定义长度（比如nvarchar(50)里的50），可以把COL_LENGTH替换为CHARACTER_MAXIMUM_LENGTH，直接从INFORMATION_SCHEMA.COLUMNS中取值即可。

内容的提问来源于stack exchange，提问作者SaberTooth