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.solike 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 normallyP.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 meP.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.definitionFROM sys.tables tINNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_idINNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_idWHERE c.Name = 'col1' AND definition = '((1))'--Script for DROP DEFAULT CONSTRAINT with value 1 for specific columnDECLARE @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.NameFROM sys.tables tINNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_idINNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_idWHERE 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 ENDClose 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 meP.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 2WHILE(@@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 columnP.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 2DECLARE @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.NameFROM sys.tables tINNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_idINNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND c.column_id = dc.parent_column_idWHERE 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 2WHILE(@@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 ENDClose cur;DEALLOCATE cur; Note: If you want to make NULL property to NOT NULL, add the following code at the end of @sql variableALTER 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 meP.V.P.MOhan |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-20 : 05:39:33
|
quote: Originally posted by mohan123 yeah thanks chandu....it worked perfect for meP.V.P.MOhan
Welcome--Chandu |
|
|
|