adams
adams

Reputation: 301

Pass table name as parameter

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

Upvotes: 1

Views: 563

Answers (2)

serge
serge

Reputation: 1022

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*/)

Upvotes: 0

George Menoutis
George Menoutis

Reputation: 7240

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

Upvotes: 2

Related Questions