sql-server - 通过循环查找最大长度

Question

我有以下查询，它返回两列表名和字段名：sample s sample record: table ='TABLOCTYP' field name='TYPDES'，查询返回记录 206 行。我们使用的是 SQL Server 2012。

 SELECT 
   CODFIC_0 AS 'Table',
   ATABZON.CODZONE_0 AS 'Field' 
   FROM LIVE.AMSKZON
          INNER JOIN LIVE.ATABZON ON AMSKZON.CODTYP_0 = ATABZON.CODTYP_0
 WHERE ATABZON.CODTYP_0 ='DES'
 AND CODMSK_0 IS NOT NULL
 GROUP BY CODFIC_0, ATABZON.CODTYP_0
 ORDER BY 1 

图一是我第一次查询的结果

我希望获取这些结果并通过可能的表值参数（可能是循环）传递它们。我表中每一行的内容如下所示。我不知道如何从和平或扫描可能表。

  DECLARE @table        nvarch(15)
  DECLARE @field        nvarch(25)

  sET @table = 'ABATABT'
  SET @field ='NOMABT'
  SELECT MAX(LEN(@field),
  FROM @table 

我的结果将是表中的最大字段长度和字段内容。我需要知道哪些记录的最大长度 >12，以便在导入新系统之前清理它们。所以我需要显示的字段内容才能知道我需要清理什么。

我在“Group By”附近得到了不正确的 Syntec

 exec ('select  
'''+@tab_name+''','''+@col_name+''',max(len('+@col_name+')),'''+@col_name+''' 
from [xxxx].'+@tab_name+'''Group By'''+@col_name+'''Having 
max(len('+@col_name+'))>12''')

 set @counter -= 1
end

标签： sql-servertsql

Answer 1

您可以使用动态 SQL 从 YourTable 中获取每个 table_column 对的列的详细信息。

尝试以下

drop table if exists #temp
create table dbo.result_tab (TableName varchar(100), FieldName varchar(100), Max_Length int, Max_Value varchar(1000))
select *, ROW_NUMBER() over (order by (select null)) rn into #temp from dbo.YourTable

declare @counter int = (select count(1) from #temp)
declare @tab_name varchar(100)
declare @col_name varchar(100)

while (@counter > 0)
begin
    set @tab_name = (select TableName from #temp where rn = @counter)
    set @col_name = (select FieldName from #temp where rn = @counter)

    print('insert into result_tab select '''+@tab_name+''','''+@col_name+''',max(len('+@col_name+')), null from dbo.'+@tab_name+'')
    exec ('insert into result_tab select '''+@tab_name+''','''+@col_name+''',max(len('+@col_name+')), null from dbo.'+@tab_name+'')
    print('update result_tab set Max_Value = (select top 1 '+@col_name+' from '+@tab_name+' where len('+@col_name+') = (select Max_Length from dbo.result_tab where TableName = '''+@tab_name+''' and FieldName = '''+@col_name+''')) where TableName = '''+@tab_name+''' and FieldName = '''+@col_name+'''')
    exec ('update result_tab set Max_Value = (select top 1 '+@col_name+' from '+@tab_name+' where len('+@col_name+') = (select Max_Length from dbo.result_tab where TableName = '''+@tab_name+''' and FieldName = '''+@col_name+''')) where TableName = '''+@tab_name+''' and FieldName = '''+@col_name+'''')

    set @counter -= 1
end

select * from result_tab

请在此处查看演示。HTH。