dbatoolsGet-DbaSchemaChangeHistory
View SourceSynopsis
Retrieves DDL change history from the SQL Server default system trace
Description
Queries the default system trace to track CREATE, DROP, and ALTER operations performed on database objects, providing a complete audit trail of schema modifications. This helps DBAs identify who made changes, when they occurred, and which objects were affected without needing to manually parse trace files or enable custom auditing. Returns detailed information including login names, timestamps, application sources, and operation types for compliance reporting and troubleshooting. Only works with SQL Server 2005 and later, as the system trace didn’t exist before then.
Syntax
Get-DbaSchemaChangeHistory
[-SqlInstance] <DbaInstanceParameter[]>
[[-SqlCredential] <PSCredential>]
[[-Database] <Object[]>]
[[-ExcludeDatabase] <Object[]>]
[[-Since] <DbaDateTime>]
[[-Object] <String[]>]
[-EnableException]
[<CommonParameters>]
Examples
Example: 1
PS C:\> Get-DbaSchemaChangeHistory -SqlInstance localhost
Returns all DDL changes made in all databases on the SQL Server instance localhost since the system trace began
Example: 2
PS C:\> Get-DbaSchemaChangeHistory -SqlInstance localhost -Since (Get-Date).AddDays(-7)
Returns all DDL changes made in all databases on the SQL Server instance localhost in the last 7 days
Example: 3
PS C:\> Get-DbaSchemaChangeHistory -SqlInstance localhost -Database Finance, Prod -Since (Get-Date).AddDays(-7)
Returns all DDL changes made in the Prod and Finance databases on the SQL Server instance localhost in the last 7 days
Example: 4
PS C:\> Get-DbaSchemaChangeHistory -SqlInstance localhost -Database Finance -Object AccountsTable -Since (Get-Date).AddDays(-7)
Returns all DDL changes made to the AccountsTable object in the Finance database on the SQL Server instance localhost in the last 7 days
Required Parameters
-SqlInstance
The target SQL Server instance or instances. This can be a collection and receive pipeline input to allow the function to be executed against multiple SQL Server instances.
| Property | Value |
|---|---|
| Alias | |
| Required | True |
| Pipeline | true (ByValue) |
| Default Value |
Optional Parameters
-SqlCredential
Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-Database
Specifies which databases to include when searching for schema changes. Accepts multiple database names and wildcards for pattern matching.
Use this when you need to focus on specific databases instead of scanning all databases on the instance.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-ExcludeDatabase
Specifies databases to exclude from the schema change search. Accepts multiple database names for filtering out unwanted databases.
Use this to skip system databases, test databases, or any databases you don’t want included in the change history results.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-Since
Filters results to show only DDL changes that occurred after the specified date and time. Accepts standard PowerShell date formats.
Use this to focus on recent changes or changes within a specific time period, especially helpful for troubleshooting recent issues or compliance reporting.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-Object
Specifies the names of specific database objects to search for in the change history. Accepts multiple object names for targeted searches.
Use this when investigating changes to particular tables, views, stored procedures, or other database objects rather than reviewing all schema changes.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-EnableException
By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with “sea of red” exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this “nice by default” feature off and enables you to catch exceptions with your own try/catch.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
Outputs
PSCustomObject
Returns one object per DDL change (CREATE, DROP, or ALTER operation) found in the SQL Server default system trace. When no schema changes are found matching the filter criteria, the command returns nothing.
Properties:
- ComputerName: The computer name of the SQL Server instance
- InstanceName: The SQL Server instance name
- SqlInstance: The full SQL Server instance name (computer\instance format)
- DatabaseName: The database in which the schema change occurred
- DateModified: DateTime when the DDL operation was executed
- LoginName: The login name (SQL or Windows) that executed the change
- UserName: The Windows user name (domain\username format) if using Windows Authentication, or SQL login name
- ApplicationName: The application or tool that executed the DDL (e.g., “SQL Server Management Studio”, “sqlcmd”)
- DDLOperation: Type of operation performed - ‘Create’ for CREATE statements, ‘Drop’ for DROP statements, ‘Alter’ for ALTER statements
- Object: The name of the database object that was created, dropped, or altered (table, procedure, view, etc.)
- ObjectType: The type of database object affected (Table, StoredProcedure, View, Index, Trigger, UserDefinedFunction, etc.) All properties are strings except DateModified which is DateTime. The ObjectType may be “Unknown” if the specific object type cannot be determined from the trace data.