Use Case
Occasionally when managing large numbers of SQL Instances and thousands of databases, you have to do some checks prior to running a query against them. One of the ways of ensuring you’re querying the right sort of database is checking if a specific table exists.
This function checks if a table exists by checking the database schema. I’ve seen other functions which query the table itself and check if the result is or isn’t null but this seems more elegant.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| function Test-SQLTableExists | |
| { | |
| param ($Instance,$Database,$TableName) | |
| $Return = $SQL = $dataTable = $null | |
| $sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'$TableName'" | |
| $dataTable = Invoke-Sqlcmd2 -ServerInstance $Instance -Database $Database -Query $sql | |
| if ($dataTable) {$return = $true} | |
| else {$return = $false} | |
| $Return | |
| } |