0

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?

  • 2
    No, you cannot "attach and detach" table between databases. Drop the table, create the proper database, and run the full script. – Sean Gallardy Sep 22 '17 at 22:42
2

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;
  • You're totally right, but for SQL Server you should consider using different views – Erik Darling Sep 22 '17 at 23:17
  • @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. – indiri Sep 22 '17 at 23:21
  • yeah, it's almost like they make it intentionally weird to cross product boundaries 😂 – Erik Darling Sep 22 '17 at 23:57
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.