[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to copy table definitions within and between databases

Posted on 2006-05-12
8
Medium Priority
?
1,022 Views
Last Modified: 2010-07-27
How might I copy table definitions within and between SQL Server 2005 Express databases in VS2005 server explorer?   Or does it need SSE management studio?  Or is it not an option?
0
Comment
Question by:codequest
  • 5
  • 3
8 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 16674049
Hi codequest;

You will need a copy of Microsoft SQL Server Management Studio Express and can be downloaded from the following link free of chages.

http://www.microsoft.com/downloads/details.aspx?familyid=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&displaylang=en

Fernando
0
 
LVL 2

Author Comment

by:codequest
ID: 16674532
Thanks for the message.   I've got a copy of MSSMSE, and I've the table folders for both DB's open, and I can select a table from one folder and then select "Copy" from the Edit menu, however, I can't seem to find a way to do a paste...the "paste" is greyed out when I select the other DB's table folder.  Any suggestions?
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 16674769
Hi codequest;

To your question, "I can select a table from one folder and then select "Copy" from the Edit menu, however, I can't seem to find a way to do a paste", When you select a table in that way and click on the Edit menu Copy command that will just copy the table name to the clipboard. You can open a new Query Window in  MSSMSE and select the Query Window and then go to the Edit menu Past command to prove that.

The best way to copy a structure of a table from one Database to another is to create a T-SQL commands and that can be done as follows:

1.      In the Object Explore window select the table of which you want to copy the structure of.
2.      Right click on the table name and select Script Table as  Create to  New Query Edit Window.
3.      When the Query Edit Window opens you will need to change a couple of line
4.      The first line to change
USE [AdventureWorks]
GO
In this sample I was using the sample database AdventureWorks, change this to the name of the database you want to make the new table in. for example I amusing Northwind db.
USE [Northwind]
GO
5.      The other line to change is:
CREATE TABLE [dbo].[AWBuildVersion]
If the Schema name, [dbo], is not what it should be for the new database then correct it and if you whant to change the table name, [AWBuildVersion], then correct this also to what you need.
6.      Then to create the table in the new database click on the Execute button just above the Query window.
7.      The message window just under the Query window will tell you that the T-SQL command was a success or not.
Command(s) completed successfully.
8.      The refresh the database and you will see the new table.


Fernando
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 2

Author Comment

by:codequest
ID: 16674798
Thanks for the detailed explanation.   I can see I'm about to learn T-SQL (this is a good start!).   I'll take a look at this a little later and get back to you.
0
 
LVL 2

Author Comment

by:codequest
ID: 16678697
Tried it.  Found all the menu items, etc.   Good learning process!

The USE line had (paraphrasing)

USE ["E:\something\something\SourceDB.MDF"]       <=  points correctly to the source DB

Then in this line:  

CREATE TABLE [dbo].[AWBuildVersion]

I changed the [dbo] to point to the result DB folder path = "E:\something\something\ResultDB.MDF"
which I copied directly from the Object Explorer name for the DB

So the line reads something like

CREATE TABLE [E:\something\something\ResultDB.MDF].[ResultTable]

However, when I execute, it gives

The specified schema name "E:\something\something\ResultDB.MDF" either does not exist or you do not have permission to use it.

I checked a couple of times and I believe the path string for the ResultDB is correct, and the DB appears in Object Explorer.

Any suggestions?

Thanks!
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 16683450
Hi codequest;

The USE statement should just have the name of the database as shown in the Object Explore window of the MSSMSE without the path, so this line

    USE ["E:\something\something\SourceDB.MDF"]

Should be most likely

    USE [SourceDB]

The Create Table command, CREATE TABLE [Schema].[DatabaseName], the schema is not the path and name of the database you want to create the table in, the USE statement does that by looking in the system tables. A schema is a method of creating groups and placing objects within groups, which can then be used to grant or revoke permissions as a group to SQL Server connections. If you look at the Object Explore window and open the Tables folder for a database you will see table names that are in two parts, Schema.Tablename. For the purpose of testing use one of the Schema names already available in the database you want to create the table in. So let’s say that there is a schema name xyz and you want to create the table ResultTable then the create table will look like this:

    CREATE TABLE [xyz].[ResultTable]

The reason you got the error you did was because the schema name you gave did not exist.

Fernando
0
 
LVL 2

Author Comment

by:codequest
ID: 16684450
Thanks for the input and the education.   I'll look into it again in a day or two with these tips in mind.
0
 
LVL 2

Author Comment

by:codequest
ID: 16722493
Fernando,

I got it to work.   Actually, the db name recognized by MSSMSE does include the full path...I guess that's the way I've got them registered with SQL Server.

Everything else was just as generated by the script.

Thanks for the help!

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question