SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to make that column with default value
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/20/2013 :  01:48:21  Show Profile  Reply with Quote
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

India
252 Posts

Posted - 03/20/2013 :  02:13:15  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/20/2013 :  03:02:10  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/20/2013 :  04:48:28  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/20/2013 :  04:50:03  Show Profile  Reply with Quote
Means you want to set default value as 2 ?

--
Chandu
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/20/2013 :  04:56:23  Show Profile  Reply with Quote
--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

Edited by - bandi on 03/20/2013 05:00:50
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/20/2013 :  05:00:00  Show Profile  Reply with Quote
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

Edited by - mohan123 on 03/20/2013 05:01:18
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/20/2013 :  05:04:55  Show Profile  Reply with Quote
Hi Mohan,

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




--
Chandu
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/20/2013 :  05:11:29  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/20/2013 :  05:14:37  Show Profile  Reply with Quote
--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

Edited by - bandi on 03/20/2013 05:16:14
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 03/20/2013 :  05:27:53  Show Profile  Reply with Quote
yeah thanks chandu....it worked perfect for me

P.V.P.MOhan
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2223 Posts

Posted - 03/20/2013 :  05:39:33  Show Profile  Reply with Quote
quote:
Originally posted by mohan123

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

P.V.P.MOhan


Welcome

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000