Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    17

    Post Unanswered: how to copy design of table

    i want to create a new table with the existing design of any another table without copying the data of that table in query analyzer not by enterprise manager

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select *
      into newtable
      from anyanothertable
     where 1=0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Be aware that Rudy's technique, while fast, will not copy indexes, triggers, or constraints, or identities.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    is this homework?

    You seem to know how to script a table in EM, so why not?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    I would just create a script file for the table and it's indexes, etc. and run it in the new server/database. I would do this creation of the script by right-clicking on "all tasks" -> "generate SQL script" in EM. I would create the script that way because I am too lazy to write it by hand/BOL lookups.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    we can get the exact schema of procedures, triggers using sp_helptext [object name] but to get the schema of the existing table we cant use sp_helptext. so you can use the procedure mentioned in the below link to know the DDL of the current table.

    http://www.koders.com/sql/fid7D3195C...spx?s=datediff

    Ex:
    The name of the procedure used is sp__revtable
    sp__revtable [table_name] will give schema of the table. By using it, you can create the schema for the new table

  7. #7
    Join Date
    Aug 2007
    Posts
    17

    Thumbs up thanks

    thanks i got it

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by parangiri
    Hi,
    we can get the exact schema of procedures, triggers using sp_helptext [object name] but to get the schema of the existing table we cant use sp_helptext. so you can use the procedure mentioned in the below link to know the DDL of the current table.

    http://www.koders.com/sql/fid7D3195C...spx?s=datediff

    Ex:
    The name of the procedure used is sp__revtable
    sp__revtable [table_name] will give schema of the table. By using it, you can create the schema for the new table
    that proc is for sybase, not sql server. parts of it may work, probably not the whole thing, though.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by jezemine
    that proc is for sybase, not sql server. parts of it may work, probably not the whole thing, though.
    Oh gee, so you mean that I can't take a control computer from a Toyota and put it into a Lexus?

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •