none
Automatically alter tables based on results of a query...

    Question

  • I need to increase the size of every varchar field in my database where the size is 1000 or greater, but not 5000.  I ran the following code:

    select TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS
    where DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH >= 1000 AND CHARACTER_MAXIMUM_LENGTH != 5000
    

    That returns 50-something rows.  For each row, I'd like to do the following:

    ALTER TABLE [dbo].[TABLE_NAME] ALTER COLUMN [COLUMN_NAME] varchar(5000)
    Any clever ways to do this in a simple automated process?

    Wednesday, April 24, 2013 6:43 PM

Answers

  • Try

    declare @SQL nvarchar(max);
    
    set @SQL = (select '; 
    ALTER TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) + 
    ' ALTER COLUMN '  + quotename(COLUMN_NAME) + ' varchar(5000)' 
     from INFORMATION_SCHEMA.COLUMNS
    where DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH >= 1000 AND CHARACTER_MAXIMUM_LENGTH != 5000
    for xml path(''), type).value('.', 'nvarchar(max)')
    
    
    print @SQL
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 24, 2013 7:10 PM

All replies

  • Yes, you would need to use dynamic query. Take a look at this blog post explaining the technique

    How to get information about all databases without a loop


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 24, 2013 6:46 PM
  • Thanks for the reply Naomi.  I get that I'll need to build a dynamic query to do the ALTER TABLE, but In those examples I don't see where they iterate through a SELECT.  Any ideas?
    Wednesday, April 24, 2013 7:03 PM
  • Try

    declare @SQL nvarchar(max);
    
    set @SQL = (select '; 
    ALTER TABLE ' + quotename(TABLE_SCHEMA) + '.' + quotename(TABLE_NAME) + 
    ' ALTER COLUMN '  + quotename(COLUMN_NAME) + ' varchar(5000)' 
     from INFORMATION_SCHEMA.COLUMNS
    where DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH >= 1000 AND CHARACTER_MAXIMUM_LENGTH != 5000
    for xml path(''), type).value('.', 'nvarchar(max)')
    
    
    print @SQL
    


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, April 24, 2013 7:10 PM