Copy table structure to a new table

Hello everyone,

I have a local MSSQL server (I guess it's called MSDE), with some tables that I would like to use as a template for a set of new tables. I would simply like a Stored Procedure that takes these 3 tables, makes a copy of their structure (not data, since they will be empty), and name them by using a parameter given to the SP. I have made something that I thought would work, but after testing it a bit more, it seems to forget default values for the fields, which is of course not good enough :). I hope that someone can tell me how to duplicate these 3 tables, including every detail for the fields!

0
kaspertsw
4/24/2005 2:41:25 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

1 Replies
764 Views

Similar Articles

[PageSpeed] 39
Get it on Google Play
Get it on Apple App Store

This is not really a trivial task in T-SQL.  You can probably
cobble something together using the INFORMATION_SCHEMA views and some
heavy dynamic SQL, but it may be easier to simply hardcode the tables'
DDL into your stored procedure.

Or, you could use DMO to help you.  See if this helps:

http://www.nigelrivett.net/DMO/DMOScripting.html




Adam Machanic
SQL Server MVP
0
AdamMachanic
4/24/2005 5:07:30 PM
Reply:

Similar Artilces:

Copy SQL express table structure to SQL server
How does one generate Transact-SQL for table structure in SQL server express 2005? I have very limited access to an SQL server database (using rudimentary web based interface) for my hosted website.  I am ready to deploy an application that was developed with Visual Studio 2005 using SQL server express as the database.  I have four tables that I want to copy (structure only) from "express" to the hosted SQL server.  I think I need to generate Transact-SQL statements to "create" the table structure, then run these statements as a stored procedure on the web base...

copying from table to table
I would like to copy the contents of one column in a table to another table. However, the first column of type double while the column it is being copied to is a varchar. Any ideas? Let's talk about the ideal situation. If the first table is 1 column and you can just blatently update the other table with the value, create a pipeline from the numberic table/column to the character table/column. Go to the SQL pane and change the SELECT "number_table"."anum" FROM "number_table" To SELECT string("number_table".&quo...

Copy Records From one table to another table with same structure
Hi allI have two tables in SqlServer with Exactly Same Structure,I want to Copy all Records fromone of them to another one.I came across to "Insert....select..." statement But i have two problem 1) I don't know any thing about Columns name!!! i just know they have same structure and as far as i know , "Insert...select..." need the Column list to operate correctly, am i right? 2) these two table have One Prinary Key column with IDENTITY feature. Any Help Greatly appriciated.Regards.Nothing Is Quite Impossible !!! Personally, i found DTS is the best way you just need to set&nbs...

how to create a new sql table from xml table through coding
I have a xml tableProgramatically I have to convert the data to sql table and show the data in the grid view from the sql tableall I have to do by coding onlycan it be possible  Hi, As far as I know Torque can generate SQL source from an XML schema describing a database structure: http://db.apache.org/torque/releases/torque-3.1.1/generator/ However you need to do that throught coding which is not as simple as it sounds. You may have to transform xml schema to sql schema manually or use xslt. Or you can load xml schema into dataset and create database schema from dataset. Hop...

From Table to Table
I am working in Delphi 4 and ran into a roadblock I am unfamiliar with. I am trying to search and find values in one table based off of matching values in another. For example a table of objects with fields like name, description, type, ect; and a table of object properties in which each object can be represented multiple times based on the settings on the page at the time. So if i search the object property table (and eventually loop through to repeat for all objects) to find all cases of visible objects when a certain criteria is met. It stops on the first object with "Yes" in th...

Tables?! Why Tables?!
Bit random...but why does everything have to be rendered as a table?  It's starting to get quite annoying.  Formview - does this need to be a table?  the Menu??  Wouldn't it make sense to allow the option to...say...not render a Menu as a table?  Am I the only one that thinks this or are there others?  www.internetgeeks.co.ukwww.norwichbookclub.co.uk You can use CSS Adapters to create your own adapters to override how the controls render it self. In the next version of ASP.Net, you will probably have more flexibility to specify the surrounding elements of th...

Adding new content to a table by refering other tables using SQL
I keep product name, id in Table1. I keep Category name, id in Table2. I keep relation between product and category (product_id, category_id) in Table3. I have added some products to the table with proper category. Work fine But for some products I did not specified any category (ie their id  is not present  in Table3) But now I want all such products (ie all products whose category is nothing)  To be associated with category_id 10   Can I do this  simply with SQL queries?   Hope u can help me   sujith Hello sujith! UPDATE [TableName] -- ...

