How to Repair "Orphaned" Database in SQL Server 2016

Question

So, in my haste, I ran a script to create a database but I did not select the full script before running it. I ended up with a bunch of tables without a "parent" database. Then I created a database intending to somehow attach the tables to it. But I have no clue on how to do it.

Short of dropping all the objects one by one and rerunning the original script, or changing the name of the database and running the script, is there a way of attaching the tables to the database? Tables are empty, i.e., no rows values have been inserted into them yet. Maybe using ALTER statements to attach tables to database?

• Sean Gallardy: No, you cannot "attach and detach" table between databases. Drop the table, create the proper database, and run the full script.

Answer 1

You cannot attach a table to a database. You can get the system to make a script for CREATE TABLE which you would then have to run on the other database. However, you already have that script so you might as well use it.

If you know the table names that were accidentally created and you don't want to delete them one by one then you can make a quick script to delete the "bad" copies from the other database.

IF EXISTS (SELECT * FROM information_schema.tables WHERE name = 'badtable')
 DROP TABLE badtable;

In 2016 you could also:

DROP TABLE IF EXISTS dbo.badtable;

• Erik Reasonable Rates Darling: You're totally right, but for SQL Server you should consider using different views
• indiri: @sp_BlitzErik I use information_schema because it ports between dbs. I use SQL Server, Postgres, and Oracle and I try to minimize the things I have to remember syntax differences on :) But yes, the extra info is helpful for a SQL environment with the sys tables.
• Erik Reasonable Rates Darling: yeah, it's almost like they make it intentionally weird to cross product boundaries 😂

Answer 2

the quickest option is to use the "export" option of management studio, so you can create the remaining tables from database 2 to database 1 (the one with the missing tables)

From Management studio, right click on your "database 2"> tasks> export data This wizard should let you see what tables are missing and then select just these.