以前一直做各种各样数据库的信息化开发工作，有SQL Server 有Oracle 有DB2 有Mysql；所以一直就想着写一套代码逻辑后各种数据库上都能正常运行，虽然这个想法比较难100%实现，但是经过这些年的摸索算是得到了很多宝贵经 验，并一直用PowerDesigner设计表结构后用配套的代码生成器生成代码。

   当然我们也想购买正版的 PowerDesigner 但是实在太贵了，有些买不起；最近正好收到了Sybase公司的律师函了，若再用就会吃官司了，那麻烦事情就大了；只能把所有跟Sybase公司相关的东 西能卸载的全卸载了，只能忍痛割爱了；因为法律不是开玩笑的，若真在用那打了官司就必输了，直接全部卸载干净多一事情不如少一事情了。

   最近几年总是遇到各种数据库的情况也变得少了一些，经常用SQL Server的状态了，那代码生成器干脆直接从SQL Server生成就好了，省心省事了；也不搞那么大的野心什么数据库就生成了。

   第一步：选哪个数据库上生成代码？

  

  每个表都可以写好说明，这样生成代码时可以把说明带上。

  

  下面语句是获取所有表的的名字及说明的方法

  下面语句是获取表的所有列的信息的方法。

同时给有需要的附加上整理好的sql语句，希望能派上一些参考意义：

复制代码 
SELECT A.name AS TableName                                          ,B.value AS Description                                         FROM (SELECT name, id                                                  FROM SysObjects                                                WHERE xType='U' AND category = 0) AS A LEFT OUTER JOIN                                                   (SELECT major_id, value                                                       FROM sys.extended_properties                                                      WHERE name='MS_Description'                                                            AND minor_id = 0) AS B  ON A.id = B.major_id                                         ORDER BY A.name
复制代码
?
SELECT 表名  = CASE a.colorder WHEN 1 THEN c.name ELSE '' END,
                                        序    = a.colorder,
                                        字段名= a.name,
                                        标识  = CASE COLUMNPROPERTY(a.id,a.name, 'IsIdentity' ) WHEN 1 THEN '√' ELSE '' END,
                                        主键  = CASE
                                   WHEN EXISTS ( SELECT * FROM sysobjects WHERE xtype= 'PK'
                                 AND name IN (SELECT [name] FROM sysindexes   WHERE id=a.id
                                 AND indid IN (SELECT indid FROM sysindexkeys WHERE id=a.id
                                 AND colid IN (SELECT colid FROM syscolumns   WHERE id=a.id
                                 AND name=a.name)))) THEN '√' ELSE '' END,
                                      类型= b.name,
                                      字节数= a.length,
                                      长度  = COLUMNPROPERTY(a.id,a.name, 'Precision' ),
                                      小数  = CASE ISNULL(COLUMNPROPERTY(a.id,a.name, 'Scale' ),0) WHEN 0 THEN '' ELSE CAST(COLUMNPROPERTY(a.id,a.name, 'Scale' ) AS VARCHAR) END,
                                      允许空= CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,
                                      默认值= ISNULL(d.[text], '' ),
                                      说明  = ISNULL(e.[value], '' )
                                  FROM syscolumns a
                                 LEFT JOIN systypes b ON a.xtype=b.xusertype
                                 INNER JOIN sysobjects c ON a.id=c.id AND c.xtype= 'U' AND c.name<> 'dtproperties'
                                 LEFT JOIN syscomments d ON a.cdefault=d.id
                                 LEFT JOIN sys.extended_properties e ON a.id = e.major_id AND a.colid=e.minor_id AND e.name= 'MS_Description'
                                 WHERE c.name = '" + tableName + @"'
                                 ORDER BY c.name, a.colorder"