hi

I'm working with SQL2000 and I want to know how can I define a "not null" 
feature for a column in my table via script and vice versa.
any help would be thanked.

alter table <table name> alter column <column name> <data type> not null



Show quoteHide quote

Hi

You will need to change any columns that currently have null in them to be a
different value, before you issue the ALTER TABLE statement e.g.

USE TEMPDB
GO

CREATE TABLE tbl_shouldnotbenull ( id int not null identity(1,1), col1
varchar(10) NULL)
GO

INSERT INTO tbl_shouldnotbenull ( col1 )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT NULL
UNION ALL SELECT 'D'
GO

SELECT * FROM tbl_shouldnotbenull
GO

ALTER TABLE tbl_shouldnotbenull ALTER COLUMN col1 varchar(10) NOT NULL
GO
/*
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'col1', table
'tempdb.dbo.tbl_shouldnotbenull'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
*/

UPDATE tbl_shouldnotbenull
SET col1 = 'C'
WHERE col1 IS NULL
GO

SELECT * FROM tbl_shouldnotbenull
GO

ALTER TABLE tbl_shouldnotbenull ALTER COLUMN col1 varchar(10) NOT NULL
GO


John

Show quoteHide quote

M
Just for fun , my two cents

CREATE TABLE t(c1 int null) ---allows NULL
insert t values(1)

SELECT c1 INTO tA FROM t
SELECT ISNULL(c1, 0) AS c1 INTO tB FROM t

EXEC sp_help tA --Does allow
EXEC sp_help tB -Does not allow






Show quoteHide quote

Dynamic SQL and column-values
SQL 2005 slower than 2000?
XP_CMDSHELL Problem
validate statement before execute with sp_executesql
Re: Connections List
Other ways to run this query?
Trying to do a blog join (entry columns, and # of comments) without success
How do I.....?
Assign A Flag Value
running a DTS package from an SP