Edit

Manage Tab Completion with SQL Server PowerShell

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

The SQL Server PowerShell snap-ins introduce three variables ($SqlServerMaximumTabCompletion, $SqlServerMaximumChildItems, and $SqlServerIncludeSystemObjects) to control Windows PowerShell tab completion. Tab completion reduces the amount of typing you must do by returning tables of items whose names start with the string you are typing.

Note

There are two SQL Server PowerShell modules; SqlServer and SQLPS.

The SqlServer module is the current PowerShell module to use.

The SQLPS module is included with the SQL Server installation (for backward compatibility) but is no longer updated.

The SqlServer module contains updated versions of the cmdlets in SQLPS and includes new cmdlets to support the latest SQL features.

Install the SqlServer module from the PowerShell Gallery.

For more information, see SQL Server PowerShell.

With Windows PowerShell tab-completion, when you have typed part of a path or cmdlet name, you can hit the Tab key to get a list of the items whose names match what you have already typed. You can then select the item you want from the list without having to type the rest of the name.

If you are working in a database that has many objects, the tab-completion lists can become large. Some SQL Server object types, such as views, also have large numbers of system objects.

The SQL Server snap-ins introduces three system variables that you can use to control the amount of information presented by tab-completion and Get-ChildItem.

$SqlServerMaximumTabCompletion =** n

Specifies the maximum number of objects to include in a tab-completion list. If you select Tab at a path node having more than n objects, the tab-completion list is truncated at n. n is an integer. 0 is the default setting, and means there is no limit to the number of objects listed.

$SqlServerMaximumChildItems =** n

Specifies the maximum number of objects displayed by Get-ChildItem. If Get-ChildItem is run at a path node having more than n objects, the list is truncated at n. n is an integer. 0 is the default setting, and means there is no limit to the number of objects listed.

$SqlServerIncludeSystemObjects =** { $True | $False }

If $True, system objects are displayed by tab-completion and Get-ChildItem. If $False, no system objects are displayed. The default setting is $False.

Set the SQL Server Tab Completion Variables

For any of the variables you want to change from the default value, set the variable to the new value.

Example (PowerShell)

The following example sets all three variables and lists their settings:

$SqlServerMaximumTabCompletion = 20  
$SqlServerMaximumChildItems = 10  
$SqlServerIncludeSystemObjects = $False  
dir variable:sqlserver*

See Also