Database Table Structure generator V1.0 released

Publish a small tool-database table structure Generator

Purpose: in the face of large projects, images are generated based on the database table structure, so that you can instantly find out the details of the project.

Function introduction:

* Generate images based on the database table structure
* Applicable to ms SQL Server 2000, SQL Server 2005, and SQL Server 2008 etc.
* Export the database table structure according to the system SQL statement
* Use GDI + to generate an image of the Table Structure

Program:

1: log on

2: After generation

3: generated table structure

 

The following describes how to implement the Program:

1. Connect to the specified database and obtain the table from the SQL statement of the specific system.
2. Obtain the table structure based on table name, such as field name, type, length, and comment description. Of course, table description (comment) is also available)
   :
   
3. Use the GDI + technology to generate images and add specific modifications to the result set.

 

The program uses a lot of SQL Server System SQL statements, as follows:

• Obtain the SQL Server version.

  exec master.dbo.xp_msver 'ProductVersion'

  This section describes the development history of SQL Server:

  Version	Corresponding database
  7.0	Developed by Microsoft Sybase and Ashton-tate, the first OS/2 version was released in 1988.
  8.0	SQL srever 2000
  9.0	SQL Server 2005
  10.0	SQL Server 2008
  11.0	SQL Server 2012 (no trial, speculation)

 

 

• Get a non-system data table -- that is, get the table created by the user

  SELECT     O.object_id AS TableId,    TableName=O.name  ,    TableDesc= PTB.[value]  FROM sys.columns C    INNER JOIN sys.objects O        ON C.[object_id]=O.[object_id]            AND O.type='U'            AND O.is_ms_shipped=0    INNER JOIN sys.types T        ON C.user_type_id=T.user_type_id    LEFT JOIN sys.extended_properties PTB        ON PTB.class=1             AND PTB.minor_id=0             AND C.[object_id]=PTB.major_idWHERE C.column_id=1 ORDER BY TableName

• Obtain the table structure based on the table name, which is similar to the DESC command of oracle or MySQL.

  Select tablename = case when. colorder = 1 then D. name else ''end, tabledesc = case when. colorder = 1 then isnull (F. value, '') else'' end, columnid =. colorder, columnname =. name, [identity] = case when columnproperty (. ID,. name, 'isidentity ') = 1 then' √ 'else' end, primarykey = case when exists (select 1 from sysobjects where xtype = 'pk' and name in (Select name from sysindexes where indid in (select indid from sysindexkeys where id =. ID and colid =. colid) then '√ 'else' 'end, type = B. name, [precision] =. length, length = columnproperty (. ID,. name, 'precision '), scale = isnull (columnproperty (. ID,. name, 'Scale'), 0), nullable = case when. isnullable = 1 then' √ 'else' end, [Default] = isnull (E. text, ''), columndesc = isnull (G. [value], ''), indexname = case when isnull (H. index name, '') Like 'ix _ % 'then' √ 'else' end, indexsort = case when isnull (H. index name, '') Like 'ix _ % 'Then isnull (H. sort, '') else' end from syscolumns a left join policypes B on. xtype = B. xusertype inner join sysobjects D on. id = D. ID and D. xtype = 'U' and D. name <> 'dtproperties' left join syscomments e on. cdefault = E. id left join sys. extended_properties g on. id = G. major_id and. colid = G. minor_id left join sys. extended_properties F on D. id = f. major_id and F. minor_id = 0 left join (select index name =. name, C. ID, D. colid, sort = case indexkey_property (C. ID, B. indid, B. keyno, 'isscending') when 1 then' in descending order 'when 0 then' in ascending order 'end from sysindexes a join sysindexkeys B on. id = B. ID and. indid = B. indid join (-- here, when multiple indexes exist, the Select ID, colid, indid = min (indid) from sysindexkeys group by ID, colid) with the smallest index number is obtained) b1 on B. id = b1.id and B. colid = b1.colid and B. indid = b1.indid join sysobjects C on B. id = C. ID and C. xtype = 'U' and C. name <> 'dtproperties' join syscolumns D on B. id = D. ID and B. colid = D. colid where. indid not in (0,255) h on. id = H. ID and. colid = H. colid where D. name = n' {0} 'order by. ID,. colorder

• The remaining focus should be on GDI +. Read the code carefully :)

Source code

Finally, where is the source code? Here, https://github.com/FrankFan/DatabasePicExporter

The executable file is here: http://files.cnblogs.com/fanyong/DatabasePicExporter.rar