Skip to content

Latest commit

 

History

History
90 lines (69 loc) · 3.62 KB

sys-ledger-table-history-transact-sql.md

File metadata and controls

90 lines (69 loc) · 3.62 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic dev_langs monikerRange
sys.ledger_table_history (Transact-SQL)
sys.ledger_table_history (Transact-SQL)
VanMSFT
vanto
05/23/2023
sql
system-objects
reference
TSQL
=azuresqldb-current||>=sql-server-ver16||>=sql-server-linux-ver16

sys.ledger_table_history (Transact-SQL)

[!INCLUDE SQL Server 2022 Azure SQL Database Azure SQL Managed Instance]

Captures the cryptographically protected history of operations on ledger tables: creating ledger tables, renaming ledger tables or ledger views, and dropping ledger tables.

For more information on database ledger, see Ledger

Column name Data type Description
object_id int The object ID of the ledger table.
schema_name sysname The name of the schema containing the ledger table. If the operation has changed the schema name, this column captures the new schema name.
table_name sysname The name of the ledger table. If the operation has changed the table name, this column captures the new table name.
ledger_view_schema_name sysname The name of the schema containing the ledger view for the ledger table. If the operation has changed the schema name, this column captures the new schema name.
ledger_view_name sysname The name of the ledger view for the ledger table. If the operation has changed the view name, this column captures the new view name.
operation_type tinyint The numeric value indicating the type of the operation

0 = CREATE – creating a ledger table.
1 = DROP – dropping a ledger table.
2 = RENAME - renaming a ledger table.
3 = RENAME_VIEW - renaming the ledger view for a ledger table.
operation_type_desc nvarchar(60) Textual description of the value of operation_type.
transaction_id bigint The transaction of the ID that included the operation on the ledger table. It identifies a row in sys.database_ledger_transactions.
sequence_number bigint The sequence number of the operation within the transaction.

Permissions

Requires the VIEW LEDGER CONTENT permission.

Examples

Consider the following sequence of operations on ledger tables.

  1. A user creates a ledger table.

    CREATE TABLE [Employees]
(
    EmployeeID INT NOT NULL,
    Salary Money NOT NULL
)
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);
GO

  2. A user renames the ledger table.

    EXEC sp_rename 'Employees', 'Employees_Copy';

  3. A user renames the ledger view of the ledger table.

    EXEC sp_rename 'Employees_Ledger', 'Employees_Ledger_Copy';

  4. A user drops the ledger table.

    DROP TABLE [Employees];

The below query joins sys.ledger_table_history and sys.database_ledger_transactions to produce the history of changes on ledger tables, including the time of each and change and the name of the user who triggered it.

SELECT 
t.[principal_name]
, t.[commit_time]
, h.[schema_name] + '.' + h.[table_name] AS [table_name]
, h.[ledger_view_schema_name] + '.' + h.[ledger_view_name] AS [view_name]
, h.[operation_type_desc]
FROM sys.ledger_table_history h
JOIN sys.database_ledger_transactions t
ON h.transaction_id = t.transaction_id

See also