Gets all columns for a given database.

Syntax

Get-ClassificationColumn [[-InstanceName] <string>] [[-DatabaseName] <string>] [[-MaxNumberOfResults] <int>] [[-StartResultNumber] <int>] [[-ColumnNameFilterString] <string>] [[-TableNamesWithSchemasSubstrings] <string[]>] [[-TableNamesWithSchemas] <string[]>] [[-Tags] <hashtable>] [[-ColumnDataTypeFullNames] <string[]>] [[-EmptyTablesFilter] <string>] [[-PrimaryKeyFilter] <string>] [[-IdentityConstraintFilter] <string>] [[-CompositeKeyFilter] <string>] [[-ForeignKeyFilter] <string>] [-WithNoTags] [-WithTagsApplied] [-InEmptyTables] [-PrimaryKeys] [-Identity] [-ForeignKeys] [-CompositePrimaryKeys] [[-InstanceNamesSubstrings] <string[]>] [[-InstanceNames] <string[]>] [[-DatabaseNamesSubstrings] <string[]>] [[-DatabaseNames] <string[]>] [[-PredictedInformationTypeNames] <string[]>] [<CommonParameters>]

Description

Gets all columns for a given database.

Available column properties: - Id - ColumnName - TableName - SchemaName - Description - Tags - FreeTextAttributes - TableRowCount - DataType - DatabaseName - InstanceName

Parameters

-InstanceName <String>

Instance Name Optional. The fully-qualified name of the SQL Server instance. For a named instance, this should take the form 'fully-qualified-host-name\instance-name' (e.g. "myserver.mydomain.com\myinstance"). For the default instance on a machine, just the fully-qualified name of the machine will suffice (e.g. "myserver.mydomain.com"). If unset, the cmdlet will return columns from all registered instances.

-DatabaseName <String>

Database Name Optional. Database name to fetch columns from. If unset, the cmdlet will return columns from all registered databases.

-MaxNumberOfResults <Int32>

Maximum Number Of Results Optional. The maximum number of results you would like returned in one go. Combine this with StartResultNumber to get a slice of columns.

-StartResultNumber <Int32>

Start Result Number Optional. The start index of the matched column collection. See MaxNumberOfResults and the below example.

-ColumnNameFilterString <String>

Column Name Filter String (Where Column Name Contains) Optional. Aliased as WhereColumnNameContains. Column names to match on.

-TableNamesWithSchemasSubstrings <String[]>

Table Names with Schemas Substrings (Where Table Name Contains) Optional. Aliased as WhereTableNameContains. Table names with schemas to match on, e.g. "dbo.Custom" to match "dbo.Customers" and "dbo.Customs".

-TableNamesWithSchemas <String[]>

Table Names With Schemas (Where Table Name Equals) Optional. Aliased as WhereTableNameEquals. Table names with schemas to match on, e.g. "dbo.Custom" to only match a table with that exact spelling.

-Tags <Hashtable>

Tags Optional. Tags to match on, e.g. @{ "Information Classification" = @("Confidential") }

-ColumnDataTypeFullNames <String[]>

Column Data Type Full Names Optional. Data Types to match on, e.g. "varchar(2)"

-EmptyTablesFilter <String>

Empty Tables Filter Optional. Whether to "Include" empty tables, "Exclude" empty tables or "Require" (restrict to) empty tables in the result set. Defaults to "Include". See-also `-InEmptyTables`

-PrimaryKeyFilter <String>

Primary Key Filter Optional. Whether to "Include" primary keys, "Exclude" them or "Require" (only return) primary keys. Defaults to "Include". See-also `-PrimaryKeys`

-IdentityConstraintFilter <String>

Identity Constraint Filter Optional. Whether to "Include" identity columns, "Exclude" them or "Require" (only return) identity columns. Defaults to "Include". See-also `-Identity`

-CompositeKeyFilter <String>

Composite Key Filter Optional. Whether to "Include" composite keys, "Exclude" them or "Require" (only return) composite keys. Defaults to "Include". See-also `-CompositePrimaryKeys`

-ForeignKeyFilter <String>

Foreign Key Filter Optional. Whether to "Include" foreign keys, "Exclude" them or "Require" (only return) foreign keys. Defualts To "Include". See-also `-ForeignKeys`

