Add a column if it doesn't exist to all tables?

You cannot use variables, like @tableName, in DDL. Besides, splinting the name into part and ignoring the schema can only result in bugs. You should just use the ''?'' replacement in the SQL batch parameter and rely on the MSforeachtable replacement:

EXEC sp_MSforeachtable 'if not exists (select * from sys.columns                where object_id = object_id(''?'')               and name = ''CreatedOn'') begin    ALTER TABLE ? ADD CreatedOn datetime NOT NULL DEFAULT getdate();end';

You'll need to mix in a bit of dynamic SQL. This should work:

EXEC sp_MSforeachtable '    declare @tblname varchar(255);    SET @tblname =  PARSENAME("?",1);    declare @sql nvarchar(1000);    if not exists (select column_name from INFORMATION_SCHEMA.columns                    where table_name = @tblname and column_name = ''CreatedOn'')     begin        set @sql = N''ALTER TABLE '' +  @tblname + N'' ADD CreatedOn datetime NOT NULL DEFAULT getdate();''        exec sp_executesql @sql    end'

DECLARE @Column VARCHAR(100) = 'Inserted_date'DECLARE @sql VARCHAR(max) = NULLSELECT @sql += ' ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + 'ADD' + @Column + 'datetime NOT NULL DEFAULT getdate()' + '; 'FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_TYPE = 'BASE TABLE'    AND TABLE_NAME IN (        SELECT DISTINCT NAME        FROM SYS.TABLES        WHERE type = 'U'            AND Object_id IN (                SELECT DISTINCT Object_id                FROM SYS.COLUMNS                WHERE NAME != @Column                )        )EXEC Sp_executesql @sql