copy table definition

Posted on 2001-06-11
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.
Question by:jrspano

Accepted Solution

ykchakri earned 100 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.
LVL 142

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

Expert Comment

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

Author Comment

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.

Author Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now