---
title: "EVENTDATA (Transact-SQL)"
description: "EVENTDATA (Transact-SQL)"
author: markingmyname
ms.author: maghan
ms.date: "03/14/2017"
ms.service: sql
ms.subservice: t-sql
ms.topic: reference
f1_keywords:
  - "EVENTDATA"
  - "fn_event_data"
  - "EVENTDATA_TSQL"
  - "fn_event_data_TSQL"
helpviewer_keywords:
  - "server instance event data [SQL Server]"
  - "event notifications [SQL Server], event status"
  - "events [SQL Server], status information"
  - "EVENTDATA function"
  - "status information [SQL Server], events"
  - "DDL triggers, returning event data"
dev_langs:
  - "TSQL"
---
# EVENTDATA (Transact-SQL)
[!INCLUDE [SQL Server Azure SQL Database Azure SQL Managed Instance](../../includes/applies-to-version/sql-asdb-asdbmi.md)]

This function returns information about server or database events. When an event notification fires, and the specified service broker receives the results, `EVENTDATA` is called. A DDL or logon trigger also support internal use of `EVENTDATA`.  
  
 :::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: [Transact-SQL syntax conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)  
  
## Syntax  
  
```syntaxsql
EVENTDATA( )
```

## Remarks  
`EVENTDATA` returns data only when referenced directly inside of a DDL or logon trigger. `EVENTDATA` returns null if other routines call it, even if a DDL or logon trigger calls those routines.
  
Data returned by `EVENTDATA` is invalid after a transaction that

+ called `EVENTDATA` explicitly
+ called `EVENTDATA` implicitly
+ commits
+ is rolled back  
  
> [!CAUTION]  
>  `EVENTDATA` returns XML data, sent to the client as Unicode that uses 2 bytes for each character. `EVENTDATA` returns XML that can represent these Unicode code points:  
>   
>  `0x0009`  
>   
>  `0x000A`  
>   
>  `0x000D`  
>   
>  `>= 0x0020 && <= 0xD7FF`  
>   
>  `>= 0xE000 && <= 0xFFFD`  
>   
>  XML cannot express, and will not permit, some characters that can appear in [!INCLUDE[tsql](../../includes/tsql-md.md)] identifiers and data. Characters or data that have code points not shown in the previous list are mapped to a question mark (?).  
  
Passwords do not display when `CREATE LOGIN` or `ALTER LOGIN` statements execute. This protects login security.  
  
## Schemas Returned  
EVENTDATA returns a value of data type **xml**. By default, the schema definition for all events installs in this directory: [!INCLUDE[ssInstallPath](../../includes/ssinstallpath-md.md)]Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.  
  
The [Microsoft SQL Server XML Schemas](https://go.microsoft.com/fwlink/?LinkID=31850) web page also has the event schema.  
  
To extract the schema for any particular event, search the schema for the Complex Type `EVENT_INSTANCE_<event_type>`. For example, to extract the schema for the `DROP_TABLE` event, search the schema for `EVENT_INSTANCE_DROP_TABLE`.  
  
## Examples  
  
### A. Querying event data in a DDL trigger  
This example creates a DDL trigger that prevents creation of new database tables. Use of XQuery against the XML data generated by `EVENTDATA` captures the [!INCLUDE[tsql](../../includes/tsql-md.md)] statement that fires the trigger. See [XQuery Language Reference &#40;SQL Server&#41;](../../xquery/xquery-language-reference-sql-server.md) for more information.  
  
> [!NOTE]  
>  When using **Results to Grid** in [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)] to query the `<TSQLCommand>` element, line breaks in the command text do not appear. Use **Results to Text** instead.  
  
```sql  
USE AdventureWorks2022;  
GO  
CREATE TRIGGER safety   
ON DATABASE   
FOR CREATE_TABLE   
AS   
    PRINT 'CREATE TABLE Issued.'  
    SELECT EVENTDATA().value  
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
   RAISERROR ('New tables cannot be created in this database.', 16, 1)   
   ROLLBACK  
;  
GO  
--Test the trigger.  
CREATE TABLE NewTable (Column1 INT);  
GO  
--Drop the trigger.  
DROP TRIGGER safety  
ON DATABASE;  
GO  
```  
  
> [!NOTE]  
>  To return event data, use the XQuery **value()** method instead of the **query()** method. The **query()** method returns XML and ampersand-escaped carriage return and line feed (CR/LF) instances in the output, while the **value()** method renders CR/LF instances invisible in the output.  
  
### B. Creating a log table with event data in a DDL trigger  
This example creates a table for information storage about all database level events, and populates that table with a DDL trigger. Use of XQuery against the XML data generated by `EVENTDATA` captures the event type and the [!INCLUDE[tsql](../../includes/tsql-md.md)] statement.  
  
```sql 
USE AdventureWorks2022;  
GO  
CREATE TABLE ddl_log (PostTime DATETIME, DB_User NVARCHAR(100), Event NVARCHAR(100), TSQL NVARCHAR(2000));  
GO  
CREATE TRIGGER log   
ON DATABASE   
FOR DDL_DATABASE_LEVEL_EVENTS   
AS  
DECLARE @data XML  
SET @data = EVENTDATA()  
INSERT ddl_log   
   (PostTime, DB_User, Event, TSQL)   
   VALUES   
   (GETDATE(),   
   CONVERT(NVARCHAR(100), CURRENT_USER),   
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'NVARCHAR(100)'),   
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(2000)') ) ;  
GO  
--Test the trigger.  
CREATE TABLE TestTable (a INT);  
DROP TABLE TestTable ;  
GO  
SELECT * FROM ddl_log ;  
GO  
--Drop the trigger.  
DROP TRIGGER log  
ON DATABASE;  
GO  
--Drop table ddl_log.  
DROP TABLE ddl_log;  
GO  
```  
  
## See Also  
 [Use the EVENTDATA Function](../../relational-databases/triggers/use-the-eventdata-function.md)   
 [DDL Triggers](../../relational-databases/triggers/ddl-triggers.md)   
 [Event Notifications](../../relational-databases/service-broker/event-notifications.md)   
 [Logon Triggers](../../relational-databases/triggers/logon-triggers.md)