SEO

#1 (**permalink**) 03-04-2008, 06:23 AM

I need to create duplicates of certain tables.
It woud be great if i can find the 'create table statement', in any of
the system tables which the Create Table of the Object Browser in
Query Analyzer users, so that i can just change the name and create a
new table thus.

Please help me find the Create Table statement available in the system
tables
thank you

#2 (**permalink**) 03-04-2008, 06:23 AM

On Mar 3, 6:06 pm, thetaamo...@gmail.com wrote:
> I need to create duplicates of certain tables.
> It woud be great if i can find the 'create table statement', in any of
> the system tables which the Create Table of the Object Browser in
> Query Analyzer users, so that i can just change the name and create a
> new table thus.
>
> Please help me find the Create Table statement available in the system
> tables
> thank you

You can't find the create table statement because it is not stored
anywhere, but there are other options. One option is to run select
into statement and add a where clause that will select no records at
all. For example the statement bellow will create a table that is
called CopyOfEmployees that has no records at all, but it has the same
structure as Employees table:
select * into CopyOfEmployees from Employees where 1=0

Notice that the select into statement will create a new table with
the same columns as the original table (name and data type) but it
will not create indexes and constraints.

Another option is to create a script from EM/QA or SSMS. If you'll
create the table in the same database, you'll need to rename all
constraints in the script. If you won't do it, the create table
statement will fail.

Adi

#3 (**permalink**) 03-04-2008, 06:23 AM

Right click on the table in SSMS object browser, select Script Table As ->
CREATE to -> New Query Window

--
Anith

#4 (**permalink**) 03-04-2008, 06:23 AM

Unfortunately, the ddl is not stored in the system tables. You can script
the tables from EM or SSMS though.

--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

<thetaamommy@gmail.com> wrote in message
news:1c72f9e7-1052-40c3-8a2d-0f72a4e4c5bf@c33g2000hsd.googlegroups.com...
>I need to create duplicates of certain tables.
> It woud be great if i can find the 'create table statement', in any of
> the system tables which the Create Table of the Object Browser in
> Query Analyzer users, so that i can just change the name and create a
> new table thus.
>
> Please help me find the Create Table statement available in the system
> tables
> thank you

#5 (**permalink**) 03-04-2008, 06:23 AM

>> I need to create duplicates of certain tables. <<

Why?? One of the fundamental rules of RDBMS design is that a table
models one and only one kind of entity or relationship. If two tables
have the same structure, then they have split a set.

A lot of newbies will divide up their data by attribute splitting
(i.e. MalePersonnel and FemalePersonnel tables instead of a single
Personnel table; one table per time period; one table for location;
etc.) It is a common disaster made by people who don't know what a
table is so they mimic a magnetic tape file system or worse.

#6 (**permalink**) 03-06-2008, 02:04 PM

Hi All :

Thank you so much for the replies. I went with
---------------------------------------------------------------------------
select * into CopyOfEmployees from Employees where 1=0
---------------------------------------------------------------------------

as i need to archive the original tables data into a another table,
clear it out and be ready for new data that year.
So I have archive it and the new table name always had a year as a
prefix.

Thank you for all the help !!
-

#7 (**permalink**) 03-06-2008, 02:04 PM

Yes it's like he says
And you can do this also in your Query Analyaer (Right Click on your table I
mean)

Arjen

"Anith Sen" <anith@bizdatasolutions.com> schreef in bericht
news:u9MPgoUfIHA.4712@TK2MSFTNGP04.phx.gbl...
> Right click on the table in SSMS object browser, select Script Table As ->
> CREATE to -> New Query Window
>
> --
> Anith
>

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode