dbatoolsGet-DbaDbCheckConstraint
View SourceSynopsis
Gets database Check constraints.
Description
Gets database Checks constraints.
Syntax
Get-DbaDbCheckConstraint
[-SqlInstance] <DbaInstanceParameter[]>
[[-SqlCredential] <PSCredential>]
[[-Database] <Object[]>]
[[-ExcludeDatabase] <Object[]>]
[-ExcludeSystemTable]
[-EnableException]
[<CommonParameters>]
Examples
Example: 1
PS C:\> Get-DbaDbCheckConstraint -SqlInstance sql2016
Gets all database check constraints.
Example: 2
PS C:\> Get-DbaDbCheckConstraint -SqlInstance Server1 -Database db1
Gets the check constraints for the db1 database.
Example: 3
PS C:\> Get-DbaDbCheckConstraint -SqlInstance Server1 -ExcludeDatabase db1
Gets the check constraints for all databases except db1.
Example: 4
PS C:\> Get-DbaDbCheckConstraint -SqlInstance Server1 -ExcludeSystemTable
Gets the check constraints for all databases that are not system objects.
Example: 5
PS C:\> 'Sql1','Sql2/sqlexpress' | Get-DbaDbCheckConstraint
Gets the check constraints for the databases on Sql1 and Sql2/sqlexpress.
Required Parameters
-SqlInstance
The target SQL Server instance or 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 search for check constraints. Accepts wildcards and multiple database names.
Use this when you need to examine constraints on specific databases rather than all accessible databases on the instance.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-ExcludeDatabase
Excludes specific databases from the check constraint search. Accepts multiple database names.
Useful when you want to scan most databases but skip certain ones like development or temporary databases.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value |
-ExcludeSystemTable
Excludes check constraints from system tables when searching through databases.
Use this to focus only on user-created tables and avoid system table constraints that are typically not relevant for DBA reviews.
| Property | Value |
|---|---|
| Alias | |
| Required | False |
| Pipeline | false |
| Default Value | False |
-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
Microsoft.SqlServer.Management.Smo.Check
Returns one Check object per check constraint found in the specified databases. Each object represents a single check constraint defined on a database table.
Default display properties (via Select-DefaultView):
- ComputerName: The computer name of the SQL Server instance
- InstanceName: The SQL Server instance name
- SqlInstance: The full SQL Server instance name (computer\instance)
- Database: The database name containing the check constraint
- Parent: The table object that contains this check constraint
- ID: Unique identifier of the check constraint
- CreateDate: DateTime when the check constraint was created
- DateLastModified: DateTime when the check constraint was last modified
- Name: The name of the check constraint
- IsEnabled: Boolean indicating if the check constraint is currently enabled
- IsChecked: Boolean indicating if the constraint is checked during INSERT/UPDATE operations
- NotForReplication: Boolean indicating if the constraint applies to replication operations
- Text: The actual check constraint definition/expression (the logic that validates the data)
- State: SMO object state (Existing, Creating, Dropping, etc.)
Additional properties available (from SMO Check object):
- DatabaseEngineEdition: The SQL Server edition where the check constraint exists
- DatabaseEngineType: The type of database engine
- Urn: Unique Resource Name for the constraint
- ExtendedProperties: Extended properties attached to the constraint All properties from the base SMO Check object are accessible even though only default properties are displayed without using Select-Object *.