View:
[AdventureWorks2016CTP3].[dbo].[AUDIT_VIEW]
View properties
| Name | Value |
| Schema | [dbo] |
| Owner | [dbo] |
| Creation date | 21.09.2016 |
| Is schema bound | |
| Encrypted | |
| ID | 1307867726 |
Creation options
| Name | Value |
| QUOTED_IDENTIFIER | ON |
| ANSI_NULLS | ON |
Resultset
| Name | Description | Data type | Max length |
| TABLE_NAME | nvarchar | 261 | |
| ACTION | varchar | 6 | |
| MODIFIED_BY | varchar | 128 | |
| PRIMARY_KEY | nvarchar | 1500 | |
| REC_COUNT | int | 4 | |
| MODIFIED_DATE | varchar | 20 | |
| COMPUTER | varchar | 128 | |
| APPLICATION | varchar | 128 |
Objects that depend on [dbo].[AUDIT_VIEW]
| Object name | Object type | Dep level |
| [dbo].[AUDIT_prc_DeleteArchitecture] | Procedure | 1 |
Objects that [dbo].[AUDIT_VIEW] depends on
| Object name | Object type | Dep level |
| [dbo].[AUDIT_LOG_DATA] | Table | 1 |
| [dbo].[AUDIT_LOG_TRANSACTIONS] | Table | 1 |
SQL
SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE VIEW dbo.AUDIT_VIEW AS /* ------------------------------------------------------------ VIEW: AUDIT_VIEW DESCRIPTION: Selects Audit Log records and groups by MODIFIED_DATE and PK effectively grouping audit data by Audit transaction ------------------------------------------------------------ */ SELECT MAX(t.TABLE_NAME) AS TABLE_NAME, CASE MAX(t.AUDIT_ACTION_ID) WHEN 1 THEN 'UPDATE' WHEN 2 THEN 'INSERT' WHEN 3 THEN 'DELETE' END AS ACTION, MAX(t.MODIFIED_BY) AS MODIFIED_BY, MAX(PRIMARY_KEY_DATA) AS PRIMARY_KEY, COUNT(DISTINCT PRIMARY_KEY_DATA) AS REC_COUNT, CONVERT(varchar(20), MODIFIED_DATE, 113) AS MODIFIED_DATE, Max(HOST_NAME) AS COMPUTER, Max(APP_NAME) as APPLICATION FROM dbo.AUDIT_LOG_TRANSACTIONS t INNER JOIN dbo.AUDIT_LOG_DATA r ON r.AUDIT_LOG_TRANSACTION_ID = t.AUDIT_LOG_TRANSACTION_ID GROUP BY MODIFIED_DATE, PRIMARY_KEY_DATA GO |
See also