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.
enrique_aeoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You can't directly.

You need to:

1. create a new data type.
2. change all of the relevant fields to use that data type.
3. drop the old data type.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrique_aeoAuthor Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
As we said, you can't.

You can alter a field type in a database, but not a data type.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
This is the code to alter the columns to a new created user data type:
DECLARE @TABSCHEMA VARCHAR(128)
DECLARE @TABNAME VARCHAR(128)
DECLARE @COLNAME VARCHAR(128)

DECLARE @AlterCommand VARCHAR(4000)

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

-- 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

-- Drop the old type
DROP TYPE DNI

Open in new window

0
enrique_aeoAuthor Commented:
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'.
0
enrique_aeoAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Strange. It worked with me.
Try to change the following line to:
-- EXEC @AlterCommand
EXECUTE sp_executesql @AlterCommand

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You may need to change the variable to NVARCHAR:
DECLARE @AlterCommand NVARCHAR(4000)

Open in new window

So the command need to be like this:
SET @AlterCommand = N'ALTER TABLE ' + @TABSCHEMA + '.' + @TABNAME + ' ALTER COLUMN ' + @COLNAME + ' DNI'

Open in new window

0
enrique_aeoAuthor Commented:
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)
0
enrique_aeoAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.