Databaseline.psm1
|
function Resolve-QueryResult { <# .SYNOPSIS Executes SQL that generates SQL strings, and optionally executes the generated SQL. .PARAMETER Action Descriptive text for the commands produced, with two format arguments: 0: Verb tense, e.g. 'Renam{0:e;ing;ed}' 1: Command count .PARAMETER Query A SQL query that produces a single-column result set, named "command", containing executable SQL. #> [CmdletBinding(SupportsShouldProcess=$true)] Param([string]$Action,[string]$Query) $count,$i = 0,0 [string[]]$commands = Invoke-DbaQuery -Query $Query -As PSObject |Select-Object -ExpandProperty command if(!$commands){return} $max,$act = ($commands.Count/100),($Action -f -1,$commands.Count) Write-Verbose ($Action -f 1,$commands.Count) foreach($command in $commands) { Write-Progress $act "Execute command #$i" -CurrentOperation $command -PercentComplete ($i++/$max) if(!$Update) {$command} elseif($PSCmdlet.ShouldProcess($command,'execute')) {Invoke-DbaQuery -Query $command -As PSObject; $count++} } Write-Progress ($action -f 0,$i) -Completed if($count) {Write-Warning ($Action -f 0,$count)} } function Export-MermaidER { <# .SYNOPSIS Generates a Mermaid entity relation diagram for database tables. .FUNCTIONALITY Mermaid Diagrams .NOTES All tables in the pipeline must exist in the same database. .LINK https://mermaid.js.org/syntax/entityRelationshipDiagram.html .LINK https://learn.microsoft.com/dotnet/api/table .LINK https://dbatools.io/ .EXAMPLE Get-DbaDbTable -SqlInstance '(localdb)\ProjectsV13' -Database AdventureWorks2016 -Table Production.Product |Export-MermaidER erDiagram Product { int ProductID PK "identity(1,1); Primary key for Product records." Name Name "Name of the product." nvarchar ProductNumber "Unique product identification number." Flag MakeFlag "0 = Product is purchased, 1 = Product is manufactured in-house." Flag FinishedGoodsFlag "0 = Product is not a salable item. 1 = Product is salable." nvarchar Color "nullable; Product color." smallint SafetyStockLevel "Minimum inventory quantity. " smallint ReorderPoint "Inventory level that triggers a purchase order or work order. " money StandardCost "Standard cost of the product." money ListPrice "Selling price." nvarchar Size "nullable; Product size." nchar SizeUnitMeasureCode FK "nullable; Unit of measure for Size column." nchar WeightUnitMeasureCode FK "nullable; Unit of measure for Weight column." decimal Weight "nullable; Product weight." int DaysToManufacture "Number of days required to manufacture the product." nchar ProductLine "nullable; R = Road, M = Mountain, T = Touring, S = Standard" nchar Class "nullable; H = High, M = Medium, L = Low" nchar Style "nullable; W = Womens, M = Mens, U = Universal" int ProductSubcategoryID FK "nullable; Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID. " int ProductModelID FK "nullable; Product is a member of this product model. Foreign key to ProductModel.ProductModelID." datetime SellStartDate "Date the product was available for sale." datetime SellEndDate "nullable; Date the product was no longer available for sale." datetime DiscontinuedDate "nullable; Date the product was discontinued." uniqueidentifier rowguid "ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample." datetime ModifiedDate "Date and time the record was last updated." } .EXAMPLE Get-DbaDbTable -SqlInstance '(localdb)\ProjectsV13' -Database AdventureWorks2016 -Schema Purchasing |Export-MermaidER erDiagram ProductVendor { int ProductID PK "Primary key. Foreign key to Product.ProductID." int BusinessEntityID PK "Primary key. Foreign key to Vendor.BusinessEntityID." int AverageLeadTime "The average span of time (in days) between placing an order with the vendor and receiving the purchased product." money StandardPrice "The vendor's usual selling price." money LastReceiptCost "nullable; The selling price when last purchased." datetime LastReceiptDate "nullable; Date the product was last received by the vendor." int MinOrderQty "The maximum quantity that should be ordered." int MaxOrderQty "The minimum quantity that should be ordered." int OnOrderQty "nullable; The quantity currently on order." nchar UnitMeasureCode FK "The product's unit of measure." datetime ModifiedDate "Date and time the record was last updated." } PurchaseOrderDetail { int PurchaseOrderID PK "Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID." int PurchaseOrderDetailID PK "identity(1,1); Primary key. One line number per purchased product." datetime DueDate "Date the product is expected to be received." smallint OrderQty "Quantity ordered." int ProductID FK "Product identification number. Foreign key to Product.ProductID." money UnitPrice "Vendor's selling price of a single product." money LineTotal "Per product subtotal. Computed as OrderQty * UnitPrice." decimal ReceivedQty "Quantity actually received from the vendor." decimal RejectedQty "Quantity rejected during inspection." decimal StockedQty "Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty." datetime ModifiedDate "Date and time the record was last updated." } PurchaseOrderHeader { int PurchaseOrderID PK "identity(1,1); Primary key." tinyint RevisionNumber "Incremental number to track changes to the purchase order over time." tinyint Status "Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete" int EmployeeID FK "Employee who created the purchase order. Foreign key to Employee.BusinessEntityID." int VendorID FK "Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID." int ShipMethodID FK "Shipping method. Foreign key to ShipMethod.ShipMethodID." datetime OrderDate "Purchase order creation date." datetime ShipDate "nullable; Estimated shipment date from the vendor." money SubTotal "Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID." money TaxAmt "Tax amount." money Freight "Shipping cost." money TotalDue "Total due to vendor. Computed as Subtotal + TaxAmt + Freight." datetime ModifiedDate "Date and time the record was last updated." } ShipMethod { int ShipMethodID PK "identity(1,1); Primary key for ShipMethod records." Name Name "Shipping company name." money ShipBase "Minimum shipping charge." money ShipRate "Shipping charge per pound." uniqueidentifier rowguid "ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample." datetime ModifiedDate "Date and time the record was last updated." } Vendor { int BusinessEntityID PK "Primary key for Vendor records. Foreign key to BusinessEntity.BusinessEntityID" AccountNumber AccountNumber "Vendor account (identification) number." Name Name "Company name." tinyint CreditRating "1 = Superior, 2 = Excellent, 3 = Above average, 4 = Average, 5 = Below average" Flag PreferredVendorStatus "0 = Do not use if another vendor is available. 1 = Preferred over other vendors supplying the same product." Flag ActiveFlag "0 = Vendor no longer used. 1 = Vendor is actively used." nvarchar PurchasingWebServiceURL "nullable; Vendor URL." datetime ModifiedDate "Date and time the record was last updated." } ProductVendor }|--|| Vendor : "BusinessEntityID: Foreign key constraint referencing Vendor.BusinessEntityID." PurchaseOrderDetail }|--|| PurchaseOrderHeader : "PurchaseOrderID: Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID." PurchaseOrderHeader }|--|| ShipMethod : "ShipMethodID: Foreign key constraint referencing ShipMethod.ShipMethodID." PurchaseOrderHeader }|--|| Vendor : "VendorID: Foreign key constraint referencing Vendor.VendorID." #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSUseProcessBlockForPipelineCommand','', Justification='This script uses $input within an End block.')] [CmdletBinding()][OutputType([string])] Param( # An SMO table object to include in the diagram. [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)] [Table] $Table ) Begin { $NL = "`r`n" filter Format-ColumnAsMermaid { Param( [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][DataType] $DataType, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][ExtendedPropertyCollection] $ExtendedProperties, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][string] $Name, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][bool] $InPrimaryKey, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][bool] $IsForeignKey, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][bool] $Nullable, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][bool] $Identity, [Parameter(ValueFromPipelineByPropertyName=$true)][long] $IdentitySeed, [Parameter(ValueFromPipelineByPropertyName=$true)][long] $IdentityIncrement, [Parameter(ValueFromPipelineByPropertyName=$true)][string] $Default ) $key = if($InPrimaryKey){' PK'}elseif($IsForeignKey){' FK'} [string[]] $details = @() if($Nullable) {$details += 'nullable'} if($Identity) {$details += "identity($IdentitySeed,$IdentityIncrement)"} if($ExtendedProperties['MS_Description']) {$details += $ExtendedProperties['MS_Description'].Value -replace '"',"'"} if($details) {$details = ' "{0}"' -f ($details -join '; ')} return "$DataType $Name$key$details" } filter Format-TableAsMermaid { Param( [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][string] $Name, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][ColumnCollection] $Columns ) $Local:OFS = "$NL`t" return @" $Name { $($Columns |Format-ColumnAsMermaid) } "@ } filter Format-ForeignKeyAsMermaid { Param( [Parameter(Position=0,Mandatory=$true)][TableCollection] $AllDatabaseTables, [Parameter(Position=1,Mandatory=$true)][string[]] $SelectedTableUrns, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][string] $Name, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][string] $ReferencedTable, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][string] $ReferencedTableSchema, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][bool] $IsEnabled, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][Table] $Parent, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][ForeignKeyColumnCollection] $Columns, [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true)][ExtendedPropertyCollection] $ExtendedProperties ) if(!$IsEnabled) {return} if($AllDatabaseTables[$ReferencedTable,$ReferencedTableSchema].Urn.Value -notin $SelectedTableUrns) {return} $description = $Columns.Name -join ', ' if($ExtendedProperties['MS_Description']) {$description += ': {0}' -f ($ExtendedProperties['MS_Description'].Value -replace '"',"'")} return "$($Parent.Name) }|--|| $ReferencedTable : `"$description`"$NL" } } End { [Table[]] $tables = if($input) {$input} else {@($Table)} $Local:OFS = '' return @" erDiagram $(($tables |Format-TableAsMermaid) -join $NL) $($tables | Select-Object -ExpandProperty ForeignKeys | Format-ForeignKeyAsMermaid -AllDatabaseTables $tables[0].Parent.Tables -SelectedTableUrns $tables.Urn.Value) "@ } } function Export-TableMerge { <# .SYNOPSIS Exports table data as a T-SQL MERGE statement. .OUTPUTS System.String of SQL MERGE script to replicate the table's data. .FUNCTIONALITY Database .LINK https://learn.microsoft.com/sql/t-sql/statements/merge-transact-sql .LINK https://dbatools.io/ .EXAMPLE Get-DbaDbTable -SqlInstance $server -Schema HumanResources -Table Department |Export-TableMerge if exists (select * from information_schema.columns where table_schema = 'HumanResources' and table_name = 'Department' and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1) set identity_insert [HumanResources].[Department] on; merge [HumanResources].[Department] as target using ( values (1, 'Engineering', 'Research and Development', '2008-04-30 00:00:00.00000'), (2, 'Tool Design', 'Research and Development', '2008-04-30 00:00:00.00000'), (3, 'Sales', 'Sales and Marketing', '2008-04-30 00:00:00.00000'), (4, 'Marketing', 'Sales and Marketing', '2008-04-30 00:00:00.00000'), (5, 'Purchasing', 'Inventory Management', '2008-04-30 00:00:00.00000'), (6, 'Research and Development', 'Research and Development', '2008-04-30 00:00:00.00000'), (7, 'Production', 'Manufacturing', '2008-04-30 00:00:00.00000'), (8, 'Production Control', 'Manufacturing', '2008-04-30 00:00:00.00000'), (9, 'Human Resources', 'Executive General and Administration', '2008-04-30 00:00:00.00000'), (10, 'Finance', 'Executive General and Administration', '2008-04-30 00:00:00.00000'), (11, 'Information Services', 'Executive General and Administration', '2008-04-30 00:00:00.00000'), (12, 'Document Control', 'Quality Assurance', '2008-04-30 00:00:00.00000'), (13, 'Quality Assurance', 'Quality Assurance', '2008-04-30 00:00:00.00000'), (14, 'Facilities and Maintenance', 'Executive General and Administration', '2008-04-30 00:00:00.00000'), (15, 'Shipping and Receiving', 'Inventory Management', '2008-04-30 00:00:00.00000'), (16, 'Executive', 'Executive General and Administration', '2008-04-30 00:00:00.00000') ) as source ([DepartmentID], [Name], [GroupName], [ModifiedDate]) on source.[DepartmentID] = target.[DepartmentID] when matched then update set [Name] = source.[Name], [GroupName] = source.[GroupName], [ModifiedDate] = source.[ModifiedDate] when not matched by target then insert ([DepartmentID], [Name], [GroupName], [ModifiedDate]) values (source.[DepartmentID], source.[Name], source.[GroupName], source.[ModifiedDate]) when not matched by source then delete ; if exists (select * from information_schema.columns where table_schema = 'HumanResources' and table_name = 'Department' and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1) set identity_insert [HumanResources].[Department] off; #> [CmdletBinding()][OutputType([string])] Param( [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)][Table] $Table ) Begin { filter ConvertTo-SqlName([Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)][string] $Name) { return [SqlSmoObject]::QuoteString($Name,'[',']') } filter ConvertTo-SqlLiteral([Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true)] $Value) { switch($Value.GetType()) { dbnull {'null'} string {"'$($Value -replace "'","''")'"} datetime {Get-Date $Value -f "\'yyyy-MM-dd HH:mm:ss.fffff\'"} bool {$Value ? 1 : 0} guid {"'$Value'"} default {$Value} } } function Format-Merge([Table] $Table) { $NL = [Environment]::NewLine $identitytest = @" if exists (select * from information_schema.columns where table_schema = $(ConvertTo-SqlLiteral $Table.Schema) and table_name = $(ConvertTo-SqlLiteral $Table.Name) and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1) "@ $columns = ($Table.Columns.Name |ConvertTo-SqlName) -join ', ' $fieldupdates = ($Table.Columns |Where-Object {!$_.InPrimaryKey} |Select-Object -ExpandProperty Name | ConvertTo-SqlName |ForEach-Object {"$_ = source.$_"}) -join ",$NL" $fieldupdates = if($fieldupdates) {"when matched then${NL}update set $fieldupdates"} else {"-- skip 'matched' condition (no non-key columns to update)"} return @" $identitytest set identity_insert $Table on; merge $Table as target using ( values $((Invoke-DbaQuery -SqlInstance $Table.Parent.Parent -Database $Table.Parent.Name -Query "select * from $Table;" -As DataRow | ForEach-Object {"($(($_.ItemArray |ConvertTo-SqlLiteral) -join ', '))"}) -join ",$NL") ) as source ($columns) on $(($Table.Columns |Where-Object {$_.InPrimaryKey} |Select-Object -ExpandProperty Name |ConvertTo-SqlName | ForEach-Object {"source.$_ = target.$_"}) -join "${NL}and ") $fieldupdates when not matched by target then insert ($columns) values ($(($Table.Columns.Name |ConvertTo-SqlName |ForEach-Object {"source.$_"}) -join ', ')) when not matched by source then delete ; $identitytest set identity_insert $Table off; "@ } } Process { return Format-Merge $Table } } function Find-DatabaseValue { <# .SYNOPSIS Searches an entire database for a field value. .OUTPUTS System.Management.Automation.PSCustomObject for each found row, including the #TableName, #ColumnName, and all fields. .FUNCTIONALITY Database .COMPONENT System.Configuration .LINK https://dbatools.io/ .EXAMPLE Find-DatabaseValue FR -IncludeSchemata Sales -MaxRows 100 -SqlInstance '(localdb)\ProjectsV13' -Database AdventureWorks2016 TableName : [Sales].[SalesTerritory] TerritoryID : 7 Name : France CountryRegionCode : FR Group : Europe SalesYTD : 4772398.3078 SalesLastYear : 2396539.7601 CostYTD : 0.0000 CostLastYear : 0.0000 rowguid : bf806804-9b4c-4b07-9d19-706f2e689552 ModifiedDate : 04/30/2008 00:00:00 .EXAMPLE Find-DatabaseValue 41636 -IncludeColumns %OrderID -SqlInstance '(localdb)\ProjectsV13' -Database AdventureWorks2016 |tee order41636.txt TableName : [Production].[TransactionHistory] TransactionID : 100046 ProductID : 826 ReferenceOrderID : 41636 ReferenceOrderLineID : 0 TransactionDate : 07/31/2013 00:00:00 TransactionType : W Quantity : 4 ActualCost : 0.0000 ModifiedDate : 07/31/2013 00:00:00 TableName : [Production].[WorkOrder] WorkOrderID : 41636 ProductID : 826 OrderQty : 4 StockedQty : 4 ScrappedQty : 0 StartDate : 07/31/2013 00:00:00 EndDate : 08/11/2013 00:00:00 DueDate : 08/11/2013 00:00:00 ScrapReasonID : ModifiedDate : 08/11/2013 00:00:00 TableName : [Production].[WorkOrderRouting] WorkOrderID : 41636 ProductID : 826 OperationSequence : 6 LocationID : 50 ScheduledStartDate : 07/31/2013 00:00:00 ScheduledEndDate : 08/11/2013 00:00:00 ActualStartDate : 08/01/2013 00:00:00 ActualEndDate : 08/11/2013 00:00:00 ActualResourceHrs : 3.0000 PlannedCost : 36.7500 ActualCost : 36.7500 ModifiedDate : 08/11/2013 00:00:00 #> [CmdletBinding()][OutputType([Management.Automation.PSCustomObject])] Param( <# The value to search for. The datatype is significant, e.g. searching for money/smallmoney columns, cast the type to decimal: [decimal]13.55 Searches, by type: * string: varchar, char, nvarchar, nchar (char length must be at least as long as value) * byte: tinyint * int: bigint, int * long: bigint, numeric or decimal (where scale is zero) * decimal: money, smallmoney * double or float: float, real, numeric, decimal * datetime: date (if no time specified), datetime, datetime2, datetimeoffset, smalldatetime * timespan: time If the -LikeValue switch is specified, the type of value is assumed to be string. #> [Parameter(Position=0,Mandatory=$true)] $Value, # The server to use, by name or constructed via Connect-DbaInstance. [Parameter(Position=0,Mandatory=$true)][Alias('Parent','ServerInstance')][DbaInstanceParameter] $SqlInstance, # The the database to connect to on the server. [Parameter(Position=1,Mandatory=$true)][Alias('Name')][string] $Database, # A like-pattern of database schemata to include (will only include these). [string[]] $IncludeSchemata, # A like-pattern of database schemata to exclude. [string[]] $ExcludeSchemata, # A like-pattern of database tables to include (will only include these). [string[]] $IncludeTables, # A like-pattern of database tables to exclude. [string[]] $ExcludeTables, # A like-pattern of database columns to include (will only include these). [string[]] $IncludeColumns, # A like-pattern of database columns to exclude. [string[]] $ExcludeColumns, # Tables with more rows than this value will be skipped. [int] $MinRows = 1, # Tables with more rows than this value will be skipped. [int] $MaxRows, # Quit as soon as the first value is found. [switch] $FindFirst, # Interpret the value as a like-pattern (% for zero-or-more characters, _ for a single character, \ is escape). [switch] $LikeValue ) function Format-LikeCondition([string]$column,[string[]]$patterns,[switch]$not) { $like,$andOr = if($not){'not like','and'}else{'like','or'} @" and ( $(($patterns |ForEach-Object {"$column $like '$($_ -replace '''','''''')' escape '\'"}) -join " $andOr ") ) "@ } Use-DbInstance -As PSObject if($Value -is [int]) { if($Value -le [byte]::MaxValue) {$Value = [byte] $Value} elseif($Value -le [short]::MaxValue) {$Value = [short] $Value} } $selectFrom = "select '{0}.{1}' [#TableName], '{2}' [#ColumnName], * from" $colssql = @" select quotename(TABLE_SCHEMA) TABLE_SCHEMA, quotename(TABLE_NAME) TABLE_NAME, quotename(COLUMN_NAME) COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS "@ if($LikeValue) { $minLength = ($Value -replace '\\.','_' -replace '%','').Length Write-Verbose "Searching for character data with a minimum length of $minLength to match pattern." $colssql += @" where DATA_TYPE in ('varchar','char','nvarchar','nchar') and (CHARACTER_MAXIMUM_LENGTH = -1 or CHARACTER_MAXIMUM_LENGTH >= $minLength) "@ $valsql = "$selectFrom {0}.{1} where {2} like '$($Value -replace '''','''''')' escape '\';" } elseif($Value -is [string]) { Write-Verbose "Searching for character data with a minimum length of $($Value.Length)." $colssql += @" where DATA_TYPE in ('varchar','char','nvarchar','nchar') and (CHARACTER_MAXIMUM_LENGTH = -1 or CHARACTER_MAXIMUM_LENGTH >= $($Value.Length)) "@ $valsql = "$selectFrom {0}.{1} where {2} = '$($Value -replace '''','''''')';" } elseif($Value -is [byte]) { Write-Verbose "Searching for byte (tinyint) data." $colssql += @" where DATA_TYPE in ('tinyint','smallint','int') "@ $valsql = "$selectFrom {0}.{1} where {2} = $Value;" } elseif($Value -is [short]) { Write-Verbose "Searching for short (smallint) data." $colssql += @" where DATA_TYPE in ('smallint','int') "@ $valsql = "$selectFrom {0}.{1} where {2} = $Value;" } elseif($Value -is [int]) { Write-Verbose "Searching for integer data." $colssql += @" where DATA_TYPE in ('int') "@ $valsql = "$selectFrom {0}.{1} where {2} = $Value;" } elseif($Value -is [long]) { Write-Verbose "Searching for long integer data." $colssql += @" where (DATA_TYPE = 'bigint' or (DATA_TYPE in ('numeric','decimal') and NUMERIC_SCALE = 0)) "@ $valsql = "$selectFrom {0}.{1} where {2} = '$Value';" } elseif($Value -is [decimal]) { Write-Verbose "Searching for decimal (money) data." $colssql += @" where DATA_TYPE in ('money','smallmoney') "@ $valsql = "$selectFrom {0}.{1} where {2} = $Value;" } elseif($Value -is [double] -or $Value -is [float]) { Write-Verbose "Searching for double-precision floating-point or money data." $colssql += @" where DATA_TYPE in ('float','real','numeric','decimal') "@ $valsql = "$selectFrom {0}.{1} where {2} = $Value;" } elseif($Value -is [datetime] -and $Value.TimeOfDay -eq 0) { Write-Verbose "Searching for date data." $colssql += @" where DATA_TYPE in ('date','datetime','datetime2','datetimeoffset','smalldatetime') "@ $valsql = "$selectFrom {0}.{1} where {2} = '$($Value.ToString('yyyy-MM-dd'))';" } elseif($Value -is [datetime]) { Write-Verbose "Searching for datetime data." $colssql += @" where DATA_TYPE in ('datetime','datetime2','datetimeoffset','smalldatetime') "@ $valsql = "$selectFrom {0}.{1} where {2} = '$($Value.ToString('u'))';" } elseif($Value -is [timespan]) { Write-Verbose "Searching for time data." $colssql += @" where DATA_TYPE in ('time') "@ $valsql = "$selectFrom {0}.{1} where {2} = '$($Value.ToString('HH:mm:ss.fffff'))';" } if($IncludeSchemata) { $colssql += Format-LikeCondition TABLE_SCHEMA $IncludeSchemata } if($ExcludeSchemata) { $colssql += Format-LikeCondition TABLE_SCHEMA $ExcludeSchemata -Not } if($IncludeTables) { $colssql += Format-LikeCondition TABLE_NAME $IncludeTables } if($ExcludeTables) { $colssql += Format-LikeCondition TABLE_NAME $ExcludeTables -Not } if($IncludeColumns) { $colssql += Format-LikeCondition COLUMN_NAME $IncludeColumns } if($ExcludeColumns) { $colssql += Format-LikeCondition COLUMN_NAME $ExcludeColumns -Not } $colssql += ' order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;' Write-Debug "Schema Query:`n$colssql" $corpus = Invoke-DbaQuery -Query $colssql if(!$corpus) {throw 'No columns left to search.'} Write-Verbose "Searching $($corpus.Length) tables" $count,$p,$rows,$lasttable = 0,0,0,'' foreach($row in $corpus) { Import-Variables $row if($lasttable -ne "$TABLE_SCHEMA.$TABLE_NAME") { [int]$rows = Invoke-DbaQuery -Query "select count(*) rows from $TABLE_SCHEMA.$TABLE_NAME" -As SingleValue $lasttable = "$TABLE_SCHEMA.$TABLE_NAME" } Write-Progress 'Searching columns' "$TABLE_SCHEMA.$TABLE_NAME.$COLUMN_NAME" 1 -CurrentOperation "$rows rows" ` -PercentComplete ((++$p)*100/$corpus.Length) -ErrorAction Ignore if($rows -lt $MinRows) {Write-Verbose "Skipping $TABLE_SCHEMA.$TABLE_NAME ($rows rows < $MinRows)"; continue} if($MaxRows -and $rows -gt $MaxRows) {Write-Verbose "Skipping $TABLE_SCHEMA.$TABLE_NAME ($rows rows > $MaxRows)"; continue} $query = $valsql -f $TABLE_SCHEMA,$TABLE_NAME,$COLUMN_NAME [Data.DataTable]$data = $null Write-Verbose "Query: $query" $data = try {Invoke-DbaQuery -Query $query -As DataTable} catch {Write-Error $_; continue} if($data -and ($data.Rows.Count -gt 0)) { $count += $data.Rows.Count Write-Verbose "Found $($data.Rows.Count) rows in $TABLE_SCHEMA.$TABLE_NAME." $data.Rows if($FindFirst) { break } } } if(!$count) {Write-Warning "No rows found."} else {Write-Verbose "Found $count total rows."} } function Find-DbColumn { <# .SYNOPSIS Searches for database columns. .OUTPUTS System.Management.Automation.PSCustomObject for each found column: * TableSchema * TableName * ColumnName * DataType * Nullable * DefaultValue .FUNCTIONALITY Database .COMPONENT System.Configuration .LINK https://dbatools.io/ .EXAMPLE Find-DbColumn -SqlInstance '(localdb)\ProjectsV13' -Database AdventureWorks2016 -IncludeColumns %price% |Format-Table -AutoSize TableSchema TableName ColumnName DataType Nullable DefaultValue ----------- --------- ---------- -------- -------- ------------ Production Product ListPrice money False Production ProductListPriceHistory ListPrice money False Purchasing ProductVendor StandardPrice money False Purchasing PurchaseOrderDetail UnitPrice money False Sales SalesOrderDetail UnitPrice money False Sales SalesOrderDetail UnitPriceDiscount money False ((0.0)) #> [CmdletBinding()][OutputType([Management.Automation.PSCustomObject])] Param( # The server to use, by name or constructed via Connect-DbaInstance. [Parameter(Position=0,Mandatory=$true)][Alias('Parent','ServerInstance')][DbaInstanceParameter] $SqlInstance, # The the database to connect to on the server. [Parameter(Position=1,Mandatory=$true)][Alias('Name')][string] $Database, # A like-pattern of database schemata to include (will only include these). [string[]] $IncludeSchemata, # A like-pattern of database schemata to exclude. [string[]] $ExcludeSchemata, # A like-pattern of database tables to include (will only include these). [string[]] $IncludeTables, # A like-pattern of database tables to exclude. [string[]] $ExcludeTables, # A like-pattern of database columns to include (will only include these). [string[]] $IncludeColumns, # A like-pattern of database columns to exclude. [string[]] $ExcludeColumns, # The basic datatype to search for. [ValidateSet('char','byte','int','long','decimal','double','date','datetime','time')] [string] $DataType, # The minimum character column length. [int] $MinLength, # The maximum character column length. [int] $MaxLength ) try{[void][Configuration.ConfigurationManager]}catch{Add-Type -AssemblyName System.Configuration} function Format-LikeCondition([string]$column,[string[]]$patterns,[switch]$not) { $like,$andOr = if($not){'not like','and'}else{'like','or'} @" and ( $(($patterns |ForEach-Object {"$column $like '$($_ -replace '''','''''')' escape '\'"}) -join " $andOr ") ) "@ } Use-DbInstance $colssql = @" select TABLE_SCHEMA TableSchema, TABLE_NAME TableName, COLUMN_NAME ColumnName, DATA_TYPE + case when DATA_TYPE in ('int','smallint','bigint','tinyint','money','bit') then '' when CHARACTER_MAXIMUM_LENGTH is not null then '(' + cast(CHARACTER_MAXIMUM_LENGTH as varchar) + ')' when NUMERIC_PRECISION is not null then '(' + cast(NUMERIC_PRECISION as varchar) + case when NUMERIC_PRECISION_RADIX is not null and NUMERIC_PRECISION_RADIX <> 10 then ' base ' + cast(NUMERIC_PRECISION_RADIX as varchar) else '' end + case when NUMERIC_SCALE is not null then ',' + cast(NUMERIC_SCALE as varchar) else '' end + ')' else '' end DataType, cast(case IS_NULLABLE when 'Yes' then 1 else 0 end as bit) Nullable, COLUMN_DEFAULT DefaultValue from INFORMATION_SCHEMA.COLUMNS "@ $colssql += switch($DataType) { string {@" where DATA_TYPE in ('varchar','char','nvarchar','nchar') $(if($MinLength){" and (CHARACTER_MAXIMUM_LENGTH = -1 or CHARACTER_MAXIMUM_LENGTH >= $MinLength)"}) $(if($MaxLength){" and (CHARACTER_MAXIMUM_LENGTH = -1 or CHARACTER_MAXIMUM_LENGTH >= $MaxLength)"}) "@} byte {@" where DATA_TYPE in ('tinyint') "@} int {@" where DATA_TYPE in ('int') "@} long {@" where (DATA_TYPE = 'bigint' or (DATA_TYPE in ('numeric','decimal') and NUMERIC_SCALE = 0)) "@} decimal {@" where DATA_TYPE in ('money','smallmoney') "@} {$_ -in 'float','double'} {@" where DATA_TYPE in ('float','real','numeric','decimal') "@} date {@" where DATA_TYPE in ('date','datetime','datetime2','datetimeoffset','smalldatetime') "@} datetime {@" where DATA_TYPE in ('datetime','datetime2','datetimeoffset','smalldatetime') "@} time {@" where DATA_TYPE in ('time') "@} default {@" where 1 = 1 "@} } if($IncludeSchemata) { $colssql += Format-LikeCondition TABLE_SCHEMA $IncludeSchemata } if($ExcludeSchemata) { $colssql += Format-LikeCondition TABLE_SCHEMA $ExcludeSchemata -Not } if($IncludeTables) { $colssql += Format-LikeCondition TABLE_NAME $IncludeTables } if($ExcludeTables) { $colssql += Format-LikeCondition TABLE_NAME $ExcludeTables -Not } if($IncludeColumns) { $colssql += Format-LikeCondition COLUMN_NAME $IncludeColumns } if($ExcludeColumns) { $colssql += Format-LikeCondition COLUMN_NAME $ExcludeColumns -Not } $colssql += ' order by TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;' Write-Debug "Schema Query:`n$colssql" Invoke-DbaQuery -Query $colssql -As PSObject } function Find-DbIndexes { <# .SYNOPSIS Returns indexes using a column with the given name. .OUTPUTS System.Management.Automation.PSCustomObject with these properties: * SchemaName * TableName * IndexName * IndexOrdinal * IsUnique * IsClustered * IsDisabled * ColumnsInIndex .FUNCTIONALITY Database .LINK https://dbatools.io/ .LINK https://docs.microsoft.com/sql/relational-databases/system-catalog-views/sys-index-columns-transact-sql .EXAMPLE Find-DbIndexes -SqlInstance '(localdb)\ProjectsV13' -Database AdventureWorks2014 -ColumnName ErrorLogID SchemaName : dbo TableName : ErrorLog IndexName : PK_ErrorLog_ErrorLogID IndexOrdinal : 1 IsUnique : 1 IsClustered : 1 IsDisabled : 0 ColumnsInIndex : 1 #> [CmdletBinding()][OutputType([Management.Automation.PSCustomObject])] Param( # The server to use, by name or constructed via Connect-DbaInstance. [Parameter(Position=0,Mandatory=$true)][Alias('Parent','ServerInstance')][DbaInstanceParameter] $SqlInstance, # The the database to connect to on the server. [Parameter(Position=1,Mandatory=$true)][Alias('Name')][string] $Database, # The column name to search for. [Parameter(Position=2,Mandatory=$true)][Alias('ColName')][string]$ColumnName ) Use-DbInstance Invoke-DbaQuery -Query @" select object_schema_name(i.object_id) SchemaName, object_name(i.object_id) TableName, i.name IndexName, ic.index_column_id IndexOrdinal, indexproperty(i.object_id,i.name,'IsUnique') IsUnique, indexproperty(i.object_id,i.name,'IsClustered') IsClustered, indexproperty(i.object_id,i.name,'IsDisabled') IsDisabled, (select count(*) from sys.index_columns c where c.object_id = i.object_id and c.index_id = i.index_id) ColumnsInIndex from sys.index_columns ic join sys.indexes i on ic.object_id = i.object_id and ic.index_id = i.index_id where col_name(ic.object_id,ic.column_id) = '$($ColumnName -replace "'","''")' order by TableName, IndexName; "@ -As PSObject } function Measure-DbColumn { <# .SYNOPSIS Provides statistics about SQL Server column data. .INPUTS Microsoft.SqlServer.Management.Smo.Column to calculate statistics for, or Microsoft.SqlServer.Management.Smo.Table to select a column from by name. .OUTPUTS System.Management.Automation.PSCustomObject that describes the column: * ColumnName * SqlType * NullValues * IsUnique * UniqueValues * MinimumValue * MaximumValue * MeanAverage * ModeAverage * Variance * StandardDeviation * additonal properties, depending on type .FUNCTIONALITY Database .LINK https://www.powershellgallery.com/packages/SqlServer/ .LINK https://dbatools.io/ .LINK https://wikipedia.org/wiki/Windows1252 .EXAMPLE $table = Get-DbaDbTable SqlServerName -Database DbName -Table TableName; Measure-DbColumn $table.Columns['record_id'] ColumnName : record_id SqlType : int NullValues : 0 IsUnique : True UniqueValues : 43 MinimumValue : 2 MaximumValue : 56 MeanAverage : 28 ModeAverage : 28 Variance : 290.330011074197 StandardDeviation : 17.0390730696889 .EXAMPLE Get-DbaDbTable SqlServerName -Database DbName -Table TableName |Measure-DbColumn surname ColumnName : surname SqlType : varchar(40) NullValues : 0 IsUnique : False UniqueValues : 72281 MinimumValue : AARONSON MaximumValue : ZYKOWSKI MostCommonValue : SMITH MininumLength : 1 MaximumLength : 40 HasLeadingSpaces : True HasTrailingSpaces : False HasControlChars : False HasWindows1252 : False HasUnicode : False HasNonAscii7 : False HasNonAlphanumeric : True .EXAMPLE Get-DbaDbTable '(localdb)\ProjectsV13' -database AdventureWorks2016 -Table Sales.SalesOrderHeader |Measure-DbColumn OrderDate ColumnName : OrderDate SqlType : datetime Values : 31465 NullValues : 0 IsUnique : False IsDateOnly : True DateOnlyValues : 31465 DateTimeValues : 0 UniqueValues : 1124 MostCommonValue : 03/31/2014 00:00:00 MinimumValue : 05/31/2011 00:00:00 MaximumValue : 06/30/2014 00:00:00 ModeAverage : 03/31/2014 00:00:00 MeanYear : 2013 ModeYear : 2013 MeanMonth : January ModeMonth : May MeanDayOfWeek : Thursday ModeDayOfWeek : Monday MeanDayOfMonth : 16 Sunday : 4444 Monday : 4875 Tuesday : 4482 Wednesday : 4591 Thursday : 4346 Friday : 4244 Saturday : 4483 January : 2877 Febuary : 2300 March : 3144 April : 2812 May : 3175 June : 2189 July : 2356 August : 2324 September : 2300 October : 2616 November : 2716 December : 2656 #> [CmdletBinding(ConfirmImpact='Medium')][OutputType([Management.Automation.PSCustomObject])] Param( # An SMO column object associated to the database column to examine. [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='Column')] [Microsoft.SqlServer.Management.Smo.Column] $Column, # The name of the column to examine in the table associated with the SMO Table object. [Parameter(Position=0,Mandatory=$true,ParameterSetName='ColumnName')][string] $ColumnName, # An SMO table object associated to the database to examine. [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='ColumnName')] [Microsoft.SqlServer.Management.Smo.Table] $Table, <# Conditions to be provided as a SQL WHERE clause to filter the column values to examine. Useful for databases that implement "soft deletes" as specific field values. #> [string] $Condition ) Begin { $SOQ = @' select '{2}' ColumnName, '{3}' SqlType, '@ $EOQ = if(!$Condition) {' from [{0}].[{1}];'} else {" from [{0}].[{1}] where $Condition ;"} $query = @{ Numeric = @" with TopValues as ( select top 1 with ties [{2}] value, count(*) # from [{0}].[{1}] group by [{2}] order by # desc ), MedianValue as ( select max(value) value from (select top 50 percent [{2}] value from [{0}].[{1}] order by value) a union select min(value) from (select top 50 percent [{2}] value from [{0}].[{1}] order by value desc) b ) $SOQ count([{2}]) [Values], sum(case when [{2}] is null then 1 else 0 end) NullValues, cast(case when count(*) = count(distinct [{2}]) then 1 else 0 end as bit) IsUnique, count(distinct [{2}]) UniqueValues, min([{2}]) MinimumValue, max([{2}]) MaximumValue, avg(cast([{2}] as real)) MeanAverage, (select avg(cast(value as real)) from MedianValue) MedianAverage, (select avg(cast(value as real)) from TopValues) ModeAverage, var([{2}]) Variance, stdev([{2}]) StandardDeviation $EOQ "@ DateTime = @" with TopValues as ( select top 1 [{2}] value, count(*) # from [{0}].[{1}] group by [{2}] order by # desc ), MedianValue as ( select max(a.value) value from (select top 50 percent [{2}] value from [{0}].[{1}] order by value) a union select min(b.value) from (select top 50 percent [{2}] value from [{0}].[{1}] order by value desc) b ), DateOnlyCount as ( select count(*) # from [{0}].[{1}] where [{2}] = cast([{2}] as date) ), TopYears as ( select top 1 Year([{2}]) [year], count(*) # from [{0}].[{1}] group by Year([{2}]) order by # desc ), TopMonths as ( select top 1 datename(month,[{2}]) [month], count(*) # from [{0}].[{1}] group by datename(month,[{2}]) order by # desc ), TopDaysOfWeek as ( select top 1 datename(dw,[{2}]) [dayofweek], count(*) # from [{0}].[{1}] group by datename(dw,[{2}]) order by # desc ), TopDays as ( select top 1 Day([{2}]) [day], count(*) # from [{0}].[{1}] group by Day([{2}]) order by # desc ) $SOQ count([{2}]) [Values], sum(case when [{2}] is null then 1 else 0 end) NullValues, cast(case when count(*) = count(distinct [{2}]) then 1 else 0 end as bit) IsUnique, cast(case count([{2}]) when (select # from DateOnlyCount) then 1 else 0 end as bit) IsDateOnly, (select # from DateOnlyCount) DateOnlyValues, count([{2}]) - (select # from DateOnlyCount) DateTimeValues, count(distinct [{2}]) UniqueValues, (select top 1 value from TopValues) MostCommonValue, min([{2}]) MinimumValue, max([{2}]) MaximumValue, --dateadd(seconds,'1970-01-01',avg(cast(datediff(second,'1970-01-01',[{2}]) as real))) MeanAverage, --(select dateadd(seconds,avg([{2}]),'1970-01-01') from TopValues) MedianAverage, (select value from TopValues) ModeAverage, cast(avg(cast(Year([{2}]) as real)) as int) MeanYear, (select [year] from TopYears) ModeYear, datename(month,avg(Month([{2}]))) MeanMonth, (select [month] from TopMonths) ModeMonth, datename(dw,avg(datepart(dw,[{2}]))) MeanDayOfWeek, (select [dayofweek] from TopDaysOfWeek) ModeDayOfWeek, avg(Day([{2}])) MeanDayOfMonth, sum(case datepart(dw,[{2}]) when 1 then 1 end) Sunday, sum(case datepart(dw,[{2}]) when 2 then 1 end) Monday, sum(case datepart(dw,[{2}]) when 3 then 1 end) Tuesday, sum(case datepart(dw,[{2}]) when 4 then 1 end) Wednesday, sum(case datepart(dw,[{2}]) when 5 then 1 end) Thursday, sum(case datepart(dw,[{2}]) when 6 then 1 end) Friday, sum(case datepart(dw,[{2}]) when 7 then 1 end) Saturday, sum(case datepart(m,[{2}]) when 1 then 1 end) January, sum(case datepart(m,[{2}]) when 2 then 1 end) Febuary, sum(case datepart(m,[{2}]) when 3 then 1 end) March, sum(case datepart(m,[{2}]) when 4 then 1 end) April, sum(case datepart(m,[{2}]) when 5 then 1 end) May, sum(case datepart(m,[{2}]) when 6 then 1 end) June, sum(case datepart(m,[{2}]) when 7 then 1 end) July, sum(case datepart(m,[{2}]) when 8 then 1 end) August, sum(case datepart(m,[{2}]) when 9 then 1 end) September, sum(case datepart(m,[{2}]) when 10 then 1 end) October, sum(case datepart(m,[{2}]) when 11 then 1 end) November, sum(case datepart(m,[{2}]) when 12 then 1 end) December $EOQ "@ Temporal = @" with TopValues as ( select top 1 [{2}] value, count(*) # from [{0}].[{1}] group by [{2}] order by # desc ) $SOQ count([{2}]) [Values], sum(case when [{2}] is null then 1 else 0 end) NullValues, cast(case when count(*) = count(distinct [{2}]) then 1 else 0 end as bit) IsUnique, count(distinct [{2}]) UniqueValues, (select top 1 value from TopValues) MostCommonValue, min([{2}]) MinimumValue, max([{2}]) MaximumValue $EOQ "@ String = @" with TopValues as ( select top 1 [{2}] value, count(*) # from [{0}].[{1}] group by [{2}] order by # desc ) $SOQ count([{2}]) [Values], sum(case when [{2}] is null then 1 else 0 end) NullValues, cast(case when count(*) = count(distinct [{2}]) then 1 else 0 end as bit) IsUnique, count(distinct [{2}]) UniqueValues, min([{2}]) MinimumValue, max([{2}]) MaximumValue, (select top 1 value from TopValues) MostCommonValue, min(len([{2}])) MininumLength, max(len([{2}])) MaximumLength, cast(case when exists (select top 1 * from [{0}].[{1}] where [{2}] <> ltrim([{2}])) then 1 else 0 end as bit) HasLeadingSpaces, cast(case when exists (select top 1 * from [{0}].[{1}] where [{2}] <> rtrim([{2}])) then 1 else 0 end as bit) HasTrailingSpaces, cast(case when exists (select top 1 * from [{0}].[{1}] where [{2}] like '%'+char(0x09)+'%') then 1 else 0 end as bit) HasTabs, cast(case when exists (select top 1 * from [{0}].[{1}] where [{2}] like '%[' + char(0x00) + '-' + char(0x1F) + ']%') then 1 else 0 end as bit) HasControlChars, cast(case when exists (select top 1 * from [{0}].[{1}] where [{2}] like '%[€‚ƒ„…†‡ˆ‰Š‹ŒŽ‘’“”•–—˜™š›œžŸ]%') then 1 else 0 end as bit) HasWindows1252Conflicts, cast(case when exists (select top 1 * from [{0}].[{1}] where [{2}] collate SQL_Latin1_General_CP437_BIN <> cast([{2}] as varchar(max)) collate SQL_Latin1_General_CP437_BIN) then 1 else 0 end as bit) HasUnicode, cast(case when exists (select top 1 * from [{0}].[{1}] where [{2}] collate SQL_Latin1_General_CP437_BIN like '%[^' + char(0x00) + '-~]%') then 1 else 0 end as bit) HasNonAscii7, cast(case when exists (select top 1 * from [{0}].[{1}] where ltrim(rtrim([{2}])) like '%[^0-9A-Za-z_]%') then 1 else 0 end as bit) HasNonAlphanumeric $EOQ "@ VariableLength = @" $SOQ count([{2}]) [Values], sum(case when [{2}] is null then 1 else 0 end) NullValues, cast(case when count(*) = count(distinct [{2}]) then 1 else 0 end as bit) IsUnique, count(distinct [{2}]) UniqueValues, min(len([{2}])) MininumLength, max(len([{2}])) MaximumLength $EOQ "@ Other = @" $SOQ count([{2}]) Values, sum(case when [{2}] is null then 1 else 0 end) NullValues $EOQ "@ } $typeinfo = @{ bigint = @('Numeric','{0}') binary = @('VariableLength','{0}({1})') bit = @('Other','{0}') char = @('String','{0}({1})') cursor = @('Other','{0}') date = @('Temporal','{0}') datetime = @('DateTime','{0}') datetime2 = @('DateTime','{0}({3})') datetimeoffset = @('DateTime','{0}({3})') decimal = @('Numeric','{0}({2},{3})') float = @('Numeric','{0}') geography = @('Other','{0}') geometry = @('Other','{0}') hierarchyid = @('Other','{0}') image = @('Other','{0}') int = @('Numeric','{0}') money = @('Numeric','{0}') nchar = @('String','{0}({1})') ntext = @('Other','{0}') numeric = @('Numeric','{0}({2},{3})') nvarchar = @('String','{0}({1:0;max})') real = @('Numeric','{0}') rowversion = @('Other','{0}') smalldatetime = @('DateTime','{0}') smallint = @('Numeric','{0}') smallmoney = @('Numeric','{0}') sql_variant = @('VariableLength','{0}') table = @('Other','{0}') text = @('Other','{0}') time = @('Temporal','{0}') tinyint = @('Numeric','{0}') uniqueidentifier = @('Other','{0}') varbinary = @('VariableLength','{0}({1:0;max})') varchar = @('String','{0}({1:0;max})') xml = @('VariableLength','{0}') } } Process { if($Column) {$ColumnName = $Column.Name} else { $Column = $Table.Columns[$ColumnName] if(!$Column) {throw "Column '$ColumnName' not found in table '$($Table.Name)'"} } $datatype = $Column.DataType $querytype,$typefmt = $typeinfo[$datatype.Name] $table = $Column.Parent $fqtn = "$($table.Parent.Parent.Name).$($table.Parent.Name).$($table.Name)" $sql = $query[$querytype] -f $table.Schema,$table.Name,$ColumnName, ($typefmt -f $datatype.Name,$datatype.MaximumLength,$datatype.NumericPrecision,$datatype.NumericScale) Write-Verbose "SQL: $sql" if($PSCmdlet.ShouldProcess("column $fqtn.$ColumnName","query $($table.RowCount) rows")) { Invoke-DbaQuery -SqlInstance $table.Parent.Parent -Database $table.Parent.Name -Query $sql -As PSObject } } } function Measure-DbColumnValues { <# .SYNOPSIS Provides sorted counts of SQL Server column values. .INPUTS Microsoft.SqlServer.Management.Smo.Column to calculate statistics for, or Microsoft.SqlServer.Management.Smo.Table to select a column from by name. .OUTPUTS System.Management.Automation.PSCustomObject that describes each counted value. .FUNCTIONALITY Database .LINK https://www.powershellgallery.com/packages/SqlServer/ .LINK https://dbatools.io/ #> [CmdletBinding(ConfirmImpact='Medium')][OutputType([Management.Automation.PSCustomObject])] Param( # An SMO column object associated to the database column to examine. [Parameter(Position=0,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='Column')] [Microsoft.SqlServer.Management.Smo.Column] $Column, # The name of the column to examine in the table associated with the SMO Table object. [Parameter(Position=0,Mandatory=$true,ParameterSetName='ColumnName')][string] $ColumnName, # An SMO table object associated to the database to examine. [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true,ParameterSetName='ColumnName')] [Microsoft.SqlServer.Management.Smo.Table] $Table, <# Conditions to be provided as a SQL WHERE clause to filter the column values to examine. Useful for databases that implement "soft deletes" as specific field values. #> [string] $Condition, # Excludes values with fewer than this number of occurrences. [int] $MinimumCount ) Begin { $query = @" select [{2}] [Value], count(*) [Count] from [{0}].[{1}] $(if($Condition){" where $Condition"}) group by [{2}] $(if($MinimumCount){"having count(*) > $MinimumCount"}) order by [Count] desc; "@ } Process { if($Column) {$ColumnName = $Column.Name} else { $Column = $Table.Columns[$ColumnName] if(!$Column) {throw "Column '$ColumnName' not found in table '$($Table.Name)'"} } $table = $Column.Parent $fqtn = "$($table.Parent.Parent.Name).$($table.Parent.Name).$($table.Name)" $sql = $query -f $table.Schema,$table.Name,$ColumnName Write-Verbose "SQL: $sql" if($PSCmdlet.ShouldProcess("column $fqtn.$ColumnName","query $($table.RowCount) rows")) { Invoke-DbaQuery -SqlInstance $table.Parent.Parent -Database $table.Parent.Name -Query $sql -As PSObject } } } function Measure-DbTable { <# .SYNOPSIS Provides frequency details about SQL Server table data. .INPUTS Microsoft.SqlServer.Management.Smo.Table to analyze. .OUTPUTS System.Management.Automation.PSCustomObject that describes each table column. .FUNCTIONALITY Database .LINK https://www.powershellgallery.com/packages/SqlServer/ .LINK https://dbatools.io/ .EXAMPLE Get-DbaDbTable -sqli '(localdb)\ProjectsV13' -dat AdventureWorks2016 -tab Production.Product |Measure-DbTable #TableName : [Production].[Product] #RowCount : 504 ProductID : unique, 0 nulls, 504 values: 1 .. 999 Name : unique, 0 nulls, 504 values: Adjustable Race .. Women's Tights, S ProductNumber : unique, 0 nulls, 504 values: AR-5381 .. WB-H098 MakeFlag : bit: 0 nulls, 239 ones, 265 zeros FinishedGoodsFlag : bit: 0 nulls, 295 ones, 209 zeros Color : 248 nulls, 9 values: Black .. Yellow SafetyStockLevel : 0 nulls, 6 values: 4 .. 1000 ReorderPoint : 0 nulls, 6 values: 3 .. 750 StandardCost : 0 nulls, 114 values: 0.00 .. 2171.29 ListPrice : 0 nulls, 103 values: 0.00 .. 3578.27 Size : 293 nulls, 18 values: 38 .. XL SizeUnitMeasureCode : CM WeightUnitMeasureCode : 299 nulls, 2 values: G .. LB Weight : 299 nulls, 127 values: 2.12 .. 1050.00 DaysToManufacture : 0 nulls, 4 values: 0 .. 4 ProductLine : 226 nulls, 4 values: M .. T Class : 257 nulls, 3 values: H .. M Style : 293 nulls, 3 values: M .. W ProductSubcategoryID : 209 nulls, 37 values: 1 .. 37 ProductModelID : 209 nulls, 119 values: 1 .. 128 SellStartDate : 0 nulls, 4 values: Apr 30 2008 12:00AM .. May 30 2013 12:00AM SellEndDate : 406 nulls, 2 values: May 29 2012 12:00AM .. May 29 2013 12:00AM DiscontinuedDate : null rowguid : unique, 0 nulls, 504 values: 7A927632-99A4-4F24-ADCE-0062D2D113D9 .. B9EDE243-A6F4-4629-B1D4-FFE1AEDC6DE7 ModifiedDate : 0 nulls, 2 values: Feb 8 2014 10:01AM .. Feb 8 2014 10:03AM #> [CmdletBinding(ConfirmImpact='Medium')][OutputType([Management.Automation.PSCustomObject])] Param( # An SMO table object associated to the database to examine. [Parameter(Position=1,Mandatory=$true,ValueFromPipeline=$true)] [Microsoft.SqlServer.Management.Smo.Table] $Table, <# Conditions to be provided as a SQL WHERE clause to filter the record values to examine. Useful for databases that implement "soft deletes" as specific field values. #> [string] $Condition ) Begin { function Format-ColumnRange([string]$colname) {@" case count([$colname]) when count(*) then 'not null, ' + cast(count(distinct [$colname]) as varchar(max)) + ' values: ' + cast(min([$colname]) as varchar(max)) + ' .. ' + cast(max([$colname]) as varchar(max)) when 0 then 'null' else cast(count(*) - count([$colname]) as varchar(max)) + ' nulls, ' + cast(count(distinct [$colname]) as varchar(max)) + ' values: ' + cast(min([$colname]) as varchar(max)) + ' .. ' + cast(max([$colname]) as varchar(max)) end "@} filter Format-ColumnCount { $colname = $_.Name switch($_.DataType.Name) { {$_ -in 'bit','Flag'} {@" , 'bit: ' + cast(count(*) - count([$colname]) as varchar(max)) + ' nulls, ' + cast(sum(cast([$colname] as int)) as varchar(max)) + ' ones, ' + cast(count([$colname]) - sum(cast([$colname] as int)) as varchar(max)) + ' zeros' [$colname] "@} {$_ -in 'text','ntext','image'} {@" , 'text/image: ' + case sum(case when [$colname] is null then 1 else 0 end) when 0 then 'not null' when count(*) then 'null' else cast(count(*) - sum(case when [$colname] is null then 1 else 0 end) as varchar(max)) + ' nulls, ' + cast(sum(case when [$colname] is null then 1 else 0 end) as varchar(max)) + ' values' end [$colname] "@} default {@" , case count(distinct [$colname]) when 0 then 'null' when 1 then cast(min([$colname]) as varchar(max)) when count(*) then 'unique, ' + $(Format-ColumnRange $colname) when count([$colname]) then 'nullable unique (no duplicates), ' + $(Format-ColumnRange $colname) else $(Format-ColumnRange $colname) end [$colname] "@} } } $SOQ = "select '[{0}].[{1}]' #TableName, count(*) #RowCount" $EOQ = if(!$Condition) {' from [{0}].[{1}];'} else {" from [{0}].[{1}] where $Condition ;"} } Process { $sql = "$SOQ $($Table.Columns |Format-ColumnCount) $EOQ" -f $Table.Schema,$Table.Name Write-Verbose "SQL: $sql" if($PSCmdlet.ShouldProcess("table $Table","query $($table.RowCount) rows")) { Invoke-DbaQuery -SqlInstance $table.Parent.Parent -Database $table.Parent.Name -Query $sql -As PSObject } } } function New-DbProviderObject { <# .SYNOPSIS Create a common database object. .INPUTS System.String to initialize the database object. .OUTPUTS System.Data.Common.DbCommand (e.g. System.Data.SqlClient.SqlCommand) or System.Data.Common.DbConnection (e.g. System.Data.SqlClient.SqlConnection) or System.Data.Common.DbConnectionStringBuilder (e.g. System.Data.SqlClient.SqlConnectionStringBuilder), as requested. .FUNCTIONALITY Database .LINK https://msdn.microsoft.com/library/system.data.common.dbproviderfactories.aspx .EXAMPLE New-DbProviderObject ConnectionStringBuilder 'Server=(localdb)\ProjectsV13;Database=AdventureWorks;Integrated Security=SSPI;Encrypt=True' Key Value --- ----- Data Source (localdb)\ProjectsV13 Initial Catalog AdventureWorks Integrated Security True Encrypt True .EXAMPLE $conn = New-DbProviderObject Connection $connstr -Open ($conn contains an open DbConnection object.) .EXAMPLE $cmd = New-DbProviderObject Command -ConnectionString $connstr -Provider Odbc -StoredProcedure -OpenConnection ($cmd contains an OdbcCommand with a CommandType of StoredProcedure and an open connection to $connstr.) #> [CmdletBinding()][OutputType([Data.Common.DbCommand])] [OutputType([Data.Common.DbConnection])][OutputType([Data.Common.DbConnectionStringBuilder])] Param( # The type of object to create. [ValidateSet('Command','Connection','ConnectionStringBuilder')] [Parameter(Mandatory=$true,Position=0)][string] $TypeName, <# A value to initialize the object with, such as CommandText for a Command object, or a ConnectionString for a Connection or ConnectionStringBuilder. #> [Parameter(Position=2,ValueFromPipeline=$true)][Alias('Value')][string] $InitialValue, # The DbProviderFactory subclass to use to create the object. [ValidateSet('Odbc','OleDb','Oracle','Sql')][string] $Provider = 'Sql', <# A connection string to use (when creating a Command object). No connection will be made if not specified. #> [Parameter(Position=3)][Alias('CS')][string] $ConnectionString, <# Sets the CommandType property of a Command object to StoredProcedure. Ignored for other objects. #> [switch] $StoredProcedure, # Opens the Connection object (or Command connection) if an InitialValue was provided, ignored otherwise. [switch] $OpenConnection ) Process { $factory = switch($Provider) { Odbc {[Data.Odbc.OdbcFactory]::Instance} OleDb {[Data.OleDb.OleDbFactory]::Instance} Oracle {[Data.OracleClient.OracleClientFactory]::Instance} Sql {[Data.SqlClient.SqlClientFactory]::Instance} } $value = switch($TypeName) { Command {$factory.CreateCommand()} Connection {$factory.CreateConnection()} ConnectionStringBuilder {$factory.CreateConnectionStringBuilder()} } if($InitialValue) { switch($TypeName) { Command { $value.CommandText = $InitialValue } Connection { $value.ConnectionString = $InitialValue if($OpenConnection) {$value.Open()} } ConnectionStringBuilder { # PowerShell must use the method form $value.set_ConnectionString($InitialValue) } } } if($TypeName -eq 'Command') { if($StoredProcedure) {$obj.CommandType = 'StoredProcedure'} if($ConnectionString) {$obj.Connection = New-DbProviderObject Connection $ConnectionString -Provider:$Provider -OpenConnection:$OpenConnection} } return $value } } function Repair-DatabaseConstraintNames { <# .SYNOPSIS Finds database constraints with system-generated names and gives them deterministic names. .FUNCTIONALITY Database .LINK Use-SqlcmdParams .LINK https://dbatools.io/ .LINK https://www.databasejournal.com/features/mssql/article.php/1570801/Beware-of-the-System-Generated-Constraint-Name.htm .EXAMPLE Repair-DatabaseConstraintNames SqlServerName DatabaseName -Update WARNING: Renamed 10 defaults #> [CmdletBinding(SupportsShouldProcess=$true)][OutputType([void])] Param( # The server to use, by name or constructed via Connect-DbaInstance. [Parameter(Position=0,Mandatory=$true)][Alias('Parent','ServerInstance')][DbaInstanceParameter] $SqlInstance, # The the database to connect to on the server. [Parameter(Position=1,Mandatory=$true)][Alias('Name')][string] $Database, # Update the database when present, otherwise simply outputs the changes as script. [switch] $Update ) Use-DbInstance function Repair-DefaultName { Resolve-QueryResult -Action 'Renam{0:e;ing;ed} {1} defaults' -Query @" select 'if object_id(''' + quotename(schema_name(schema_id)) +'.'+ quotename(name) +''') is not null exec sp_rename '''+quotename(schema_name(schema_id))+'.'+quotename(name) +''', ''DF_'+object_name(parent_object_id)+'_'+col_name(parent_object_id,parent_column_id) +''', ''OBJECT'';' [command] from sys.default_constraints where name like 'DF._._%' escape '.' and name <> 'DF_'+object_name(parent_object_id)+'_'+col_name(parent_object_id,parent_column_id) and objectproperty(parent_object_id,'IsUserTable') = 1 -- excludes 'sys' schema, &c and objectproperty(parent_object_id,'IsMsShipped') = 0 -- excludes dtproperties, &c and parent_object_id not in (select major_id from sys.extended_properties where class = 1 and minor_id = 0 and name = 'microsoft_database_tools_support'); -- excludes sysdiagrams, &c "@ } function Repair-PrimaryKeyName { Resolve-QueryResult -Action 'Renam{0:e;ing;ed} {1} primary keys' -Query @" select 'if object_id(''' + quotename(schema_name(schema_id)) +'.'+ quotename(name) +''') is not null exec sp_rename '''+quotename(schema_name(schema_id))+'.'+quotename(name) +''', '''+'PK_'+object_name(parent_object_id)+''', ''OBJECT'';' command from sys.key_constraints where name like 'PK._._%' escape '.' and name <> 'PK_'+object_name(parent_object_id) and objectproperty(parent_object_id,'IsUserTable') = 1 -- excludes 'sys' schema, &c and objectproperty(parent_object_id,'IsMsShipped') = 0 -- excludes dtproperties, &c and parent_object_id not in (select major_id from sys.extended_properties where class = 1 and minor_id = 0 and name = 'microsoft_database_tools_support'); -- excludes sysdiagrams, &c "@ } function Repair-ForeignKeyName { #TODO: Mitigate possible deterministic naming collisions. Resolve-QueryResult -Action 'Renam{0:e;ing;ed} {1} foreign keys' -Query @" select 'if object_id(''' + quotename(schema_name(schema_id)) +'.'+ quotename(name) +''') is not null exec sp_rename '''+quotename(schema_name(schema_id))+'.'+quotename(name) +''', '''+'FK_'+object_name(parent_object_id)+'_'+object_name(referenced_object_id)+''', ''OBJECT'';' command from sys.foreign_keys where name like 'FK._._%' escape '.' and name <> 'FK_'+object_name(parent_object_id) and objectproperty(parent_object_id,'IsUserTable') = 1 -- excludes 'sys' schema, &c and objectproperty(parent_object_id,'IsMsShipped') = 0 -- excludes dtproperties, &c and parent_object_id not in (select major_id from sys.extended_properties where class = 1 and minor_id = 0 and name = 'microsoft_database_tools_support'); -- excludes sysdiagrams, &c "@ } Repair-DefaultName Repair-PrimaryKeyName Repair-ForeignKeyName } function Repair-DatabaseUntrustedConstraints { <# .SYNOPSIS Finds database constraints that have been incompletely re-enabled. .FUNCTIONALITY Database .LINK Use-SqlcmdParams .LINK https://dbatools.io/ .LINK https://www.brentozar.com/blitz/foreign-key-trusted/ .EXAMPLE Repair-DatabaseUntrustedConstraints SqlServerName DatabaseName -Update WARNING: Checked 2 constraints #> [CmdletBinding(SupportsShouldProcess=$true)][OutputType([void])] Param( # The server to use, by name or constructed via Connect-DbaInstance. [Parameter(Position=0,Mandatory=$true)][Alias('Parent','ServerInstance')][DbaInstanceParameter] $SqlInstance, # The the database to connect to on the server. [Parameter(Position=1,Mandatory=$true)][Alias('Name')][string] $Database, # Update the database when present, otherwise simply outputs the changes as script. [switch] $Update ) Use-DbInstance function Repair-DefaultName { Resolve-QueryResult -Action 'Check{0:;ing;ed} {1} constraints' -Query @" select 'if exists (select * from sys.foreign_keys where object_id = object_id(''' + quotename(schema_name(schema_id)) + '.' + quotename(object_name(object_id)) + ''') and is_not_trusted = 1) alter table ' + quotename(object_schema_name(parent_object_id)) + '.' + quotename(object_name(parent_object_id)) + ' with check check constraint ' + quotename(name) + '; -- FK' command from sys.foreign_keys where is_not_trusted = 1 and is_not_for_replication = 0 and is_disabled = 0 union all select 'if exists (select * from sys.foreign_keys where object_id = object_id(''' + quotename(schema_name(schema_id)) + '.' + quotename(object_name(object_id)) + ''') and is_not_trusted = 1) alter table ' + quotename(object_schema_name(parent_object_id)) + '.' + quotename(object_name(parent_object_id)) + ' with check check constraint ' + quotename(name) + ';' command from sys.check_constraints where is_not_trusted = 1 and is_not_for_replication = 0 and is_disabled = 0; "@ } Repair-DefaultName } function Send-SqlReport { <# .SYNOPSIS Execute a SQL statement and email the results. .FUNCTIONALITY Database .LINK https://dbatools.io/ .LINK Send-MailMessage #> [CmdletBinding(SupportsShouldProcess=$true,ConfirmImpact='None')][OutputType([void])] Param( # The email subject. [Parameter(Position=0,Mandatory=$true)][string]$Subject, # The email address(es) to send the results to. [Parameter(Position=1,Mandatory=$true)][string[]]$To, # The SQL statement to execute. [Parameter(Position=2,Mandatory=$true)][string]$Sql, # The server to use, by name or constructed via Connect-DbaInstance. [Parameter(Position=0,Mandatory=$true)][Alias('Parent','ServerInstance')][DbaInstanceParameter] $SqlInstance, # The the database to connect to on the server. [Parameter(Position=1,Mandatory=$true)][Alias('Name')][string] $Database, # The subject line for the email when no data is returned. [string]$EmptySubject, # The from address to use for the email. The default is to use $PSEmailServer. [string]$From, # The optional table caption to add. [string]$Caption, <# A UNC path to a .csv or .tsv file writable by the script and readable by the email recipient to output the data to, which will be linked in the email rather than included in the email body. Supports a format template for the current date and time (e.g. {0:yyyyMMddHHmmss}). #> [string]$ReportFile, # The timeout to use for the query, in seconds. The default is 90. [Alias('Timeout')][int]$QueryTimeout= 90, # HTML content to insert into the email before the query results. [string]$PreContent= ' ', # HTML content to insert into the email after the query results. [string]$PostContent= ' ', # The email address(es) to CC the results to. [string[]]$Cc, # The email address(es) to BCC the results to. [string[]]$Bcc, # The priority of the email, one of: High, Low, Normal [Net.Mail.MailPriority]$Priority, <# Indicates that SSL should be used when sending the message. (See the From parameter for an alternate SSL flag.) #> [switch]$UseSsl, # The URL of the Seq server to log to. [uri]$SeqUrl = $PSDefaultParameterValues['Send-SeqEvent:Server'] ) Use-DbInstance if($SeqUrl){Use-SeqServer $SeqUrl} # use the default From host for emails without a host $mailhost = ([Net.Mail.MailAddress]$PSDefaultParameterValues['Send-MailMessage:From']).Host |Out-String if($mailhost) { $To = $To |ForEach-Object { if($_ -like '*@*'){$_}else{"$_@$mailhost"} } # allow username-only emails $Cc = $Cc |ForEach-Object { if($_ -like '*@*'){$_}elseif($_){"$_@$mailhost"} } # allow username-only emails if($Bcc) { $Bcc = $Bcc |ForEach-Object { if($_ -like '*@*'){$_}else{"$_@$mailhost"} } } # allow username-only emails } $Msg = @{ To = $To Subject = $Subject BodyAsHtml = $true SmtpServer = $PSEmailServer } if($From) { $Msg.From= $From } if($Cc) { $Msg.Cc= $Cc } if($Bcc) { $Msg.Bcc= $Bcc } if($Priority) { $Msg.Priority= $Priority } if($UseSsl) { $Msg.UseSsl = $true } try { [psobject[]]$data = Invoke-DbaQuery -Query $Sql -As PSObject -ErrorAction Stop $data |Format-Table |Out-String |Write-Verbose if(!$data -or $data.Length -eq 0) # no rows { Write-Verbose "No rows returned." if($SeqUrl) { Send-SeqEvent 'No rows returned for {Subject}' @{Subject=$Subject} -Level Information } if($EmptySubject) { $Msg.Subject = $EmptySubject; Send-MailMessage @Msg } return } Write-Verbose "$($data.Length) rows returned." if($ReportFile) { # convert the table into a tsv/csv file and link to it $ReportFile = $ReportFile -f (Get-Date) if($ReportFile -like '*.tsv') {$data |Export-Csv $ReportFile -Delimiter "`t" -Encoding UTF8 -NoTypeInformation} else {$data |Export-Csv $ReportFile -Encoding UTF8 -NoTypeInformation} $ReportFile = (Resolve-Path $ReportFile).ProviderPath if(([uri]$ReportFile).IsUnc) { $Msg.Add('Body',@" $PreContent <a href=`"$([Security.SecurityElement]::Escape($ReportFile))`">$([Security.SecurityElement]::Escape((Split-Path $ReportFile -Leaf)))</a> $PostContent "@) } else { $Msg.Add('Body',"$PreContent`n$PostContent") $Msg.Add('Attachments',$ReportFile) } } else { # convert the table into HTML (select away the add'l properties the DataTable adds), add some Outlook 2007-compat CSS, email it $tableFormat = @{OddRowBackground='#EEE'} if($Caption){$tableFormat.Add('Caption',$Caption)} $Msg.Add('Body',($data | ConvertTo-Html -PreContent $PreContent -PostContent $PostContent -Head '<style type="text/css">th,td {padding:2px 1ex 0 2px}</style>' | Format-HtmlDataTable @tableFormat | Out-String)) } if($PSCmdlet.ShouldProcess("Message:`n$(New-Object PSObject -Property $Msg|Format-List|Out-String)`n",'Send message')) { Send-MailMessage @Msg } # splat the arguments hashtable } catch # report problems { Write-Warning $_ if($SeqUrl) { Send-SeqScriptEvent 'Reporting' -InvocationScope 2 } # consciously omitting Cc & Bcc $Msg = @{ To = $To Subject = "$Subject [Error]" BodyAsHtml = $false SmtpServer = $PSEmailServer Body = $_ } if($From) { $Msg.From= $From } if($Priority) { $Msg.Priority= $Priority } if($PSCmdlet.ShouldProcess("Message:`n$(New-Object PSObject -Property $Msg|Format-List|Out-String)`n",'Send message')) { Send-MailMessage @Msg } throw "$_" } } function Test-ConnectionString { <# .SYNOPSIS Test a given connection string and provide details about the connection. .OUTPUTS System.Management.Automation.PSObject containing properties about the connection. .FUNCTIONALITY Database .LINK https://dbatools.io/ .EXAMPLE Test-ConnectionString 'Server=(localdb)\ProjectsV13;Integrated Security=SSPI;Encrypt=True' -Details ServerName : SERVERNAME\LOCALDB#DCCC9EEC AppName : Core Microsoft SqlClient Data Provider LocalRunAsAdmin : False ConnectingAsUser : SERVERNAME\username SqlInstance : (localdb)\ProjectsV13 LocalWindows : 10.0.19045.0 InstanceName : LOCALDB#DCCC9EEC DatabaseName : master AuthType : Windows Authentication Integrated Security : True Data Source : (localdb)\ProjectsV13 ConnectSuccess : True Workstation ID : SERVERNAME AuthScheme : NTLM ComputerName : SERVERNAME Encrypt : True LocalCLR : TcpPort : 1433 LocalPowerShell : 7.3.9 NetBiosName : SERVERNAME Edition : Express Edition (64-bit) IPAddress : 192.168.1.223 ServerTime : 2023-11-10 12:14:09 DomainName : WORKGROUP Server : [(localdb)\ProjectsV13] IsPingable : True LocalEdition : Core Pooling : True LocalDomainUser : False MachineName : SERVERNAME SqlVersion : 13.0.4001 LocalSMOVersion : 17.100.0.0 #> [Diagnostics.CodeAnalysis.SuppressMessageAttribute('PSAvoidUsingConvertToSecureStringWithPlainText','', Justification='The data source is plaintext. SecureString benefits may be in dispute: <https://github.com/dotnet/platform-compat/blob/master/docs/DE0001.md>')] [CmdletBinding()][OutputType([psobject])] Param( [Parameter(Position=0,Mandatory=$true)][string] $ConnectionString, [switch] $Details ) Process { try { if($Details) { $csb = New-DbaConnectionStringBuilder -ConnectionString $ConnectionString $server = Connect-DbaInstance -ConnectionString $ConnectionString $conn = Join-Keys -ReferenceObject (New-Object Collections.Hashtable $csb) ` -InputObject (Test-DbaConnection $csb.DataSource -SkipPSRemoting |ConvertTo-OrderedDictionary) $info = Invoke-DbaQuery -SqlInstance $server -As PSObject -Query @' select @@ServerName [ServerName], db_name() [DatabaseName], serverproperty('ComputerNamePhysicalNetBIOS') [ComputerName], serverproperty('MachineName') [MachineName], serverproperty('InstanceName') [InstanceName], current_timestamp [ServerTime], serverproperty('Edition') [Edition], app_name() [AppName]; '@ |ConvertTo-OrderedDictionary [void] $info.Add('Server', $server) $connInfo = Join-Keys $conn $info if($connInfo.Contains('Password')) {$connInfo['Password'] = ConvertTo-SecureString $connInfo['Password'] -AsPlainText -Force} return [pscustomobject]$connInfo } else { return Invoke-DbaQuery -SqlInstance (Connect-DbaInstance -ConnectionString $ConnectionString) ` -Query 'select cast(1 as bit) Success;' -As SingleValue } } catch {return $false} } } function Use-DbInstance { <# .SYNOPSIS Sets a default dbatools connection, using a caller script's parameter values when available. .FUNCTIONALITY Database .EXAMPLE Use-DbInstance -SqlInstance '(localdb)\ProjectsV13' -Database AdventureWorks2016 Sets a default connection to use for queries. #> [CmdletBinding()] Param( # The server to use, by name or constructed via Connect-DbaInstance. [Parameter(Position=0)][Alias('Parent','ServerInstance')][DbaInstanceParameter] $SqlInstance = $ExecutionContext.SessionState.Module.GetVariableFromCallersModule('PSCmdlet')?.Value?.SessionState?.PSVariable?.Get('SqlInstance'), # The the database to connect to on the server. [Parameter(Position=1)][Alias('Name')][string] $Database = $ExecutionContext.SessionState.Module.GetVariableFromCallersModule('PSCmdlet')?.Value?.SessionState?.PSVariable?.Get('Database'), # Sets a default output type for Invoke-DbaQuery. [ValidateSet('DataSet','DataTable','DataRow','PSObject','PSObjectArray','SingleValue')][string] $As ) #TODO: Add or replace dependencies. Set-ParameterDefault Invoke-DbaQuery SqlInstance $SqlInstance -Scope 1 if($Database) {Set-ParameterDefault Invoke-DbaQuery Database $Database -Scope 1} if($As) {Set-ParameterDefault Invoke-DbaQuery As $As -Scope 1} } Export-ModuleMember -Function Export-MermaidER,Export-TableMerge,Find-DatabaseValue,Find-DbColumn,Find-DbIndexes,Measure-DbColumn,Measure-DbColumnValues,Measure-DbTable,New-DbProviderObject,Repair-DatabaseConstraintNames,Repair-DatabaseUntrustedConstraints,Send-SqlReport,Test-ConnectionString,Use-DbInstance |