如何通过 SQL 提取 Sybase (12.5) 表 DDL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3720851/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I extract Sybase (12.5) table DDL via SQL?
提问by Michael Wilson
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.
我希望通过 SQL 以编程方式提取表 DDL，结果“足够好”来重新导入和重建表。
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.
DBArtisan 产生了我正在寻找的确切结果，但我有一个需要使用的几十个表的动态列表，并且希望有一个程序化的解决方案。
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?
我认为 DBArtisan 必须以某种方式调用 API。他们只是在破坏 systables 还是有一个系统安装了我缺少的存储过程（类似于产生存储过程文本的系统）？
回答by B0rG
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:
使用您的表名并执行代码，您应该在此代码的末尾获得#rtn 表中的所有 DDL 语句：
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 ;-)
（学分转到 ROCKY 这一个 ;-)
回答by DVK
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.
IIRC 有一个名为 DBSchema 的工具（peppler.org/downloads/dbschema-2_4_2.zip 是我能找到的最好的 URL） - 如果 URL 没有响起，Mike Peppller 是 sybperl 的作者。如果您更喜欢自己动手，您可能可以对该脚本的代码进行逆向工程。
As far as SQL-wise, the table info is in sysobjectstable and the column info is in syscolumnsin Sybase.
就 SQL 而言，表信息在sysobjects表中，列信息syscolumns在 Sybase 中。
You can also use stored procs: http://www.razorsql.com/articles/sybase_admin_queries.html
您还可以使用存储过程：http: //www.razorsql.com/articles/sybase_admin_queries.html
回答by hasski
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!)
是的，但不仅仅是表名和列。你需要约束、索引、键、默认值、分区、权限...... sybase 代码的基础资源是多么的稀薄（sp_help 并没有涵盖所有这些 - 要测试，使用像 DBArtisan Extract 这样的东西） DDL 工具，您将看到它是多么全面！）
回答by Arun Christopher
ASE ships in with DDL Script Generator Utility - ddlgen
ASE 附带 DDL 脚本生成器实用程序 - 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.
该实用程序可用于为整个数据库、表等创建脚本备份。 Sybase 帮助站点中提供了示例命令。
Under Windows, the utilty can be found at %sybase%/ASE-15_0/bin
在 Windows 下，可以在 %sybase%/ASE-15_0/bin 找到该实用程序
