[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Modify nullability on SqlServer column

Posted on 2004-04-19
12
Medium Priority
?
999 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
10 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10864300
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
ID: 10864521
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
ID: 10864839
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 1

Author Comment

by:strongs120
ID: 10864874
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 152 total points
ID: 10864941
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
 
LVL 13

Expert Comment

by:danblake
ID: 10866805
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
ID: 10868091
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
ID: 10868482
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 148 total points
ID: 10887767
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
ID: 11026487
@strongs120,

Any update ?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 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