---
title: "EVENTDATA (Transact-SQL) | Microsoft Docs"
ms.custom: ""
ms.date: "03/14/2017"
ms.prod: "sql"
ms.prod_service: "database-engine, sql-database"
ms.service: ""
ms.component: "t-sql|functions"
ms.reviewer: ""
ms.suite: "sql"
ms.technology: 
  - "database-engine"
ms.tgt_pltfrm: ""
ms.topic: "language-reference"
f1_keywords: 
  - "EVENTDATA"
  - "fn_event_data"
  - "EVENTDATA_TSQL"
  - "fn_event_data_TSQL"
dev_langs: 
  - "TSQL"
helpviewer_keywords: 
  - "server instance event data [SQL Server]"
  - "event notifications [SQL Server], event status"
  - "events [SQL Server], status infromation"
  - "EVENTDATA function"
  - "status information [SQL Server], events"
  - "DDL triggers, returning event data"
ms.assetid: 03a80e63-6f37-4b49-bf13-dc35cfe46c44
caps.latest.revision: 55
author: "edmacauley"
ms.author: "edmaca"
manager: "craigg"
ms.workload: "On Demand"
---
# EVENTDATA (Transact-SQL)
[!INCLUDE[tsql-appliesto-ss2008-asdb-xxxx-xxx-md](../../includes/tsql-appliesto-ss2008-asdb-xxxx-xxx-md.md)]

  Returns information about server or database events. EVENTDATA is called when an event notification fires, and the results are returned to the specified service broker. EVENTDATA can also be used inside the body of a DDL or logon trigger.  
  
 ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md)  
  
## Syntax  
  
```  
  
EVENTDATA( )  
```  
  
## Remarks  
 EVENTDATA returns data only when referenced directly inside of a DDL or logon trigger. EVENTDATA returns null if it is called by other routines, even if those routines are called by a DDL or logon trigger.  
  
 Data returned by EVENTDATA is not valid after a transaction that called EVENTDATA, either implicitly or explicitly, commits or is rolled back.  
  
> [!CAUTION]  
>  EVENTDATA returns XML data. This data is sent to the client as Unicode that uses 2 bytes for each character. The following Unicode code points can be represented in the XML that is returned by EVENTDATA:  
>   
>  `0x0009`  
>   
>  `0x000A`  
>   
>  `0x000D`  
>   
>  `>= 0x0020 && <= 0xD7FF`  
>   
>  `>= 0xE000 && <= 0xFFFD`  
>   
>  Some characters that can appear in [!INCLUDE[tsql](../../includes/tsql-md.md)] identifiers and data are not expressible or permissible in XML. Characters or data that have code points not shown in the previous list are mapped to a question mark (?).  
  
 To protect the security of logins, when CREATE LOGIN or ALTER LOGIN statements are executed, passwords are not displayed.  
  
## Schemas Returned  
 EVENTDATA returns a value of type **xml**. By default, the schema definition for all events is installed in the following directory: [!INCLUDE[ssInstallPath](../../includes/ssinstallpath-md.md)]Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd.  
  
 Alternatively, the event schema is published at the [Microsoft SQL Server XML Schemas](http://go.microsoft.com/fwlink/?LinkID=31850) Web page.  
  
 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  
 The following example creates a DDL trigger to prevent new tables from being created in the database. The [!INCLUDE[tsql](../../includes/tsql-md.md)] statement that fires the trigger is captured by using XQuery against the XML data that is generated by EVENTDATA. For more information, see [XQuery Language Reference &#40;SQL Server&#41;](../../xquery/xquery-language-reference-sql-server.md).  
  
> [!NOTE]  
>  When you query the `\<TSQLCommand>` element by using **Results to Grid** in [!INCLUDE[ssManStudioFull](../../includes/ssmanstudiofull-md.md)], line breaks in the command text do not appear. Use **Results to Text** instead.  
  
```  
USE AdventureWorks2012;  
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]  
>  When you want to return event data, we recommend that you 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  
 The following example creates a table to store information about all database level events, and populates the table with a DDL trigger. The event type and [!INCLUDE[tsql](../../includes/tsql-md.md)] statement are captured by using XQuery against the XML data generated by `EVENTDATA`.  
  
```  
USE AdventureWorks2012;  
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)