How to change or alter a user defined data type?

Hi all,
I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
and it's ok for the table/columns but I can NOT drop my user defined data
type using sp_droptype because it's already used in some Stored procs :(
Is there a solution WITHOUT a "delete & re-create" of these stored procs ?
Lilian.This example should demonstrate how to swap out the type. Note that the
rename affects tables, but not stored procs (so you won't have to recompile
your procedures, just hope they don't get called during the brief period
where the type doesn't exist).
-- originally, we create a datatype called fax,
-- and made it able to accept 32 characters
EXEC sp_addType 'fax', 'VARCHAR(32)'
GO
-- so we created a table that uses this datatype
CREATE TABLE dbo.foobar0
(
[fax] fax
)
GO
-- and a simple stored procedure as well
CREATE PROC dbo.foobar1
@.fax fax
AS
BEGIN
DECLARE @.fax2 fax
END
GO
-- now, we realize that fax numbers on
-- jupiter can contain 64 characters, so we
-- have to increase the size of the UDT
-- but we can't just alter the type, and we
-- can't drop it and re-create it either, without
-- altering or dropping / re-creating tables,
-- stored procedures, etc. that use the UDT
-- but we can use a little rename trick to
-- create an interim UDT that meets our needs
-- first, rename the existing UDT. This will
-- change table definitions but it will not alter
-- the text for a procedure / function
EXEC sp_rename 'fax', 'oldfax', 'USERDATATYPE'
GO
-- let's just make sure that it affected our table
-- but not our procedure:
EXEC sp_help foobar0
EXEC sp_helptext foobar1
GO
-- okay, now let's add the larger fax UDT back
-- into the system
EXEC sp_addtype 'fax', 'VARCHAR(64)'
GO
-- alter any tables / views that reference the old
-- fax UDT
ALTER TABLE foobar0 ALTER COLUMN [fax] fax
GO
-- now we should be able to drop the interim
-- UDT:
EXEC sp_droptype 'oldfax'
GO
-- (now let's clean up my silly example)
DROP TABLE dbo.foobar0
DROP PROCEDURE dbo.foobar1
EXEC sp_droptype 'fax'
GO
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:#pSUDo9PDHA.2036@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
> and it's ok for the table/columns but I can NOT drop my user defined data
> type using sp_droptype because it's already used in some Stored procs :(
> Is there a solution WITHOUT a "delete & re-create" of these stored procs
?
> Lilian.
>|||Sorry, you will have to recompile stored procedures that use the datatype as
in/out parameters, but not those that only use the type for local
variables...|||Very interesting...but do you "recompile" a stored proc by script ?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:elXMq29PDHA.2676@.TK2MSFTNGP10.phx.gbl...
> Sorry, you will have to recompile stored procedures that use the datatype
as
> in/out parameters, but not those that only use the type for local
> variables...
>

How to change identity to an existing column?

Hi

can anybody tell , Is it possible to alter existing column to add IDENTITY?

Thanks in advance

Yes

It is possible to add Identity Column to a table already have some data. BUT there is no such a command to alter a column to be an IDENTITY Column. You have to go through the following steps.

1. Create a same table with the IDENTITY COLUMN. but with diffrent Name
2. Move all data to the New Table, with IDENTITY INSERT ON option
3. Drop All constraints from the OLD table
4. Drop the OLD table
5. Rename the new table as the OLD table
6. Attach all constraints to the new table ...

Hope that it will help

|||Thank u akbar

How to change COLLATION NAME for the database?

Is there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.Originally posted by zuhara
Is there a way (besides "ALTER DATABASE COLLATE ...") to change collation name for the whole database? I tried to use the "ALTER DATABASE" command, but it didn't work. And I wouldn't like to run "ALTER COLUMN" commands for over 100 tables.

Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:

The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.

Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.

All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.

The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.
After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft SQL Server 2000 you must:

Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.

Export all of your data using a tool such as bulk copy.

Drop all of the user databases.

Rebuild the master database specifying the new collation.

Create all of the databases and all of the objects in them.

Import all of your data.|||If your database has a reasonably conventional design you are welcome to try my script generator

http://www.dbforums.com/showthread.php?threadid=926370&highlight=collation

which will generate a t-sql script for manually tearing down and rebuilding all collatable columns in a dabase.

How to change a field's type?

Hi,
How could I change the field type through T-SQL?
I have tried Using the ALTER:
ALTER TABLE tblName ALTER COLUMN myID int
It didn't work...

And also, how could you rename a fieldname?
cheers,
Paul June A. DomagThe syntax you have listed is correct, although you may need to specify NULL/NOT NULL:
Alter Table dbo.SomeTable
Alter Column SomeField Varchar(101) NULL
For the rename funtion, use sp_rename you can find the syntax in books online...
Edit: If you are getting an error, please post the details of the error so we can dig a little deeper...
|||Hi,

I tried placing a NOT NULL statement and still it doesn't work...

ALTER TABLE myTable ALTER COLUMN myCol int NOT NULL

What is wrong with my statement?
BTW, the myCol field is numeric (I don't know if it matters)

ALSO, is there another alternative in renaming the field? Coz Im trying to achieve this in SQLServerCE and there is no sp_rename function in SQLServerCE...
cheers,
Paul June A. Domag|||Hey Paul,
I'm not familiar with SQL Server CE, so not sure if the T-SQL is any different.
In the absence of sp_rename, the following should work
-- Create the renamed column
Alter Table myTable with Check Add newColname varchar(100) not null
Go
-- copy the data to the new column
Update myTable set newColumn = oldColumn
Go
-- delete the old column
Alter Table myTable Drop Column oldColumn
Go
For the Alter statement that isn't working, can you please post the error message that you receive?
Thanks,
Tyler|||Hi,
Whew! Why didn't I think about that? tsk....
Thank you very much! You really made my day...

Here's the error message that I keep on getting in SQLServerCE:

FAILED: ALTER TABLE CustomerAddresses
ALTER COLUMN IDNew int
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 7
Param. 2: 0
Param. 3: IDNew
Param. 4:
Param. 5:

Cheers,
Paul June A. Domag|||Hi,
In addition to that, how could I modify a field to make it an IDENTITY column?
I tried:
ALTER TABLE mytable ALTER COLUMN IDNew IDENTITY

It generates an error saying that my field could not be made identity column after it was made...

BTW, the column that im trying to rename is an IDENTITY column, so by using the solution that you have provided, I need to restore the Autonumbering function on my field...
cheers,
Paul June A. Domag|||Hi Paul,
I think that Identity may be the issue. I suppose for an easy answer you could use the same method to change the column type, i.e., create a new column, copy the values and delete the orginal column.
To restore the numbering just do it when you declare the column:
Alter Table SomeTable
With Check Add
MyColumn INT IDENTITY (x, y) NOT NULL
Where x is the SEED value (i.e., what the next number will start at) and y is the INCREMENT (usually 1 to increment by one)
So if you where to declare the column as IDENTITY (1500, 1) the next record inserted would automatically have a value of 1500, etc.
Let me know if that makes sense.
Thanks!
Tyler|||Hi,
Sorry, but it didn't work. It seems that Identity columns couldn't be updated...
I used your steps:

ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
UPDATE t SET myNewCol = ID ' Error Here
ALTER TABLE t DROP COLUMN ID

BTW, my ID field (w/c is the identity column) is not entirely sequential. What I mean is, there have been some deletions and the numbering is somewhat jumbled. eg 1,2,3,8,11,13,21,22,25...

In this case I can't even use the new Identity Column that I would create coz the newly created identity would be sequential and thus would break my relationship with other tables...

Any other bright ideas?
cheers,
Paul June A. Domag|||Hi Paul
I think you cannot update an Identity column unless you set the identity insert on
ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
go
SET IDENTITY_INSERT t ON
UPDATE t SET myNewCol = ID ' Error Here
SET IDENTITY_INSERT t OFF
ALTER TABLE t DROP COLUMN ID
GO|||Hi,
Im currently using SQLServerCE and I doubt that this function/variable exists... Im trying to do this purely in SQL Statement...
Any Other Bright Ideas?
cheers,
Paul June A. Domag|||With a column set to Identity you cannot insert values unless your basically turn off the Identity feature, which is done as Eisa states.
If this doesn't work in SQL CE then we'll need to find a SQL CE Expert to answer this question.
Have you tried turning off the IDENTITY_INSERT? Also, make sure you insert GO statements after each line.
|||Hi,
Sad to say, it didn't work. I searched the BOL of SQLServerCE and the IDENTITY_INSERT switch isn't available. Guess, I'll have to find other means to solve this problem...

Thanks a lot Tyler Free and Eisa for your ideas and comments...
cheers,
Paul June A. Domag