In this article,  We are going to CREATE/ ALTER/ DROP a table and observe transaction log records created for those DDL statements.

Our focus is to observe schema level impacts in lower level.

1. CREATE TABLE

Lets create a simple table named tEmployee in a sample database EXPLORE_TLOG

USE MASTER
GO

-- Drop this database if already exists
DROP DATABASE EXPLORE_TLOG
GO

-- Create a database named Explore_TLog
CREATE DATABASE EXPLORE_TLOG
GO

USE EXPLORE_TLOG
GO

-- Create table is covered with a transaction name CREATE_TABLE1

BEGIN TRAN CREATE_TABLE1
CREATE TABLE tEmployee(intRoll int, strName char(20), strDept char(20))
COMMIT TRAN CREATE_TABLE1
GO

Here is the query to fetch t-log records generated for the transaction name CREATE_TABLE1.

SELECT
[spid],
[Current LSN],
[Operation],
[Context],
[AllocUnitName],
[Page ID],
[Slot ID]
FROM fn_dblog(null,null)
WHERE [Transaction ID] IN(
 SELECT [Transaction ID] FROM fn_dblog(null,null)
 WHERE [Transaction Name] = 'CREATE_TABLE1')

Note:
1. In the above query, first column spid tells which spid fired this CREATE TABLE statemet. We will see its use in detail later
2. Above create table statement has generated 27 t-log records (below table).

CREATE TABLE t-log records - Output (read description column):

In the above output, you can see lots of system object names in AlloUnitName column.
as I mentioned in previous article, these objects are protected and you can't directly query them.
If you want to query them you have to login as DAC (ADMIN:<InstanceName>)

here is list of protected system objects and equivalent documeted system views provided to Us. 
Note: this is just a high level  similaritie observed by me. you can observe lots of difference in column count and naming convention etc.

If you are new to querying system views, run two below queries and observe the relationship among the system views.

-- sys.objects and sys.columns
SELECT so.object_id, so.name ObjectName, sc.column_id, sc.name column_name
FROM sys.objects so
 inner join sys.columns sc on so.object_id = sc.object_id
where so.name = 'tEmployee'

-- Object - Partitions - Allocation Units
SELECT
so.name,
so.object_id,
si.type_desc,
sp.index_id,
sp.partition_id,
sp.hobt_id,
sa.container_id,
internals.total_pages,
internals.used_pages,
internals.data_pages,
first_page,
root_page,
first_iam_page
FROM sys.objects so
 inner join sys.indexes si on so.object_id = si.object_id
 inner join sys.partitions sp on so.object_id = sp.object_id
 inner join sys.allocation_units sa on sa.container_id = sp.hobt_id
 inner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_id
WHERE so.object_id = object_id('temployee')

2. ALTER TABLE

Now lets drop a column in tExample table and observe t-log records generated.

BEGIN TRAN ALTER_TABLE1
ALTER TABLE tEmployee DROP Column strDept
COMMIT TRAN ALTER_TABLE1
GO

Now fetch t-log records generated for this transaction

SELECT
[spid],
[Current LSN],
[Operation],
[Context],
[AllocUnitName],
[Page ID],
[Slot ID]
FROM fn_dblog(null,null)
WHERE [Transaction ID] IN(
 SELECT [Transaction ID] FROM fn_dblog(null,null)
 WHERE [Transaction Name] = 'ALTER_TABLE1')

As you can expect, there will be an UPDATE in sys.sysschobjs and DELETE in sys.syscolpars

ALTER TABLE - output:

Observation:
1. Dropping a column in a simple user table makes little changes in records inserted for CREATE TABLE in system objects.

2. There is an MODIFY_ROW operation in sys.sysschobjs (sysobjects) and some MARK_AS_GHOST in column level system objects such as sys.sysrscols and sys.syscolpars (syscolumns).

3. LOCK_XACT  - Schema lock.

3. DROP TABLE

Lets drop this tEmployee table and observe t-log records generated by DROP TABLE statement

BEGIN TRAN DROP_TABLE1
DROP TABLE tEmployee
COMMIT TRAN DROP_TABLE1
GO

here is the query to list T-log records for DROP TABLE statement:

SELECT
[spid],
[Current LSN],
[Operation],
[Context],
[AllocUnitName],
[Page ID],
[Slot ID]
FROM fn_dblog(null,null)
WHERE [Transaction ID] IN(
 SELECT [Transaction ID] FROM fn_dblog(null,null)
 WHERE [Transaction Name] = 'DROP_TABLE1')

Output:

Observations:

1. As you can expect, DROP TABLE marks records as *GHOST* (delete) in some system objects which are INSERTed when a table is created.

2. Single delete in sys.sysschobjs performs 4 seperate deletes internally. one for deleting cluster index record and 3 deletes for 3 non cluster index records (for example: clst, nc1, nc2, nc3 etc)

3. LOCK_XACT - Schema level lock.

Summary:

This simple excercise helps us to be familiar with some protected system objects. you can expect few more system objects when you create a table with constraints such as Primary key and Foreign keys.

Reference:

Paul Randal