Home / Questions / 328485
Accepted
carallot
Asked: 2020-02-12 06:43:39 +0800 CST 2020-02-12 06:43:39 +0800 CST

Mass property change of a field

  • 772

I would like to make a query or procedure that would look for all the fields with a specific name in a database and if the size of the field was less than N , it would establish a new value. That is, set all named fields articuloof a database Varchar(8)to Varchar(15). Can someone guide me on how to do it? Thank you very much.

It's MSSQL Server, sorry for not specifying it, I thought Transact-sql in the tags was enough, sorry.

sql-server

1 Answers

  1. Best Answer

    Here you have a basic proposal, which serves as a solution to what you ask for and that you can modify as appropriate or put in a procedure passing parameters.

    We create a cursor that through the system tables sys.objects, sys.columns, sys.schemasand sys.typesand WHEREreturn us the list of matching fields and their properties (schema, table name, field name and type). In this case only the columns whose name is 'article'.

    For each of them, it mounts a string with the statement ALTERand executes it. Each of them would be of the type:

    ALTER TABLE [dbo].[Articulos] ALTER COLUMN [Articulo] VARCHAR (15)

    It only takes user tables ( where a.type='u'). It only takes fields of type varchary nvarchar, whose length is 8, and transforms it to length 15.

    declare @schema varchar(250)
declare @Tabla varchar(250)
declare @Campo varchar(250)
declare @tipo varchar(20)
DECLARE prod_cursor CURSOR FAST_FORWARD FOR 
select d.name, a.name, b.name, c.name
from sys.objects a
inner join sys.columns b on a.object_id = b.object_id 
inner join sys.systypes c on b.system_type_id = c.xtype
inner join sys.schemas d on a.schema_id = d.schema_id 
where a.type='u'
and c.name in ('varchar', 'nvarchar')
and b.name = 'articulo'
and b.max_length = 8

OPEN prod_cursor

FETCH NEXT FROM prod_cursor 
INTO @schema, @Tabla, @Campo, @tipo

WHILE @@FETCH_STATUS = 0
BEGIN

    declare @sql varchar(500)
    set @sql = 'ALTER TABLE [' +  @schema + '].[' + @Tabla + '] ALTER COLUMN [ ' + @Campo + '] ' + @tipo + ' (15)'
    exec(@sql)

    FETCH NEXT FROM prod_cursor 
    INTO @schema, @Tabla, @Campo, @tipo
END 
CLOSE prod_cursor
DEALLOCATE prod_cursor

    EDIT When a field associated with a Constraint is found, it will give an error. This Constraint can be from the same table or from another table that refers to the one being modified. Therefore you can temporarily disable these restrictions by

    EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

    then turn them back on

    EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
    • 1