Solved

Modify nullability on SqlServer column

Posted on 2004-04-19
12
984 Views
Last Modified: 2012-06-21
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!!

0
Comment
Question by:strongs120
  • 4
  • 3
  • 2
  • +1
12 Comments
 
LVL 34

Expert Comment

by:arbert
Comment Utility
sp_help yourtablenamehere

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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Not to mention the fact that you have to create a brand new column and copy the data from the old column.
0
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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....
0
 
LVL 1

Author Comment

by:strongs120
Comment Utility
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!!
0
 
LVL 34

Accepted Solution

by:
arbert earned 38 total points
Comment Utility
Like I just said above--you either issue an alter statement, or you manually do it in Enterprise manager.  You cannot specify NULL or NOT NULL without also specifying the datatype--that's just the way SQL works...
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 13

Expert Comment

by:danblake
Comment Utility
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.
0
 
LVL 1

Author Comment

by:strongs120
Comment Utility
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..
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
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]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
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.
0
 
LVL 13

Assisted Solution

by:danblake
danblake earned 37 total points
Comment Utility
Ok, you want some deep stuff, here goes:
/*This is a test table*/
/****** Object:  Table [dbo].[tests2]    Script Date: 22/04/2004 12:24:45 ******/
SET ANSI_PADDING ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[tests2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tests2]
GO

CREATE TABLE [tests2] (
      [test_id] [int] NULL ,
      [test_name] [varchar] (5) NULL ,
      [qty] [int] NULL
) ON [PRIMARY]
GO


exec sp_addextendedproperty N'MS_Description', N'Sequence ID of the colour code', N'user', N'dbo', N'table', N'tests2', N'column', N'test_id'
GO
exec sp_addextendedproperty N'MS_Description', N'Colour of object/Name', N'user', N'dbo', N'table', N'tests2', N'column', N'test_name'
GO
exec sp_addextendedproperty N'MS_Description', N'Quantity occurred within system', N'user', N'dbo', N'table', N'tests2', N'column', N'qty'


GO

SET NOCOUNT ON
/*Make a column NOT NULL -- this is the really BAD way of doing things -- and presents problems as given below*/
/*I want to make column test_id now NOT NULL*/
update syscolumns
set typestat = typestat + 1 /*Change nullability based upon results if not allowing nulls +1 allows nulls*/
from sysobjects SO join syscolumns SC on
      SO.id = SC.id and
     SO.name = 'tests2'  /*Table Name*/
and SC.name = 'test_id'  /*Column Name*/
and (1 - [typestat] & 1) = 1 /*Test for nullability: &result = 0: allowing nulls, &result = 1:not allowing nulls*/
/*This should give 1 row affected*/

/*Check results: iscomputed column will give information on nullability*/
select * from syscolumns where name = 'test_id'


/*Check new schema on table -- that it is now not null for key test_id column*/
sp_help tests2

/*Check if we can insert a null value into the table -- this fails*/
insert into tests2 values
(NULL,'Fredy',2)

/*-----------------------------
insert into tests2 values
(NULL,'Freddy',2)
-----------------------------*/
-- Server: Msg 515, Level 16, State 2, Line 1
-- Cannot insert the value NULL into column 'test_id', table 'Northwind.dbo.tests2'; column does not allow nulls. INSERT fails.
-- The statement has been terminated.

/*What happens if we change a field from NOT NULL -> allows NULL..*/
update syscolumns
set typestat = typestat - 1 /*Change nullability based upon results if not allowing nulls +1 allows nulls*/
from sysobjects SO join syscolumns SC on
      SO.id = SC.id and
     SO.name = 'tests2'  /*Table Name*/
and SC.name = 'test_id'  /*Column Name*/
and (1 - [typestat] & 1) = 0
/*Again 1 row should be affected*/
/*Verify results*/
exec sp_help tests2

/*Check if we can insert a null value into the table -- this fails*/
insert into tests2 values
(NULL,'Fredy',2)


/*Please be aware that this message will occur due to this form of syscolumn manipulation even
after you move a column from NULLs allowed to NOT NULL -- A problem due to no prior NULL values being
inserted into the system...*/
/*-----------------------------
insert into tests2 values
(NULL,'Freddy',2)
-----------------------------*/
-- Server: Msg 515, Level 16, State 2, Line 1
-- Cannot insert the value NULL into column 'test_id', table 'Northwind.dbo.tests2'; column does not allow nulls. INSERT fails.
-- The statement has been terminated.
-- This cannot be changed until the table is fully recreated !...

/*What happens if there is NULL data in the table, and we change the nullability via the syscolumns/objects...*/
/*Lets start our example again..*/
if exists (select * from dbo.sysobjects where id = object_id(N'[tests2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tests2]
GO

CREATE TABLE [tests2] (
      [test_id] [int] NULL ,
      [test_name] [varchar] (5) NULL ,
      [qty] [int] NULL
) ON [PRIMARY]
GO

insert into tests2 values
(NULL,'Fredy',2)

update syscolumns
set typestat = typestat + 1 /*Change nullability based upon results if not allowing nulls +1 allows nulls*/
from sysobjects SO join syscolumns SC on
      SO.id = SC.id and
     SO.name = 'tests2'  /*Table Name*/
and SC.name = 'test_id'  /*Column Name*/
and (1 - [typestat] & 1) = 1

/*WARNING: Notice no errors were produced by running the modification of nullability
 and the field value has changed for test_id in the table !...*/
select * from tests2
-- The table now contains...
-- 0      Fredy      2

update syscolumns
set typestat = typestat - 1 /*Change nullability based upon results if not allowing nulls +1 allows nulls*/
from sysobjects SO join syscolumns SC on
      SO.id = SC.id and
     SO.name = 'tests2'  /*Table Name*/
and SC.name = 'test_id'  /*Column Name*/
and (1 - [typestat] & 1) = 0

select * from tests2
--> Data has been perm. changed and no checks on nullability have been run..*/
insert into tests2 values
(NULL,'Fredy',2)
/*We can now perform a null insert..*/
select * from tests2
/*We now have two rows with a default value of 0 instead of a new row with NULL*/



/*This includes only some of the reasons why WE do not recommend doing this......I have another 10 at least -- as I previously mentioned -- this is NOT a good idea -- its a really, really BAD idea.*/
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
@strongs120,

Any update ?
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now