none
How to update a common column name in the database RRS feed

  • Question

  • Is there a script where I can alter column size in the db? This is a common column and I hope not to check each table when updating.

    Thanks

    Wednesday, August 7, 2019 4:10 PM

All replies

  • Hello,

    Yes,

    You use standard script to create new table with different name. In addition - copy data into new table, add relations, triggers and etc. Than - drop original table and rename new one. In some cases you may need to recompile related procedures.

    Simplest way to get a sample of this procedure - in SSMS resize a column and get a script saved.


    Sincerely, Highly skilled coding monkey.

    Wednesday, August 7, 2019 4:18 PM
  • There is no "common" scripts. But you can build one with something like below.

    select 
    	'ALTER TABLE ' + s.name + '.[' + t.name +
    	'] ALTER COLUMN [' + c.name + 
    	'] varchar(255); ' + char(13) + char(10) as [text()]
    from 
    	sys.tables t join sys.schemas s on 
    		t.schema_id = s.schema_id
    	join sys.columns c on 
    		t.object_id = c.object_id
    where 
    	c.name = 'bla-bla-bla'
    for xml path('');
    

    The issue here is deployment. There are three types of alterations:

    1. Metadata-only. Happens when you can change data type on metadata level. Like increasing the size of varchar column. The table will be locked for very short amount of time after table lock is acquired.
    2. Metadata-only with check. The change is done on metadata level; however, it may require to scan table to check the data. Consider reducing varchar size or altering bigint to int column. The lock will be held for the duration of scan
    3. Data modifications - when every row needs to be changed. It happens when you need to change data type in the way that require data conversion. From int to float, for example. Entire table will be rebuilt - this is the case when copying data to another table may be the better option.

    Also, table alteration never ever decreases the row size. You need to rebuild your indexes/table for that.


    Thank you!

    Dmitri V. Korotkevitch (MVP, MCM, MCPD)

    My blog: http://aboutsqlserver.com

    Wednesday, August 7, 2019 9:26 PM
  • Is there a script where I can alter column size in the db? This is a common column and I hope not to check each table when updating.

    Your request is not exactly clear, but say that there are umpteen tables in the database with a column named glocekspiel of the type varchar(20) and you want to make it varchar(40). Then you can do this:

    SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(o.name) +
           ' ALTER COLUMN glockenspiel varchar(40) NULL'
    FROM   sys.tables o
    JOIN   sys.schemas s ON o.schema_id = s.schema_id
    WHERE  EXISTS (SELECT *
                   FROM   sys.columns c
                   WHERE  c.object_id = o.object_id
                     AND  c.name = 'glockenspiel')

    Copy result and paste into query window.

    Here I'm assuming that the column is nullable. If this not the case, this requires more work.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, August 7, 2019 9:43 PM
  • Hi Jason,

    I'm not quite sure about your question. But if you mean changing the data size, like you have a varchar(10) but you want to increase its size to varchar(100). Here’s the code in that case.

    ALTER TABLE ABCDE
    ALTER COLUMN COLUMNNAME varchar(100);
    GO

    Hope it could help. 

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 8, 2019 2:53 AM
  • Hi Jason

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Regards,

    Sabrina


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, August 26, 2019 7:43 AM
  • Hi Jason Alerta Obina,

    Hope the below can help you to solve your issue!

    --Table 1
    CREATE TABLE STUDENTS
    (
    Student_Id INT NOT NULL PRIMARY KEY,
    Student_Name VARCHAR(50) NOT NULL 
    );
    --Table 2
    CREATE TABLE AWARDEES
    (
    Award_Id INT NOT NULL PRIMARY KEY,
    Student_Name VARCHAR(50) NULL 
    );
    --> if you want to increse the varchar size from 50 to 100 here in Student_Name column (BY NOT IMPACTING OF THEIR NULLABILITY), you can use the below code:
    
    DECLARE @column_name VARCHAR(100);
    SET @column_name='Student_Name';
    
    SELECT 'ALTER TABLE ' + QUOTENAME(s.NAME) + '.' + QUOTENAME(o.NAME) +
           ' ALTER COLUMN ' + @column_name + ' VARCHAR(100) ' + CASE WHEN c.is_nullable=0 THEN 'NOT NULL' ELSE 'NULL' END + ' ; '
    AS [Dynamic_Scripts]
    FROM sys.tables o
    INNER JOIN sys.columns c ON o.OBJECT_ID = c.OBJECT_ID
    INNER JOIN sys.schemas s ON o.SCHEMA_ID = s.SCHEMA_ID
    WHERE  c.NAME = @column_name;
    

    --Fix Notes:
    1. As datasize of the columns changing, it impacts sp's/functions/views etc.,You need to cross-check with all the Stored procedures/functions/views and whatever batch_scripts you used for this particular column. 
    2. Also you need to drop & re-create/rebuild your index/constraints respectively if you have any.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    • Proposed as answer by Shah EBanned Thursday, August 29, 2019 3:32 AM
    Thursday, August 29, 2019 3:29 AM