Link to home
Start Free TrialLog in
Avatar of enrique_aeo
enrique_aeo

asked on

10774: modify datatype ALIAS

Buenos dias experts

I have a data type alias:
CREATE TYPE DNI
FROM varchar (6) NOT NULL;
which is used in various tables.

I need to expand its size 8. Please support.
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Since you cannot alter an user data type I'll recommend you to create a new one and alter the columns in each table.
Example:
CREATE TYPE DNI2
FROM varchar (8) NOT NULL;

ALTER TABLE MyTableName
ALTER COLUMN MyDNIColumn DNI2

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of enrique_aeo
enrique_aeo

ASKER

Long ago I asked the question and any of you sent me a code where if you could change the size, it was not a type alter, but I do remember being sought in the metadata. Unfortunately this code is in a expertx-exchange account you cancel it, in this new account I do not have it, please support.
As we said, you can't.

You can alter a field type in a database, but not a data type.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi experts:

i have this error:
Msg 2812, Level 16, State 62, Line 21
Could not find stored procedure 'ALTER TABLE dbo.mitabla ALTER COLUMN documento DNI2'.
Msg 2812, Level 16, State 62, Line 21
Could not find stored procedure 'ALTER TABLE dbo.mitabla2 ALTER COLUMN documento DNI2'.
this complete code

-- Cleaned
Drop table mitabla;
Drop table mitabla2;
DROP TYPE DNI;
DROP TYPE DNI2;
GO

-- Creando el tipo de dato y el objeto
CREATE TYPE DNI
FROM varchar(6) NOT NULL ;
go

create table mitabla
(
c1 int,
documento DNI
)

create table mitabla2
(
c1 int,
documento DNI
)

select * from sys.types where name LIKE 'DNI%'

-- Create the new type
CREATE TYPE DNI2
FROM varchar (8) NOT NULL;

--Asignando un nuevo ALIAS
DECLARE @TABSCHEMA VARCHAR(128)
DECLARE @TABNAME VARCHAR(128)
DECLARE @COLNAME VARCHAR(128)

DECLARE @AlterCommand VARCHAR(4000)

-- Find the columns that has the old type
DECLARE DNI_COLS CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DOMAIN_NAME = 'DNI'

OPEN DNI_COLS
FETCH NEXT FROM DNI_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME

WHILE @@FETCH_STATUS=0
      BEGIN
            -- Change the column to the new type
            SET @AlterCommand = 'ALTER TABLE ' + @TABSCHEMA + '.' + @TABNAME + ' ALTER COLUMN ' + @COLNAME + ' DNI2'
            EXEC @AlterCommand
            FETCH NEXT FROM DNI_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME
      END

CLOSE DNI_COLS
DEALLOCATE DNI_COLS
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I have this error:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.


--Asignando un nuevo ALIAS
DECLARE @TABSCHEMA VARCHAR(128)
DECLARE @TABNAME VARCHAR(128)
DECLARE @COLNAME VARCHAR(128)

DECLARE @AlterCommand VARCHAR(4000)

-- Find the columns that has the old type
DECLARE DNI_COLS CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DOMAIN_NAME = 'DNI'

OPEN DNI_COLS
FETCH NEXT FROM DNI_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME

WHILE @@FETCH_STATUS=0
      BEGIN
            -- Change the column to the new type
            SET @AlterCommand = 'ALTER TABLE ' + @TABSCHEMA + '.' + @TABNAME + ' ALTER COLUMN ' + @COLNAME + ' DNI2'
            EXECUTE sp_executesql @AlterCommand
            FETCH NEXT FROM DNI_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME
      END

CLOSE DNI_COLS
DEALLOCATE DNI_COLS

i have this version
Microsoft SQL Server 2012 - 11.0.2218.0 (Intel X86)
      Jun 12 2012 13:16:18
      Copyright (c) Microsoft Corporation
      Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
this work!!!

DECLARE @TABSCHEMA VARCHAR(128)
DECLARE @TABNAME VARCHAR(128)
DECLARE @COLNAME VARCHAR(128)

DECLARE @AlterCommand NVARCHAR(4000)

-- Find the columns that has the old type
DECLARE DNI_COLS CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DOMAIN_NAME = 'DNI'

OPEN DNI_COLS
FETCH NEXT FROM DNI_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME

WHILE @@FETCH_STATUS=0
      BEGIN
            -- Change the column to the new type
            SET @AlterCommand = 'ALTER TABLE ' + @TABSCHEMA + '.' + @TABNAME + ' ALTER COLUMN ' + @COLNAME + ' DNI2'
            EXECUTE sp_executesql @AlterCommand
            FETCH NEXT FROM DNI_COLS INTO @TABSCHEMA, @TABNAME, @COLNAME
      END

CLOSE DNI_COLS
DEALLOCATE DNI_COLS