[Solved] how do I delete a SQL table with a dbo prefix
I recently asked a question in Focal Point asking how - in DataMigrator - to delete or rename tables from a SQL server database. Tsepe gave me the answer I was looking for, by adapting the following DDL script:
ENGINE SQLMSS SET DEFAULT_CONNECTION SQLCONN1 SQL SQLMSS COMMIT SQL SQLMSS DROP TABLE dbo.TBL1 -RUN
The above worked fine for most of the test tables I wanted to get rid of. I use an MS Access Project to see the list of SQL tables in my database. An example of what I see in the Access Project for most of the tables looks like this in the navigation pane on the left hand side:
Test1 (dbo) Test2 (dbo) Test3 (dbo)
For these tables, the above script worked fine. However, I have a couple tables that look like this in the navigation pane:
dbo.Test_Table_Date1 (dbo)
I'm not sure how these few tables got created, which have a prefix of dbo. But I can't figure out how to adapt the above DDL script to recognize these "dbo" tables. I tried all kinds of things in the DROP TABLE statement, but I keep getting an error, such as:
: Microsoft SQL Server Native Client 11.0: [42S02] Cannot drop the table : 'dbo.Test_Table_Date1', because it does not exist or you do not have : permission.
Do you know of a way to delete tables with a prefix of dbo by adapting the DDL script above? Or, of any other way to delete them using DataMigrator?
Thanks, MarkThis message has been edited. Last edited by: folkie,
WF 8.2.02 App Studio DataMigrator ReportCaster Windows Server 2016
February 04, 2019, 11:55 AM
folkie
I figured it out. I happened to see the tool tip while hovering over the name of one of these funky tables. The tool tip had:
dbo."dbo.Test_Table_Date1"
Thus, I tried:
DROP TABLE dbo."dbo.Test_Table_Date1"
and it worked.
Thanks, Mark
WF 8.2.02 App Studio DataMigrator ReportCaster Windows Server 2016