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

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

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

--
Anith

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

>> 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.

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 !!
-

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
>