Scott Klein and Joey D'Antoni provide an overview of the SQL Server 2016 features and enhancements, teasing up many of the features discussed in the other videos as well as covered in the training kit.
 

A simple free (AGPL) T-SQL Formatting Extension for SSMS and SSMS Express 2014-2016 and Visual Studio 2013-2015. 

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [t].[text]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_sql_text([r].sql_handle) [t];
GO

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_sql_text([r].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([r].[plan_handle]) [p];
GO

SELECT *
FROM sys.dm_exec_cached_plans;

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [cp].[objtype],
    [cp].[size_in_bytes],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_sql_text([r].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([r].[plan_handle]) [p]
    JOIN sys.dm_exec_cached_plans [cp]
    ON [r].[plan_handle] = [cp].[plan_handle];
GO

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [tq].[query_plan]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_text_query_plan([r].plan_handle, 0, -1) [tq];
GO

SELECT *
FROM sys.dm_exec_query_stats;
GO

SELECT
    [qs].[last_execution_time],
    [qs].[execution_count],
    [qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
    [qs].[max_logical_reads],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [qs].[execution_count] > 25
    OR [qs].[total_logical_reads] > 10000
ORDER BY [qs].[total_logical_reads]/[qs].[execution_count] DESC;
GO

SELECT *
FROM sys.dm_exec_function_stats;
GO

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace DbCop
{
    // Microsoft® SQL Server® 2012 Transact-SQL ScriptDom 
    // SQL Server 2012 managed parser, Supports SQL Server 2005+
    // SQLDom.msi (redist x86/x64)
    // http://www.microsoft.com/en-us/download/details.aspx?id=29065
    // X86: http://go.microsoft.com/fwlink/?LinkID=239634&clcid=0x409
    // X64: http://go.microsoft.com/fwlink/?LinkID=239635&clcid=0x409
    // Program Files\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.TransactSql.ScriptDom.dll

    class Program
    {
        static void Main()
        {
            const string tSql = @"
                -- select * in PROCEDURE
                CREATE PROCEDURE dbo.SelectStarTest
                AS
                SELECT * FROM dbo.tbl1
                go

                -- select * in PROCEDURE with TableVar
                Create PRocedure SelectAll
                AS
                Declare @X table(Id integer)
                Select * from @x
                go

                -- select * in PROCEDURE with ctex
                CREATE PROCEDURE dbo.SelectAllCte
                AS 
                WITH ctex
                AS (
                SELECT * FROM sys.objects
                )
                SELECT * FROM ctex
                go

                -- normal select *
                select * from tbl1; 
                select * from dbo.tbl2;
            ";

            IList<ParseError> errors;
            TSqlScript sqlFragment;
            using (var reader = new StringReader(tSql))
            {
                var parser = new TSql110Parser(initialQuotedIdentifiers: true);
                sqlFragment = (TSqlScript)parser.Parse(reader, out errors);
            }

            if (errors != null && errors.Any())
            {
                var sb = new StringBuilder();
                foreach (var error in errors)
                    sb.AppendLine(error.Message);

                throw new InvalidOperationException(sb.ToString());
            }

            var i = 0;
            foreach (var batch in sqlFragment.Batches)
            {
                Console.WriteLine("Batch: {0}, Statement(s): {1}", ++i, batch.Statements.Count);
                foreach (var statement in batch.Statements)
                {
                    processStatement(statement);
                }
                Console.WriteLine();
            }

            Console.WriteLine("\nPress a key...");
            Console.Read();
        }

        private static void processStatement(TSqlStatement statement)
        {
            var createProcedureStatement = statement as CreateProcedureStatement;
            if (createProcedureStatement != null)
            {
                var statementList = createProcedureStatement.StatementList;
                foreach (var procedureStatement in statementList.Statements)
                {
                    processStatement(procedureStatement);
                }
            }

            var selectStatement = statement as SelectStatement;
            if (selectStatement != null)
            {
                var query = selectStatement.QueryExpression;
                var selectElements = ((QuerySpecification)query).SelectElements;
                foreach (var selectElement in selectElements)
                {
                    var expression = selectElement as SelectStarExpression;
                    if (expression == null) continue;
                    Console.WriteLine(
                        "`Select *` detected @StartOffset:{0}, Line:{1}, T-SQL: {2}",
                        expression.StartOffset,
                        expression.StartLine,
                        statementToString(selectStatement));
                }
            }
        }

        private static string statementToString(TSqlFragment selectStatement)
        {
            var text = new StringBuilder();
            for (var i = selectStatement.FirstTokenIndex; i <= selectStatement.LastTokenIndex; i++)
            {
                text.Append(selectStatement.ScriptTokenStream[i].Text);
            }
            return text.ToString();
        }
    }
}

Batch: 1, Statement(s): 1
`Select *` detected @StartOffset:140, Line:5, T-SQL: SELECT * FROM dbo.tbl1

Batch: 2, Statement(s): 1
`Select *` detected @StartOffset:368, Line:12, T-SQL: Select * from @x

Batch: 3, Statement(s): 1
`Select *` detected @StartOffset:659, Line:22, T-SQL: WITH ctex
                AS (
                SELECT * FROM sys.objects
                )
                SELECT * FROM ctex

Batch: 4, Statement(s): 2
`Select *` detected @StartOffset:753, Line:26, T-SQL: select * from tbl1;
`Select *` detected @StartOffset:791, Line:27, T-SQL: select * from dbo.tbl2;

Following is a chart that shows licensing cost comparison between standard and Enterprise Edition of SQL Server 2012, 2016 and 2017.