Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

copy table definition

Posted on 2001-06-11
5
Medium Priority
?
626 Views
Last Modified: 2012-08-14
I need a sql statement that copies a table with all related stuff EXACLTY, but without the data to a new table name.  I has to work in sql server 7 and 2000 and must bring permissions and preferably indexes, defaults, etc with it.

Thanks in advance.
0
Comment
Question by:jrspano
5 Comments
 
LVL 2

Accepted Solution

by:
ykchakri earned 400 total points
ID: 6178909
You can run the following statement:
select * into table2 from table1
where 1 = 2

This is the quickest way of creating the table structure of table1 into table2.

But, if you want all other related stuff, such as permissions, then right click on the table in EM, select all tasks/generate SQL scripts and check all the options you require. Then modify this script to contain the new table name and run it.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6178915
I suggest that you look up the "generate script" in the "all tasks" menu of the table(s).
Cheers
0
 
LVL 1

Expert Comment

by:vim_asteya
ID: 6180082
see if this helps
create table t1(select * from mytable where 1=2)
t1- is the new table
mytable-is the table who's copy u want
this will copy the structure alone without any data
0
 
LVL 3

Author Comment

by:jrspano
ID: 6181010
thanks for all your input but unfortunatly it wont produce any dynamic output.  the script is static to my database.  I need something dynamic that will take a table I tell it to and make another table with the same table structure and permissions.
0
 
LVL 3

Author Comment

by:jrspano
ID: 6187907
i ended up using this, and not needing the user definitions. thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 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