How to alter an used alias type?

If I created an alias type as followed,
CREATE TYPE SSN
FROM varchar(11) NOT NULL
For some reasons, I would like to modify this alias type to varchar(20) not
null,
How to do it?
--Frank, using SQL2005devYou can to drop it and re-create it. But you cannot drop it as long as it is
being used, so...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Frank Lee" <Reply@.to.newsgroup> wrote in message news:OI8c$X$LGHA.140@.TK2MSFTNGP12.phx.gbl
..
> If I created an alias type as followed,
> CREATE TYPE SSN
> FROM varchar(11) NOT NULL
> For some reasons, I would like to modify this alias type to varchar(20) no
t
> null,
> How to do it?
> --Frank, using SQL2005dev
>|||Frank Lee (Reply@.to.newsgroup) writes:
> If I created an alias type as followed,
> CREATE TYPE SSN
> FROM varchar(11) NOT NULL
> For some reasons, I would like to modify this alias type to varchar(20)
> not null,
> How to do it?
That's a bit of work! Rename the type (sp_rename), create the new
definition. Run ALTER TABLE ALTER COLUMN on all tables that uses
the type, and recreate all procedures that uses the type as a parameter.
Finally you can drop the old definition.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se>
'?:Xns9769A5B38E43Yazorman@.127.0.0.1...
> Frank Lee (Reply@.to.newsgroup) writes:
> That's a bit of work! Rename the type (sp_rename), create the new
> definition. Run ALTER TABLE ALTER COLUMN on all tables that uses
> the type, and recreate all procedures that uses the type as a parameter.
> Finally you can drop the old definition.
>
Oh My god!
I would like to use alias type because I thought that if one day I want to
alter the difinition of alias type, it would be more easy as it has central
difinition. However, it's not really. It's more complex. :-(
Anyway, if it's the only way to alter the used alias type, I accept.
--Frank|||Frank Lee (Reply@.to.newsgroup) writes:
> I would like to use alias type because I thought that if one day I want
> to alter the difinition of alias type, it would be more easy as it has
> central difinition. However, it's not really. It's more complex. :-(
And it's maybe just as well. For a small database with little data in it
a single ALTER TYPE may be harmless. But for a widely used type in a 1TB
database it could send the database offline for a w.
Changing table schema in a large database requires careful planning, and
even more so if you change a type.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.se>
'?:Xns97695FE4BC283Yazorman@.127.0.0.1...
> And it's maybe just as well. For a small database with little data in it
> a single ALTER TYPE may be harmless. But for a widely used type in a 1TB
> database it could send the database offline for a w.
> Changing table schema in a large database requires careful planning, and
> even more so if you change a type.
>
Ya, you are right.sql