Reverse Engineer created copies of table (table:1, table:2)... How do I merge them back into 1 table?
PowerDesigner 12.1 I just reverse-engineered about 20 tables from our database, and it took one of my key tables and created two copies ("TableName : 1" and "TableName : 2"). I'm sure the engine was trying to make the diagram easier for it to auto-layout, but I want all the FKs and such from one table). How do I merge them back into just one table? Thanks, Jason Jason Vogel wrote: > PowerDesigner 12.1 > > I just reverse-engineered about 20 tables from our database, and it took > one of my key tables and created two copies ("Tabl...

Copy values from one table to another table using LINQ to SQL?
Hello,I have two database tables that have different names but identical structure (columns).What is the easiest way to copy the values from one table to the other table using LINQ to SQL? Thanks,-- shawn shawn Hi, As far as I research, we could use "foreach"  to insert the data from one table to another table. The code is shown below.         //there exist two table list and listSecond        DataClassesDataContext dataClass = new DataClassesDataContext(); //create the instance of the DataCon...

Copy tables from SQL to new Access DB
Anyone out there have an easy way to copy entire tables from SQL to an Access DB? The obvious answer to this is DTS, but I've run into a problem using DTS in a hosted environment that I just cannot overcome, not to mention all the hoops you have to jump through with DTS (such as it doesn't support FTP PUTS!!!!) I was thinking there has got to be a way to use the DataTable.Add(DataTable.Copy()), but can't seem to get things together with this.Rob Richardwww.robrichard.com Okay, I'm getting closer to the solution here. Anyone care to check out this code and tell me why the dataadapter...

How to get table structure of a table .
I want to get the columns and column types of a table . I know that table syscolumns would help . But my database is created as ASE compatible . The definition of syscolumns is quite difference . I try to use the method of ASE like "select b.name as col,c.name as type from sysobjects a, syscolumns b,systypes c where a.name='table_name' and a.id=b.id and b.usertype=c.usertype " It works ,but it is so slow . It's better if you use the system base tables rather than the ASE-compatibility views like sysobjects. Here are some sample queries involving s...

How to basically copy tables with new names rather than create similar tables from similar manual input.
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating th...

I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem?  Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...

stored procedure to copy table 1 to table 2 appending the data to table 2.
Just wondering if there is an easy transact statement to copy table 1 to table 2, appending the data in table 2.with SQL2000, thanks.Paul GSoftware Engineer Appending the rows, or the columns? Append rows: Insert into table2SELECT * FROM table1 Append columns: UPDATE Table2SET t2.col1=t1.col1+t2.col1, t2.col2=t1.col2+t2.col2, etcFROM Table2 t2JOIN Table1 t1 ON (t1.yourkeyhere=t2.yourkeyhere) Not sure how useful the second append is, and it'd be really odd to actually want to do that, but you can, if you ever really needed to ;p ok thanks, just need to append the rows.  Lo...

Web resources about - Copy table structure to a new table - asp.net.sql-datasource

Crystal structure - Wikipedia, the free encyclopedia
A crystal structure is composed of a pattern, a set of atoms arranged in a particular way, and a lattice exhibiting long-range order and symmetry. ...

Smarkets: Online betting company with ‘self management’ structure
RESTAURANT-catered lunches, free football tickets, unlimited holidays and getting paid what you like sounds more like a luxury jaunt than a day ...

Investors look past dual stock structure in Atlassian float
Fund managers have brushed off concerns about Atlassian's proposed structure and welcomed news it will head to an initial public offering in ...

Stonehenge-like structure 'Wheel of Giants' a mystery in the Middle East
Archaeologists are learning more about the 'Wheel of Giants,' a massive structure in the Golan Heights that dates back to the time of Stonehenge ...

Atlassian founders to keep control with News Corp style structure
Just like Rupert Murdoch, Atlassian's founders will use a dual-class share structure to retain control.

Tiny Skin Structures Make Fish 'Invisible' to Predators
Scientists work out an enduring mystery about how some fish can seem to disappear.

Digital Marketing: Organisational Structures and Resourcing Best Practice Guide - Econsultancy
The Digital Marketing: Organisational Structures and Resourcing Best Practice Guide is an update on the previous iterations of this research ...

Finding Geometric Structure In Rodent Neural Activity
Methods of algebraic topology yield a technique to uncover the geometry of the hippocampus.

Tornado Strikes Denair Damaging Homes, Structures In Its Path
FOX40 Tornado Strikes Denair Damaging Homes, Structures In Its Path FOX40 This funnel cloud is a rare sight in Denair, and it left as quickly ...

These Amazing Immersive Models Help Scientists Understand the Structure of Forests
Tropical forest are large, complex and easy to get lost in—which isn’t helpful if you’re trying to study them. Now, scientists are using these ...

Resources last updated: 12/11/2015 12:33:07 AM