Remove-DbaDbData View Source Jess Pomfret (@jpomfret), jesspomfret.com Windows, Linux, macOS Synopsis Truncates all user tables in specified databases to remove all data while preserving table structure.
Description Removes all data from user tables by truncating each table in the specified databases. When foreign keys or views exist that would prevent truncation, the function automatically scripts them out, drops them temporarily, performs the truncation, then recreates the objects with their original definitions and permissions.
Remove-DbaDbSequence View Source Adam Lancaster, github.com/lancasteradam Windows, Linux, macOS Synopsis Removes database sequence objects from SQL Server instances.
Description Removes sequence objects from SQL Server databases, freeing up schema namespace and cleaning up unused database objects.
Sequences are commonly used for generating unique numeric values and may need removal during application changes or database cleanup.
When used without a pipeline, the function will first retrieve matching sequences using Get-DbaDbSequence with the provided parameters, then remove them.
Remove-DbaDbTable View Source Andreas Jordan (@JordanOrdix), ordix.de Windows, Linux, macOS Synopsis Drops tables from SQL Server databases with safety controls and detailed status reporting.
Description Permanently removes tables from one or more databases using SQL Server Management Objects (SMO). This function provides a safer alternative to manual DROP TABLE statements by including built-in confirmation prompts and comprehensive error handling. You can specify tables directly by name or pipe table objects from Get-DbaDbTable for more complex filtering scenarios.
Remove-DbaDbTableData View Source Adam Lancaster, github.com/lancasteradam Windows, Linux, macOS Synopsis Performs batch deletion of table data while controlling transaction log growth during large-scale data removal operations.
Description Safely removes large amounts of table data without causing transaction log file growth issues that typically occur with single large DELETE operations. This command implements Aaron Bertrand’s chunked deletion technique (https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes) to break large deletions into manageable batches, preventing log file expansion and blocking issues.
Select-DbaDbSequenceNextValue View Source Adam Lancaster, github.com/lancasteradam Windows, Linux, macOS Synopsis Retrieves and increments the next value from a SQL Server sequence object.
Description Executes a SELECT NEXT VALUE FOR statement against the specified sequence, which increments the sequence counter and returns the next value in the series.
This is useful for testing sequence behavior, troubleshooting sequence issues, or retrieving sequence values for application logic.
Note that calling this function will permanently increment the sequence counter, so it’s not just a read operation.
Set-DbaDbCompression View Source Jason Squires (@js_0505), [email protected] Windows, Linux, macOS Synopsis Applies data compression to SQL Server tables and indexes to reduce storage space and improve performance.
Description Compresses tables, indexes, and heaps across one or more databases using Row, Page, or intelligent recommendations based on Microsoft’s Tiger Team compression analysis. Automatically handles the complex process of analyzing usage patterns, applying appropriate compression types, and rebuilding objects online when possible.
Set-DbaDbSequence View Source Adam Lancaster, github.com/lancasteradam Windows, Linux, macOS Synopsis Modifies properties of existing SQL Server sequence objects
Description Modifies existing SQL Server sequence objects by updating their properties such as increment value, restart point, minimum and maximum bounds, cycling behavior, and cache settings. This function is essential when you need to adjust sequence behavior after deployment, fix increment issues, or optimize performance without recreating the sequence and losing its current state.
Test-DbaDbCompression View Source Jason Squires (@js_0505), [email protected] Windows, Linux, macOS Synopsis Analyzes user tables and indexes to recommend optimal compression settings for storage space reduction.
Description Performs comprehensive compression analysis on user tables and indexes to help DBAs identify storage optimization opportunities. Uses SQL Server’s sp_estimate_data_compression_savings system procedure combined with workload pattern analysis to recommend the most effective compression type for each object.
This function analyzes your database workload patterns (scan vs update ratios) and calculates potential space savings to recommend ROW compression, PAGE compression, or no compression.
Test-DbaIdentityUsage View Source Brandon Abshire, netnerds.net Windows, Linux, macOS Synopsis Analyzes IDENTITY column seed consumption and calculates percentage of available range used.
Description Scans IDENTITY columns across databases to calculate how much of the available seed range has been consumed based on data type limits (tinyint, smallint, int, bigint). This helps DBAs proactively identify tables approaching identity exhaustion before they hit maximum values and cause application failures. The function calculates percentage used by comparing current identity values against theoretical maximums, so you can plan remediation like reseeding or changing data types before problems occur.
Write-DbaDbTableData View Source Chrissy LeMaire (@cl), netnerds.net Windows, Linux, macOS Synopsis Performs high-speed bulk inserts of data into SQL Server tables using SqlBulkCopy.
Description Imports data from various sources (CSV files, DataTables, DataSets, PowerShell objects) into SQL Server tables using SqlBulkCopy for optimal performance. This command handles the heavy lifting of data type conversion from PowerShell to SQL Server, automatically creates missing tables when needed, and provides fine-grained control over bulk copy operations.
dbatools