Update smalldatetime fields in all tables

[partymong]

Hi All,
I want to update(increment) the year in all smalldatetime fields in all tables of a database. Is there an easy way of doing this?

There are 1000's of these fields. Can I use sysobjects to do this?

Any help would be appreciated.

Thanks in advance for your help!

Regards,

Peter

 

[JayKusch]

You would have to write an UPDATE statement to handle that. It would need to update each field that is a smalldatetime that needs to be addresses. Somewhat like ...

UPDATE MyTable

  SET  MyDate1 = DateAdd(yy,MyDate1,1),
       MyDate2 = DateAdd(yy,MyDate2,1),
       MyDate3 = DateAdd(yy,MyDate3,1)

Thanks

J. Kusch

 

[partymong]

Thanks Jay,
I need to dynamically update all the fields, in 1000's of tables, where I may not know the field or table name.

Can I get the datatype from syscolumns, i.e type 58, then update all the records in all the fields that have that datatype?

Thanks,

Regards,

P

 

[vongrunt]

I just found this in my ol' how-to-clean-legacy-cr@p SQL archive:

create procedure updateAllFields( @table_pattern sysname, @xtype tinyint, @expression varchar(255) )
as

declare @tablename sysname, @lasttable sysname, @colname sysname, @sql_update varchar(4000)
declare cur cursor for
	select A.name as tablename, B.name as colname
	from sysobjects A inner join syscolumns B on A.id=B.id
	where A.xtype='U' and B.xtype = @xtype and A.name like @table_pattern
	order by A.name, B.colorder
open cur
fetch next from cur into @tablename, @colname
while @@fetch_status = 0
begin
	set @lasttable=@tablename
	set @sql_update = ''
	while @@fetch_status = 0
	begin
		if @tablename <> @lasttable break

		if @sql_update <> '' set @sql_update = @sql_update + ', '
		set @sql_update = @sql_update + @colname + '=' + replace(@expression, '$$', @colname)

		fetch next from cur into @tablename, @colname
	end

	set @sql_update = 'UPDATE ' + @lasttable + ' SET ' + @sql_update
	print @sql_update
end

close cur
deallocate cur

GO

Then go to QA and try:

exec updateAllFields '%', 58, 'dateadd(yy, 1, $$)'

 

[partymong]

Hi Vongrunt,
Thankyou! That works great!...

I just changed the following line:-

set @sql_update = N'UPDATE ' + @lasttable + N' SET ' + @sql_update

Then added the following line:-

EXEC sp_executesql @sql_update

To execute the update statement


Thankyou for your help!

Regards

P