Retrieve tables based on COL_LENGTH in SQL server-CodePudding

I'm trying to establish a query design that can search my whole database and identify which tables have both of my columns (say these are 'Column_1', 'Column_2'), and only present the tables where the COL_LENGTH doesn't match. This the following design I've made, but SQL wouldn't process it as 'Cannot call methods on nvarchar'

SELECT SUBQUERY.TABLE_NAME
FROM 
(
SELECT
    TABLE_NAME
FROM
    INFORMATION_SCHEMA.TABLES T
WHERE
    T.TABLE_CATALOG = 'DB_Name' AND
    T.TABLE_TYPE = 'BASE TABLE'
    AND EXISTS (
        SELECT T.TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE
            C.TABLE_CATALOG = T.TABLE_CATALOG AND
            C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
            C.TABLE_NAME = T.TABLE_NAME AND
            C.COLUMN_NAME = 'Column_1')
    AND EXISTS
    (
    SELECT T.TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS C
        WHERE
            C.TABLE_CATALOG = T.TABLE_CATALOG AND
            C.TABLE_SCHEMA = T.TABLE_SCHEMA AND
            C.TABLE_NAME = T.TABLE_NAME AND
            C.COLUMN_NAME = 'Column_2')
) AS SUBQUERY
WHERE
COL_LENGTH(SUBQUERY.TABLE_NAME.Column_1, 'Len_Column_1') != COL_LENGTH(SUBQUERY.TABLE_NAME.Column_2, 'Len_Column_2')

CodePudding user response：

I would just start over and not use INFORMATION_SCHEMA at all.

SELECT [table] = t.name, 
    c1name = c1.name, 
    c1type = concat(type_name(c1.system_type_id), '(', c1.max_length, ')'),
    c2name = c2.name,
    c2type = concat(type_name(c2.system_type_id), '(', c2.max_length, ')')
  FROM sys.tables AS t
  INNER JOIN sys.columns AS c1 ON t.[object_id] = c1.[object_id]
  INNER JOIN sys.columns AS c2 ON t.[object_id] = c2.[object_id]
  WHERE c1.name = N'column_1'
    AND c2.name = N'column_2'
    AND (c1.system_type_id <> c2.system_type_id
     OR c1.max_length <> c2.max_length);

Here is an example fiddle.

Note that max_length is double when the type is nchar, nvarchar, etc. but it shouldn't matter for comparison purposes (because a difference only between varchar/nvarchar or even nchar/nvarchar should still be highlighted). Also it doesn't matter for max - those are both -1 but it would still be returned by this query if one was varchar(max) and the other was nvarchar(max).