Update table date column from default value to NULL


ALTER PROCEDURE [dbo].[Update_dt_value]
@TableName NVARCHAR(128)
AS
BEGIN
SET NOCOUNT ON;

Declare @Sql nvarchar(max)=”

;with mycte as (SELECT [TABLE_NAME], [COLUMN_NAME], ‘update ‘ + QUOTENAME([TABLE_NAME])
+’ Set ‘+ [COLUMN_NAME] +’= null WHERE ‘ + [COLUMN_NAME] +’=”1900-01-01”’ as sqlstr

FROM [mydb1].[INFORMATION_SCHEMA].[COLUMNS]
where data_type=’date’ and [TABLE_NAME]=@TableName
)

SELECT @sql= ( SELECT Cast(t2.sqlstr as varchar(2000)) +’;’
FROM mycte t2
WHERE t2.[TABLE_NAME] = t1.[TABLE_NAME]

FOR XML PATH(”), TYPE).value(‘.’, ‘varchar(max)’)
FROM mycte t1
Group by [TABLE_NAME]
;

EXECUTE sp_executesql @Sql
–print @Sql

END

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s