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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
You can alter a field type in a database, but not a data type.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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'.
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'.
ASKER
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
-- 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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)
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)
ASKER
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
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
Good :)
Example:
Open in new window