Check If SQL Table Exists Using Powershell

Another common request for DBA’s is to search for or validate that certain tables or other objects exist and in what database. This post is a follow-up to our “Check If Database Exists Using Powershell” post where we search look for a database remotely using the “invoke-sqlcmd” commandlet.

Note: This process can be easily modified to look for any object in all databases.

SQL Server Table

Assuming we have the SQL server, instance and database we can use a simple invoke-sqlcmd statement to connect to the instance and DB to query for the SQL table.

# Import-Module -Name SQLServer

$query = "USE [MyDB];
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'TabletoFind')
    SELECT 'Yes' AS Table_Found
ELSE
    SELECT 'No' AS Table_Found"

invoke-sqlcmd -ServerInstance "SQLServer/Instance01" -Query $query 

Check If Multiple Tables Exist In Multiple Databases

Here we use the Invoke-Sqlcmd command and a T-SQL query to check for multiple tables but we also want to search for partial table names too. We define a variable for the SQL instance and then we use an array so we can enter multiple table names for the query to find.

You will also notice one of the array members has the % symbol. Were going to use the ‘LIKE’ T-SQL statement so we can use the exact name and/or partials too.

Get All Databases

We need to collect all of the databases on the SQL instance that are not system DB’s. This is very straight forward, get the database names from the ‘master’ database.

$instance = 'SQLServer/Instance01'

$dbquery = "SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','tempdb','msdb')"

$databases = Invoke-Sqlcmd -ServerInstance $instance -query $dbquery

Array of Tables to Find

The array is our ‘search criteria’for the table (or object) names. We’re using a LIKE statement so this example includes a SQL wild card. Then we create (db tales com) an empty array to store the “table” results for each query along with the database name.

$tablearray = @( 
    'Table1', 
    'Table2', 
    'MyTable%')

$tables = @()

Foreach DB and Table

The last step is a Foreach to go through each DB one at a time and search for the tables. Then we need another foreach to “check” for each table from the $tables array.

Foreach ($db in $databases) {
    # $db.name
    foreach ($table in $tablearray) {
        $tablequery = "USE "+$db.name+"; SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '" +$table+ "'"
        $tables += Invoke-Sqlcmd -ServerInstance $instance -query $tablequery
    }
}
Foreach ($db in $databases) {
    # $db.name
    foreach ($table in $tablearray) {
        $tablequery = "USE "+$db.name+"; SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '" +$table+ "'"
        $tables += Invoke-Sqlcmd -ServerInstance $instance -query $tablequery
    }
}

Check If Table Exists Full Example

## Import-Module -Name SQLServer

$instance = 'SQLServer/Instance01'
$dbquery = "SELECT name FROM master.sys.databases WHERE name NOT IN ('master','model','tempdb','msdb')"
$databases = Invoke-Sqlcmd -ServerInstance $instance -query $dbquery

$tablearray = @( 
    'Table1', 
    'Table2', 
    '%MyTable%')

$tables = @()

Foreach ($db in $databases) {
    # $db.name
    foreach ($table in $tablearray) {
        $tablequery = "USE "+$db.name+"; SELECT DB_NAME(), TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '" +$table+ "'"
        $tables += Invoke-Sqlcmd -ServerInstance $instance -query $tablequery
    }
}
$tables

---