SQL Server 2005 - T-SQL 根据参数增加字段大小

发布于 2024-10-18 17:43:52 字数 229 浏览 6 评论 0原文

我想知道是否有一种方法可以循环遍历表中的所有列并根据当前大小增加每个字段的大小。基本上，我需要将当前字段大小低于 1000 个字符的所有字段设置为 1000 个字符。我可以对我想要更改的每个字段执行类似的操作

ALTER TABLE tableName ALTER COLUMN nvarchar(1000)

，但是有数百个字段，如果可能的话，我想以编程方式执行此操作。谢谢。

原文

I'm wondering if there is a way to loop through all of the columns in a table and increase the size of each field based on what the current size. Basically, I need any fields that currently have a field size under 1000 characters to be set to 1000 characters. I can do something like

ALTER TABLE tableName ALTER COLUMN nvarchar(1000)

for each field that I want changed, but there are hundreds of fields and I'd like to do it programmatically if possible. Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

喜你已久 2024-10-25 17:43:52

使用它来生成所有 ALTER TABLEs

SELECT
    'ALTER TABLE ' +
        OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) +
        ' ALTER COLUMN '+ C.name + ' ' + T.[name] + ' (1000) ' +
        CASE WHEN c.is_nullable = 0 THEN 'NOT' ELSE '' END + ' NULL'
FROM
    sys.columns C
    JOIN
    sys.types T ON C.system_type_id = T.system_type_id
WHERE
    T.[name] LIKE '%varchar' AND C.max_length < 1000
    AND
    OBJECTPROPERTYEX(c.object_id, 'IsMSShipped') = 0

使用系统视图更容易。

Use this to generate all your ALTER TABLEs

SELECT
    'ALTER TABLE ' +
        OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) +
        ' ALTER COLUMN '+ C.name + ' ' + T.[name] + ' (1000) ' +
        CASE WHEN c.is_nullable = 0 THEN 'NOT' ELSE '' END + ' NULL'
FROM
    sys.columns C
    JOIN
    sys.types T ON C.system_type_id = T.system_type_id
WHERE
    T.[name] LIKE '%varchar' AND C.max_length < 1000
    AND
    OBJECTPROPERTYEX(c.object_id, 'IsMSShipped') = 0

It's easier with the system views.

回复收藏 0 原文

苏佲洛 2024-10-25 17:43:52

下面是一些循环遍历命名表的所有列的代码。它只是选择他们的详细信息；你需要填写你在循环中所做的事情。

declare @tableName varchar(32)
select @tableName = 'tableName'

declare @i int 
select @i = count(*) from INFORMATION_SCHEMA.COLUMNS
 where Table_Name = @tableName 
while @i > 0 
begin   
  select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = @tableName and Ordinal_Position = @i  
  select @i = @i-1 
end

您在 INFORMATION_SCHEMA.COLUMNS 视图中感兴趣的字段是“DATA_TYPE”和“CHARACTER_MAXIMUM_LENGTH”

我想我还假设您所做的更改不会改变列序号，但如果它们确实改变了您可以通过其他方式选择它们。

Here's some code to loop through all of the columns of a named table. It just selects their details; you need to fill out what you do in the loop.

declare @tableName varchar(32)
select @tableName = 'tableName'

declare @i int 
select @i = count(*) from INFORMATION_SCHEMA.COLUMNS
 where Table_Name = @tableName 
while @i > 0 
begin   
  select * from INFORMATION_SCHEMA.COLUMNS where Table_Name = @tableName and Ordinal_Position = @i  
  select @i = @i-1 
end

The fields that you'll be interested in in the INFORMATION_SCHEMA.COLUMNS view are 'DATA_TYPE' and 'CHARACTER_MAXIMUM_LENGTH'

I guess that I'm also making the assumption that the changes you make won't alter the column ordinals, but if they did you could select them in another way.

回复收藏 0 原文

~没有更多了~