Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: Table DLL Extraction

    I know there are 3rd party tools.
    Is there a simple(ish) TSQL script that can be run for one or all table which results in one file per object and takes account of keys, indexes, constrains, defaults and permissions (though the latter is not essential)?
    Chrs

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    well ... if such a DDL extraction was a simple thing as running a query, do you think that somebody would pay for 3rd party tools?

  3. #3
    Join Date
    Oct 2008
    Posts
    4
    Thanks, but yes, such a DDL extraction IS as simple a thing as running a query - it's called sp_help (which in turns uses sp_helpindexes and sp_helpconstratints). I just didn't want to spend time pulling the parts I want (sp_help is huge) when someone else may have already done it

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Also you can query data dictionary tables: sysobjects, syscolumns, sysindexes and sysconstraints.
    These tables are presented here:
    http://infocenter.sybase.com/help/in...es/tables7.htm

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Why not use ddlgen
    Else have a look at sp__revtable available at
    http://www.edbarlow.com/gem/procs_only/index.htm

  6. #6
    Join Date
    Oct 2008
    Posts
    4

    systable

    Quote Originally Posted by aflorin27
    Also you can query data dictionary tables: sysobjects, syscolumns, sysindexes and sysconstraints.
    These tables are presented here:
    http://infocenter.sybase.com/help/in...es/tables7.htm
    System tables? What are they?
    Last edited by hasski; 10-07-08 at 04:58.

  7. #7
    Join Date
    Oct 2008
    Posts
    4
    Quote Originally Posted by pdreyer
    Why not use ddlgen
    Else have a look at sp__revtable available at
    http://www.edbarlow.com/gem/procs_only/index.htm
    Thanks a lot pdreyer.

    sp_revtable is not suitable as it just extracts the table def not the indexes, keys, constraints, defaults, etc..

    However, I had already been looking at dllgen and it's definitely there! I've written a wrapper to extract all object types, but needs more tweeking..
    The sting is that for sub-procs (procs called by other procs who'd previously initialised a temp table), will need somehow the table definition added to the top for the sake of the compiler. It IS a one-off excercise and I WOULD do it manually, but for the fact there are hundreds of such procs
    Last edited by hasski; 10-07-08 at 05:11.

Posting Permissions

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