-WithNoTags <SwitchParameter>

With No Tags Optional. Whether to return columns that lacks tags.

-WithTagsApplied <SwitchParameter>

With Tags Applied Optional. Whether to return columns with any tags applied.

-InEmptyTables <SwitchParameter>

In Empty Tables Optional. Whether to limit the results to columns within empty tables. Equivalent to `-EmptyTablesFilter=Require`.

-PrimaryKeys <SwitchParameter>

Primary Keys Optional. Whether to limit the results to primary key columns. Equivalent to `-PrimaryKeyFilter=Require`.

-Identity <SwitchParameter>

Identity Optional. Whether to limit the results to identity columns. Equivalent to `-IdentityConstraintFilter=Require`.

-ForeignKeys <SwitchParameter>

Foreign Keys Optional. Whether to limit the results to foreign key columns. Equivalent to `-ForeignKeysFilter=Require`.

-CompositePrimaryKeys <SwitchParameter>

Composite Primary Keys Optional. Whether to limit the results to composite primary keys. Equivalent to `-CompositeKeyFilter=Require`.

-InstanceNamesSubstrings <String[]>

Instance Names Substrings (Where Instance Name Contains) Optional. Aliased as WhereInstanceNameContains. Instance names to match on, e.g. "loca" to match "localhost" and "location".

-InstanceNames <String[]>

Instance Names (Where Instance Name Equals) Optional. Aliased as WhereInstanceNameEquals. Instance names to match on, e.g. "local" to only match an instance with that exact spelling.

-DatabaseNamesSubstrings <String[]>

Database Names Substrings (Where Database Name Contains) Optional. Aliased as WhereDatabaseNameContains. Database names to match on, e.g. "adventure" to match "adventureworks" and "adventures".

-DatabaseNames <String[]>

Database Names (Where Database Name Equals) Optional. Aliased as WhereDatabaseNameEquals. Database names to match on, e.g. "adventure" to only match an Database with that exact spelling.

-PredictedInformationTypeNames <String[]>

Predicted Information Types (Where Predicted Information Type Equals) Optional. Aliased as WherePredictedInformationTypeEquals. Predicted Information Type to match on, e.g. "City" to only match an Columns with that exact Predicted Information Type.

-ProgressAction <ActionPreference>

{{ Fill ProgressAction Description }}

<CommonParameters>

This cmdlet supports the common parameters: -Verbose, -Debug, -ErrorAction, -ErrorVariable, -OutBuffer, and -OutVariable. For more information, see http://technet.microsoft.com/en-us/library/hh847884.aspx.

Inputs

The input type is the type of the objects that you can pipe to the cmdlet.

• None.
  You cannot pipe input to this cmdlet.

Return values

The output type is the type of the objects that the cmdlet emits.

• An array of hashtables with the following properties:

  - id: string

  - columnName: string

  - tableName: string

  - schemaName: string

  - description: string

  - tags: An array of Tag

  - freeTextAttributes: An array of FreeTextAttribute

  - tableRowCount: long

  - dataType: string

  - databaseName: string

  - instanceName: string

• Tag: A hashtable with the following properties:

  - id: string

  - name: string

  - categoryId: string

• FreeTextAttribute: A hashtable with the following properties:

  - attributeId: string

  - freeText: string

• ColumnData: A hashtable with the following properties:

  - columnData: string

  - confidence: int

Examples

-------------------------- EXAMPLE 1 --------------------------

Get-ClassificationColumn -instanceName "sqlserver\sql2016" -databaseName "WideWorldImporters"

Fetches all columns from instance "sqlserver\sql2016" database "WideWorldImporters".

-------------------------- EXAMPLE 2 --------------------------

Get-ClassificationColumn -instanceNames "sqlserver\prod","sqlserver\staging" -databaseNamesSubstrings "world"

Fetches all columns from all databases containing the term "world" (e.g. WideWorldImporters) on instances "sqlserver\prod" and "sqlserver\staging".

-------------------------- EXAMPLE 3 --------------------------

Get-ClassificationColumn -InEmptyTables -StartResultNumber 100 -MaxNumberOfResults 25

Fetches up to 25 columns contained within empty tables from all registered databases and instances, ignoring the first 100 matched columns.