Ytukyg

I want to pass the table name as parameter and get all columns size of that table altered by column length + 50.

alter PROCEDURE dbo.testsp

  @tablename varchar(100)

AS

BEGIN

DECLARE 

@totalWeight TABLE (columnnames nvarchar(100));

INSERT INTO @totalWeight (columnnames)

SELECT name FROM sys.columns

 WHERE object_id = object_id(@tablename);

END

GO

This query returns your alteration statements in the last column. You can then run them through dynamic sql.

SELECT 

    tbl.name 'Table Name',

   c.name 'Column Name',

   t.name,  

   c.max_length 'Max Length in Bytes',

   c.precision ,

   c.scale ,

   c.is_nullable,

   q1.*,

       'alter table ['

       +tbl.name

       +'] alter column ['

       +c.name

       +'] '

       +t.name

       +'('+

       convert(nvarchar(1000),  CASE WHEN t.name IN ('nchar','nvarchar') THEN  c.max_length/2 ELSE c.max_length END +50)

       +') '

        + case c.is_nullable when 0 then ' not null' else ' null' end

    as 'Alteration statement'

FROM    

   sys.columns c

INNER JOIN 

   sys.types t ON c.user_type_id = t.user_type_id

inner join sys.tables tbl on tbl.object_id=c.object_id

CROSS APPLY

(

    select

        t.name +

        CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '('+



                    CASE WHEN c.max_length=-1 THEN 'MAX'



                        ELSE CONVERT(VARCHAR(4),



                                    CASE WHEN t.name IN ('nchar','nvarchar')



                                    THEN  c.max_length/2 ELSE c.max_length END )



                        END +')'



                WHEN t.name IN ('decimal','numeric')



                        THEN '('+ CONVERT(VARCHAR(4),c.precision)+','



                                + CONVERT(VARCHAR(4),c.Scale)+')'



                        ELSE '' END

        + case c.is_nullable when 0 then ' not null' else ' null' end

        as [DDL name before]

)q1

where t.name IN ('char', 'varchar','nchar','nvarchar') and c.max_length>-1

Run the following example of query, it creates a bunch of ALTER statements. Copy-paste the result into a new query window and run it again. Add other data types from sys.types if need.

SELECT 'ALTER TABLE [' + s.name + '].[' + t.name + '] ALTER COLUMN [' +

       c.name + '] ' + ty.name + '(' + CAST(c.max_length + 50 AS nvarchar(10)) + ') ' +

       CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ';'

FROM sys.tables t 

     INNER JOIN sys.columns c ON t.object_id = c.object_id

     INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

     INNER JOIN sys.types ty ON c.system_type_id = ty.system_type_id

WHERE t.type = 'U' AND

      c.system_type_id IN (167 /*varchar*/, 231 /*nvarchar*/)