DECLARE @sql VARCHAR(5000);
WITH T
AS (SELECT SchemaName = c.TABLE_SCHEMA,TableName = c.TABLE_NAME,ColumnName = c.COLUMN_NAME,DataType = DATA_TYPE,sql = 'update ' + c.TABLE_NAME + ' set ' + c.COLUMN_NAME + ' = ''2017-11-20''' -----------執行語句FROM INFORMATION_SCHEMA.COLUMNS cINNER JOIN INFORMATION_SCHEMA.TABLES tON c.TABLE_NAME = t.TABLE_NAMEAND c.TABLE_SCHEMA = t.TABLE_SCHEMAAND t.TABLE_TYPE = 'BASE TABLE'AND c.COLUMN_NAME LIKE '%CreateTime%' ---列名AND c.DATA_TYPE = 'datetime'),T2
AS (SELECT STUFF((SELECT '; ' + A.sql FROM T A FOR XML PATH('')),1,1,'') AS sqls)
SELECT @sql = sqls
FROM T2;
PRINT @sql;
EXEC (@sql);