CODEWITHSUNDEEP
sqlsybaseddl
Michael Wilson
Michael Wilson

Reputation: 442

How do I extract Sybase (12.5) table DDL via SQL?

I've scanned similar questions but they seem to be referring to other databases and/or external languages.

I'm looking to programatically extract table DDL via SQL, with a result that's "good enough" to re-import and reconstruct the table.

DBArtisan produces the exact result I'm looking for, but I have a dynamic list of a few dozen tables that I need to work with, and was hoping for a programatic solution.

I figure DBArtisan has to be doing calling the API somehow. Are they just ripping against the systables or is there a system installed stored proc (similar to the one that yields stored proc text) that I'm missing?

Upvotes: 2

Views: 27948

Answers (4)

Arun Christopher
Arun Christopher

Reputation: 402

ASE ships in with DDL Script Generator Utility - ddlgen

The utility can be used to create backup of scripts for an entire database, tables, etc. Sample commands are provided in Sybase help site.

Under Windows, the utilty can be found at %sybase%/ASE-15_0/bin

Upvotes: 0

B0rG
B0rG

Reputation: 1225

Best solution would be to wrap this into a nice stored procedure, but you should get the idea from the code below. Just replace:

   SELECT @OnlyTableName = 'my_table_name'

with your table name and execute the code, you should get all DDL statements in #rtn table at the end of this code:

   DECLARE @TableName               varchar(50)
   DECLARE @ObjectID                int
   DECLARE @IndexID                 int
   DECLARE @IndexStatus             int
   DECLARE @IndexName               varchar(30)
   DECLARE @msg                     varchar(255)
   DECLARE @OnlyTableName           varchar(50)
   DECLARE @LastColumnId            int
   DECLARE @i                       int


   SELECT @OnlyTableName = 'my_table_name'


   CREATE TABLE #columns (
      column_name char(30)    NULL,
      type_name   char(30)    NULL,
      length      char(10)    NULL,
      iden_flag   char(10)    NULL,
      null_flag   char(20)    NULL,
      flag        char(1)     NULL
   )

   CREATE TABLE #rtn (
      msg   varchar(255)   NULL
   )



   SELECT @TableName = name,
          @ObjectID  = id
     FROM sysobjects
    WHERE type = 'U'
      AND name = @OnlyTableName
    ORDER BY name

   SELECT @LastColumnId = MAX(colid) FROM syscolumns WHERE id = @ObjectID

   INSERT #columns
   SELECT col.name,
          typ.name,
          CASE WHEN typ.name in ('decimal','numeric') THEN '(' +
convert(varchar, col.prec) + ',' + convert(varchar, col.scale) + ')'
               WHEN typ.name like '%char%'THEN
'('+CONVERT(varchar,col.length)+')'
               ELSE '' END,
          CASE WHEN col.status = 0x80 THEN 'IDENTITY' ELSE '' END,
          CASE WHEN convert(bit, (col.status & 8)) = 0 THEN "NOT NULL"
ELSE "NULL" END + CASE WHEN col.colid = @LastColumnId THEN ')' ELSE
',' END,
          NULL
     FROM syscolumns col, systypes typ
    WHERE col.id = @ObjectID
      AND col.usertype = typ.usertype
    ORDER BY col.colid


   INSERT #rtn
   SELECT "CREATE TABLE " + @TableName + " ("
   UNION ALL
   SELECT '    '+
                    column_name + replicate(' ',30- len(column_name)) +
                    type_name + length + replicate(' ',20 -
len(type_name+length)) +
                    iden_flag + replicate(' ',10 - len(iden_flag))+
                    null_flag
        FROM #columns

   SELECT name, indid, status, 'N' as flag INTO #indexes
   FROM sysindexes WHERE id = @ObjectID

   SET ROWCOUNT 1
   WHILE 1=1
   BEGIN
      SELECT @IndexName = name, @IndexID = indid, @IndexStatus =
status FROM #indexes WHERE flag = 'N'
      IF @@ROWCOUNT = 0
         BREAK

      SELECT @i = 1
      SELECT @msg = ''
      WHILE 1=1
      BEGIN
         IF index_col(@TableName, @IndexID, @i) IS NULL
            BREAK

         SELECT @msg = @msg + index_col(@TableName, @IndexID, @i) +
CASE WHEN index_col(@TableName, @IndexID, @i+1) IS NOT NULL THEN ','
END
         SELECT @i = @i+1
      END

      IF @IndexStatus & 2048 = 2048 --PRIMARY KEY
         INSERT #rtn
         SELECT "ALTER TABLE " + @TableName +
                " ADD CONSTRAINT " + @IndexName +
                " primary key "+
                CASE WHEN @IndexID != 1 THEN 'nonclustered ' END +
'('+ @msg +')'
      ELSE
         IF (@IndexStatus & 2048 = 0 AND @IndexID NOT IN (0, 255))
--NOT PRIMARY KEY
            INSERT #rtn
            SELECT 'CREATE '+
            CASE WHEN @IndexStatus & 2 = 2 THEN 'UNIQUE ' ELSE '' END +
            CASE WHEN @IndexID = 1 THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END +
            'INDEX ' + @IndexName + ' ON ' + @TableName + ' ('+ @msg +')'

      UPDATE #indexes SET flag = 'Y' WHERE indid = @IndexID

   END
   SET ROWCOUNT 0

   SELECT * FROM #rtn

   DROP TABLE #columns
   DROP TABLE #rtn

let me know if it helped.

(credits go to ROCKY for this one ;-)

Upvotes: 5

hasski
hasski

Reputation: 11

Yeah, but more to it than tables names and columns. You need constraints, indexes, keys, defaults, partitions, permissions ...... Remarkable how thin on the ground resources are for sybase code that will do it (sp_help does not cover it all - to test, use something like DBArtisan Extract DDL tool and you will see how comprehensive THAt is!)

Upvotes: 1

DVK
DVK

Reputation: 129519

IIRC there's a tool called DBSchema ( peppler.org/downloads/dbschema-2_4_2.zip is the best URL I was able to find ) - in case the URL doesn't ring any bells, Mike Peppller is the author of sybperl. You can likely reverse engineer the code for that script if you prefer to roll your own.

As far as SQL-wise, the table info is in sysobjects table and the column info is in syscolumns in Sybase.

You can also use stored procs: http://www.razorsql.com/articles/sybase_admin_queries.html

Upvotes: 3

Related Questions