March 24, 2010 at 3:06 pm
Ok, so I have a column called Created which is a dattime column and is in almost every table in my db that I have inherited. Some of the columns have default values of getdate() and others do not. I need to write a sql statement that will find all the columns that don't have a default value and alter that column to give it a default value. Where I am stuck is I don't know the sys tables well enough to get an effective query of all the tables that don't already have this constraint. I am running sql 08. Below is what I have thus far.
DECLARE @TableName varchar(max)
DECLARE setdefault_cursor CURSOR FOR
SELECT name FROM sysobjects WHERE id IN ( SELECT id FROM syscolumns WHERE name = 'Created')
and Sysobjects.name not like ('sys%')
OPEN setdefault_cursor
FETCH next from setdefault_cursor into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN setdefault
exec('ALTER TABLE '+@TableName+' ADD DEFAULT getdate() FOR Created')
exec('ALTER TABLE '+@TableName+' ALTER COLUMN Modified datetime null')
if @@ERROR = 0
BEGIN
commit tran setdefault
END
else
BEGIN
rollback tran setdefault
END
FETCH next from setdefault_cursor into @TableName
END
CLOSE setdefault_cursor
DEALLOCATE setdefault_cursor
March 24, 2010 at 3:28 pm
since this is a one time update, i would not create the defaults inside a cursor;
i would just generate the sql, and run it once.
this gets similar results, but without a cursor:
select
sys.objects.name as ColumnName,
'ALTER TABLE ' + sys.objects.name + ' ADD DEFAULT getdate() FOR' + sys.columns.name + ')'
from sys.objects
inner join sys.columns on sys.objects.object_id = sys.columns.object_id
where sys.columns.name = 'Created'
and sys.columns.default_object_id = 0
and sys.objects .type_desc = 'USER_TABLE'
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply