how to make that column with default value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to make that column with default value

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-20 : 01:48:21

hello all,

i have a column createduser where i given default value in some of the table rest of the tables NULL.
so

like this i gave :


ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_CreatedByUserId]  DEFAULT ('1') FOR [CreatedByUserId]
GO

i need to make null value in that column which is having default value of 1 and rest of the tables having another default value should rermian normally

P.V.P.MOhan

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-20 : 02:13:15

is there any way to make the default value '1' to null and rest of the tables which having default value 23 should remain same...suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 03:02:10

First run this query
SELECT
t.Name,
c.Name,
dc.Name,
dc.definition
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
WHERE c.Name = 'col1' AND definition = '((1))'

--Script for DROP DEFAULT CONSTRAINT with value 1 for specific column


DECLARE @TableName VARCHAR(50), @columnName VARCHAR(60), @ConstraintName VARCHAR(100)
DECLARE @sql VARCHAR(8000)=''
DECLARE cur CURSOR FOR SELECT 
    TableName = t.Name,
    ColumnName = c.Name,
    ConstraintName = dc.Name
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
WHERE c.Name = 'col1' AND definition = '((1))';
open cur;
FETCH NEXT FROM cur INTO @TableName, @ColumnName, @ConstraintName;
WHILE(@@FETCH_STATUS = 0)
BEGIN
	SET @sql = 'ALTER TABLE '+@TableName +' DROP CONSTRAINT ' + @constraintName +';
	ALTER TABLE '+@TableName+' ALTER Column col1 INT NULL;'
	
	EXEC (@SQL);
	FETCH NEXT FROM cur INTO @TableName, @ColumnName, @ConstraintName	
END
Close cur;
DEALLOCATE cur;

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-20 : 04:48:28

hi chandu,

I executed above select query i got the list of default keys with '1' and in 2 nd you gave cursor to delete all them. So now i need to delete '1' and update with '2' while cursor is running it will make allow nulls into Yes .So how can i update column into 2 and allow nulls into not null...suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 04:50:03

Means you want to set default value as 2 ?

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 04:56:23

--to change default value where that column have 1


--First drop that constraint and then add default constraint with 2
WHILE(@@FETCH_STATUS = 0)
BEGIN
	SET @sql = 'ALTER TABLE '+@TableName +' DROP CONSTRAINT ' + @constraintName +';
	ALTER TABLE '+@TableName+' ADD CONSTRAINT '+  @constraintName + ' DEFAULT (''2'') FOR  @columnName;'
	
	EXEC (@SQL);
	FETCH NEXT FROM cur INTO @TableName, @ColumnName, @ConstraintName	
END

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-20 : 05:00:00

hi chandu,

yeah i need to need to put 2 in place of 1 and keep allow nulls NO for that column

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 05:04:55

Hi Mohan,

Did you have allow NULLs (= yes) for CreatedByUserId ?

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-20 : 05:11:29

no i wont allow nulls for CreatedByUserId and i executed your given query it giving error as

Incorrect syntax near '@columnName'

may be here getting error

ALTER TABLE '+@TableName+' ADD CONSTRAINT '+  @constraintName + ' DEFAULT (''2'') FOR  @columnName';

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 05:14:37

--This script is for drop constraint with value 1 and then add constraint with value as 2


DECLARE @TableName VARCHAR(50), @columnName VARCHAR(60), @ConstraintName VARCHAR(100), @sql VARCHAR(8000)=''

DECLARE cur CURSOR FOR SELECT 
    TableName = t.Name,
    ColumnName = c.Name,
    ConstraintName = dc.Name
FROM sys.tables t
INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_id
WHERE c.Name = 'col1' AND definition = '((1))';
open cur;
FETCH NEXT FROM cur INTO @TableName, @columnName, @ConstraintName;
--First drop that constraint and then add default constraint with 2
WHILE(@@FETCH_STATUS = 0)
BEGIN
	SET @sql = 'ALTER TABLE '+@TableName +' DROP CONSTRAINT ' + @constraintName +';
	ALTER TABLE '+@TableName+' ADD CONSTRAINT '+  @constraintName + ' DEFAULT (''2'') FOR  '+@columnName +';'
	
	EXEC (@SQL);
	FETCH NEXT FROM cur INTO @TableName, @columnName, @ConstraintName	
END
Close cur;
DEALLOCATE cur;

Note: If you want to make NULL property to NOT NULL, add the following code at the end of @sql variable
ALTER TABLE '+@TableName+' ALTER Column col1 INT NOT NULL;'

EDIT: here i have fixed that issue... just execute the main script
--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-03-20 : 05:27:53

yeah thanks chandu....it worked perfect for me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-20 : 05:39:33