How to get list of tables and column in mssql. How to update the field value of specific's column in mssql?

How to update the field value of specific's column in SQL Server using Query in one shot?

This query changing all datetime columns in db. (Add 1 week). if you want you can change specific column data
.

USE [YourDatabaseName] GO /****** Object: StoredProcedure [dbo].[AddWeekAllDatetimeColumn] Script Date: 20.11.2015 17:14:31 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <İsa ATASEVEN> -- Create date: <19.11.2015> -- Description: <Add 1 week all datatype=datetime column> -- ============================================= CREATE PROCEDURE [dbo].[AddWeekAllDatetimeColumn] AS BEGIN DECLARE @ColumnName nvarchar(max) DECLARE @TableName nvarchar(max) DECLARE @UpdateScript nvarchar(max) -------------------------------------------------------- DECLARE @MyCursor CURSOR SET @MyCursor = CURSOR FAST_FORWARD FOR SELECT c.name 'Column Name', tt.TABLE_NAME FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id INNER JOIN information_schema.tables tt ON c.object_id = OBJECT_ID(tt.TABLE_NAME) LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id where t.name like '%datetime%' OPEN @MyCursor FETCH NEXT FROM @MyCursor INTO @ColumnName,@TableName WHILE @@FETCH_STATUS = 0 BEGIN set @UpdateScript='update '+ @TableName+ ' set ' +@ColumnName+'=DateAdd(week,1,'+@ColumnName+')' execute (@UpdateScript) FETCH NEXT FROM @MyCursor INTO @ColumnName,@TableName END CLOSE @MyCursor DEALLOCATE @MyCursor END