动态T-SQL-将列定义更改为字段的最大长度

Question

我正在尝试动态创建代码，以将列定义更改为字段的最大长度。

请注意，数据库的内容不会更改。

这是我到目前为止所拥有的，但我不能单独执行最大长度查询来获取数字。我哪里错了？

谨致问候。

DECLARE @SQL_STMT VARCHAR(MAX) = ''
SELECT @SQL_STMT = @SQL_STMT
    + '''ALTER TABLE '
    + TABLE_NAME
    + ' ALTER COLUMN '
    + COLUMN_NAME
    + ' '
    + DATA_TYPE
    + '('' SELECT MAX(LEN('
    + COLUMN_NAME
    + ')) FROM '
    + TABLE_NAME
    + ''') ''
    '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'varchar'
PRINT(@SQL_STMT)

Answer 1

EDIT我的第一个版本不够完善，下面是我测试过的新版本：

DECLARE @SQL_STMT VARCHAR(MAX) = 'DECLARE @query nvarchar(max);'
SELECT @SQL_STMT = @SQL_STMT
    + 'SET @query =''ALTER TABLE '
    + TABLE_NAME
    + ' ALTER COLUMN '
    + COLUMN_NAME
    + ' '
    + DATA_TYPE
    + '('' +CAST((SELECT MAX(DATALENGTH('
    + COLUMN_NAME
    + ')) FROM '
    + TABLE_NAME
    + ') as nvarchar(max))+'') ''
    exec(@query);'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'nvarchar'
PRINT(@SQL_STMT)

Answer 2

我不是这个想法的朋友，但这应该是你想要的：

DECLARE @SQL_STMT VARCHAR(MAX) = ''
SELECT 
    IDENTITY(int,1,1) as ID, -- for later update   
     'ALTER TABLE '
    + c.TABLE_NAME
    + ' ALTER COLUMN '
    + c.COLUMN_NAME
    + ' '
    + c.DATA_TYPE
    + '('  P1
    , '  MAX(LEN('
    + c.COLUMN_NAME
    + ')'
    +') FROM '
    + c.TABLE_NAME P2
    , ') ' P3
into #tmp    
FROM INFORMATION_SCHEMA.Tables t 
JOIN INFORMATION_SCHEMA.Columns c 
on    c.TABLE_CATALOG= t.TABLE_CATALOG 
  and c.TABLE_SCHEMA=t.TABLE_SCHEMA
  and c.TABLE_NAME=t.TABLE_NAME
where TABLE_TYPE='BASE TABLE' -- only Tables not views
and DATA_TYPE = 'varchar'
Select @SQL_STMT=''  -- collect ID + Max info e.g. SELECT 1,   MAX(LEN(FirstName)) FROM user_info 
Select @SQL_STMT=@SQL_STMT + 'SELECT '+ CAST(ID as varchar(10)) + ', '+ P2 +CHAR(13) 
from #tmp
Declare @a table (ID Integer,Size Integer) -- table for ID and Len
--print @SQL_STMT
insert into @a Exec (@SQL_STMT)  -- fill table by executing block
                     -- define a minimum size if 0  
Update #tmp set P2 = Case When Size<1 then 1 else Size end  -- update p2
From @a a where a.ID = #tmp.ID
Select @SQL_STMT=''
Select @SQL_STMT=@SQL_STMT + P1 + P2 + P3 +CHAR(13) 
from #Tmp
print @SQL_STMT
Exec(@SQL_STMT)

Drop table #tmp

Answer 3

不能使用SELECT语句或整数变量作为ALTER TABLE语句中列的大小。一般来说，不能在同一语句中混合DDL（数据描述语言，例如CREATE、ALTER、DROP）和DML（数据操作语言，例如，SELECT、INSERT、UPDATE）。

这意味着您必须使用动态SQL来完成任务。您将无法使用动态SQL来构建可以保存和重用的静态SQL。您需要检索字段的最大长度并将其保存到一个变量中，然后构造ALTER语句，最后构造EXEC () ALTER语句。最简单的方法可能是通过INFORMATION_SCHEMA.COLUMNS使用光标，但也可以使用双动态SQL。也就是说，动态SQL本身会生成动态SQL。然而，调试起来要困难得多。

除此之外，我还质疑你尝试做的事情的有效性。简单地说，你永远不应该使用这个代码。数据库模式应该相对固定，因为更改它会对性能产生重大影响。通过更改列的大小，您告诉数据库引擎，它需要更改在磁盘上物理存储数据的方式，而这不是您应该轻松或定期做的事情。只要设置足够大的值以满足用户需求，您就可以走得更远。现代RDBMS在不低效地存储数据方面要好得多，但更改列大小通常仍然是一种糟糕的做法。