Reputation: 301
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
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
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