Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


copy table definition

Posted on 2001-06-11
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

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

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

604 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