strongs120
asked on
Modify nullability on SqlServer column
How can I modify a column attirubute from NOT NULL to "Allow Nulls".
I do NOT want to use this format:
alter table <dbo.TABLE_NAME> alter column <COLUMN_NAME> <data_type> NULL
because I dont know the data-type!!
isn't there some Sp_ that I can do this with?
or something like alter table... modify column xxx NULL
Like Oracle!!
I do NOT want to use this format:
alter table <dbo.TABLE_NAME> alter column <COLUMN_NAME> <data_type> NULL
because I dont know the data-type!!
isn't there some Sp_ that I can do this with?
or something like alter table... modify column xxx NULL
Like Oracle!!
Not to mention the fact that you have to create a brand new column and copy the data from the old column.
I'm backwards--you can alter the column to allow nulls with existing data--not the other way around....No additional column is needed--you still have to use your alter statement or enterprise manager to do it....
ASKER
Sorry if I wasn't clear.
It's not that I CANT know what the datatype is it's that I don't want to.
I need to **PROGRAMMATICALLY** change the nullability so I just want to refer to the table and column name.
Thanks!!
It's not that I CANT know what the datatype is it's that I don't want to.
I need to **PROGRAMMATICALLY** change the nullability so I just want to refer to the table and column name.
Thanks!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Strongs120,
Oracle and SQL Server are different like arbert has said.
Arbert has presented both viable options based upon how SQL Server works.
There is no way to do it the SAME way as Oracle -- neither forcing it to work the same way as oracle will happen either.
(They are built different, run differently, and have their little neuances this is one of them !
Try finding a rowid in SQL Server for example ( I know Oracle has one !) !)
The only alternative would be to read from a source to autopopulate the data-type so you don't have to manually specify this, but that is dynamic sql. You can always suggest to MS that they make their system work the SAME way as Oracle... .(You might be met with some odd looks though ;-) )
Rgds,
Dan.
Oracle and SQL Server are different like arbert has said.
Arbert has presented both viable options based upon how SQL Server works.
There is no way to do it the SAME way as Oracle -- neither forcing it to work the same way as oracle will happen either.
(They are built different, run differently, and have their little neuances this is one of them !
Try finding a rowid in SQL Server for example ( I know Oracle has one !) !)
The only alternative would be to read from a source to autopopulate the data-type so you don't have to manually specify this, but that is dynamic sql. You can always suggest to MS that they make their system work the SAME way as Oracle... .(You might be met with some odd looks though ;-) )
Rgds,
Dan.
ASKER
I know they're diff.
I'm looking for some "in depth knowledge" here..
For example seems that none of you know that you cannot do what I want in Enterprise Manager.. you get a ODBC cursor error.
I was also told in the past that one could not add a comment on a table or a column (.. like Oracle.. :-) )but I found later I could using an extended stored procedure.... that's the kind of info I'm looking for..
Thanks for all the time thus far..
I'm looking for some "in depth knowledge" here..
For example seems that none of you know that you cannot do what I want in Enterprise Manager.. you get a ODBC cursor error.
I was also told in the past that one could not add a comment on a table or a column (.. like Oracle.. :-) )but I found later I could using an extended stored procedure.... that's the kind of info I'm looking for..
Thanks for all the time thus far..
This is the most "in depth knowledge" you will get here at this time on this particular subject -- I believe the error you are experiencing must be a problem in just your enviroment.
For example seems that none of you know that you cannot do what I want in Enterprise Manager..
(You can do it EM, but it is laborious and prone to human error -- you should not get an ODBC cursor error !, I do know the scripts that are produced have you every looked at them ? -- I have done this kind of thing before in EM... So it seems you are incorrect here.)
You want to test this go ahead, heres a demo:
/*First lets create our test table !*/
/****** Object: Table [dbo].[BigGUIDTest] Script Date: 20/04/2004 14:13:00 ******/
SET ANSI_PADDING ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[BigGUIDTest]'
drop table [BigGUIDTest]
GO
CREATE TABLE [BigGUIDTest] (
[t1] [uniqueidentifier] NOT NULL ,
[Company] [varchar] (4) NOT NULL
) ON [PRIMARY]
GO
/*This works...*/
alter table [BigGUIDTest] alter column [t1] [uniqueidentifier] null
alter table [BigGUIDTest] alter column [t1] [uniqueidentifier] not null
/*This will return an error..*/
alter table [BigGUIDTest] alter column [t1] null
/*Error is: Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'null'.*/
/*I perform the modification in EM, this is the script that is produced to move a field from NOT NULL to NULL
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_BigGUIDTest
(
t1 uniqueidentifier NULL,
Company varchar(4) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.BigGUIDTest)
EXEC('INSERT INTO dbo.Tmp_BigGUIDTest (t1, Company)
SELECT t1, Company FROM dbo.BigGUIDTest TABLOCKX')
GO
DROP TABLE dbo.BigGUIDTest
GO
EXECUTE sp_rename N'dbo.Tmp_BigGUIDTest', N'BigGUIDTest', 'OBJECT'
GO
COMMIT*/
As arbert has mentioned it drops the existing table.
For example seems that none of you know that you cannot do what I want in Enterprise Manager..
(You can do it EM, but it is laborious and prone to human error -- you should not get an ODBC cursor error !, I do know the scripts that are produced have you every looked at them ? -- I have done this kind of thing before in EM... So it seems you are incorrect here.)
You want to test this go ahead, heres a demo:
/*First lets create our test table !*/
/****** Object: Table [dbo].[BigGUIDTest] Script Date: 20/04/2004 14:13:00 ******/
SET ANSI_PADDING ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[BigGUIDTest]'
drop table [BigGUIDTest]
GO
CREATE TABLE [BigGUIDTest] (
[t1] [uniqueidentifier] NOT NULL ,
[Company] [varchar] (4) NOT NULL
) ON [PRIMARY]
GO
/*This works...*/
alter table [BigGUIDTest] alter column [t1] [uniqueidentifier] null
alter table [BigGUIDTest] alter column [t1] [uniqueidentifier] not null
/*This will return an error..*/
alter table [BigGUIDTest] alter column [t1] null
/*Error is: Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'null'.*/
/*I perform the modification in EM, this is the script that is produced to move a field from NOT NULL to NULL
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_BigGUIDTest
(
t1 uniqueidentifier NULL,
Company varchar(4) NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.BigGUIDTest)
EXEC('INSERT INTO dbo.Tmp_BigGUIDTest (t1, Company)
SELECT t1, Company FROM dbo.BigGUIDTest TABLOCKX')
GO
DROP TABLE dbo.BigGUIDTest
GO
EXECUTE sp_rename N'dbo.Tmp_BigGUIDTest', N'BigGUIDTest', 'OBJECT'
GO
COMMIT*/
As arbert has mentioned it drops the existing table.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@strongs120,
Any update ?
Any update ?
will show you the datatypes of the table...
Also, you won't be able to change a column to "Allow Nulls" if there is existing data and no default value defined.