SQL Server – Dropping a Table with PowerShell

powershellsql server

Trying to delete an existing table using powershell

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server(".\CEF_2014_1")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("tempdb")

#drop the Table
$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "listeningport")
$tb.Drop()

but getting an error

PS C:\Users\craig.efrein\Dropbox\Scripts\Powershell> .\drop_table.ps1
Exception calling "Drop" with "0" argument(s): 
"Drop failed for Table 'dbo.listeningport'. "
At C:\Users\craig.efrein\Dropbox\Scripts\Powershell\create_table.ps1:11 char:11
+         $tb.Drop <<<< ()
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException

If I create the table first, and then drop it, that works.

Am I filling the $tb object correctly?

$tb = new-object Microsoft.SqlServer.Management.Smo.Table($db, "listeningport")

As a second question, how do I check for the existence of the table before deleting it in powershell?

Best Answer

A cleaner way to do this would be to get the table object from the database object directly, and then drop it if it returns non-null. This will ONLY run if the table exists.

#drop the Table
$tb = $db.Tables['listeningport']
IF ($tb)
    {$tb.Drop()}

You can use $tb.Tables['tablename', 'schemaname'] to use a non-dbo schema.

When I deal with things like this in SMO my preference is ALWAYS to get the child objects from the parent object directly. It's cleaner, it's more obvious to the next person reading the code what you're doing (you're getting the table directly from that table collection in the db object), and it's very easy to check for existence in a single step.

For your specific issue, it's possible that the table is not created with that name since you're looking at tempdb. Are you sure it's there with that specific name?

Related Question