代码生成工具系列--------（二）根据数据库，得到必要的信息

编写代码生成器，要从数据库中得到哪些信息呢？表名？存储过程名？

 

当然这些都是必要的。如。下面列举几个SQL语句，也不多作解，相信都能看懂。

 

1：

/***************************************

得到TABLE,存储过程的

编号

Owner

表名

类型

****************************************/

select

 a.Object_id

,b.name 'Owner'

,a.name 'Table'

,'T' type

from sys.tables a

inner join sys.schemas b on b.schema_id = a.schema_id

union all

select

 a.Object_id

,b.name 'Owner'

,a.name 'Name'

,'P' type

from sys.procedures a

inner join sys.schemas b on b.schema_id = a.schema_id

where a.type = 'P' and charindex('$NPSP', a.name) = 0 and charindex('diagram', a.name) = 0

order by type desc, b.name, a.name

 

 

 

 

/**********************************************

得到表ID

字段

类型

长度

SQLTtyp

是否为空

ISIDENTITY

**********************************************/

select

 a.Object_id

,a.name 'Column'

,b.name 'Type'

,case

 when b.name in ('Text', 'NText', 'Image') then -1

 when b.name in ('NChar', 'NVarchar') then a.max_length / 2

 else a.max_length end 'Length'

,b.name + case

 when b.name in ('Char', 'VarChar', 'NChar', 'NVarChar', 'Binary', 'VarBinary') then '(' +

  case when a.max_length = -1 then 'MAX'

  when b.name in ('NChar', 'NVarchar') then cast(a.max_length / 2 as varchar)

  else cast(a.max_length as varchar) end + ')'

 when b.name in ('Numeric', 'Decimal') then '(' + cast(a.precision as varchar) + ',' + cast(a.scale as varchar) + ')'

 else '' end as 'SqlType'

 

,a.is_nullable 'IsNullable'

,a.is_identity 'IsIdentity'

from sys.columns a

inner join sys.types b on b.user_type_id = a.user_type_id

where a.object_id in (629577281,661577395,469576711,2073058421)

 

 

 

 

3

 

select

 a.object_id 'Object_id'

,c.name 'Column'

,case when is_unique = 1 then b.index_id else 0 end 'Unique'

,b.is_primary_key 'IsPrimaryKey'

,cast(case when b.type_desc = 'CLUSTERED' then 1 else 0 end as bit) 'IsClustered'

,case when a.is_descending_key = 1 then 2 when a.is_descending_key = 0 then 1 else 0 end 'IsDesc'

from sys.index_columns a

inner join sys.indexes b on b.object_id = a.object_id and b.index_id = a.index_id

left join sys.columns c on c.object_id = a.object_id and c.column_id = a.column_id

where a.object_id in (629577281,661577395,469576711,2073058421)

 

 

 

4

 

select

 b.object_id 'Object_id'

,c.name 'Column'

,referenced_object_id

,cast(1 as bit) 'IsForeignKey'

,d.name 'Referenced_Column'

from sys.foreign_key_columns a

inner join sys.tables b on b.object_id = a.parent_object_id

inner join sys.columns c on c.object_id = a.parent_object_id and c.column_id = a.parent_column_id

inner join sys.columns d on d.object_id = a.referenced_object_id and d.column_id = a.referenced_column_id

where b.object_id in (629577281,661577395,469576711,2073058421)

 

 

说明：其中IN后面的都是由第一步得到。都在SQL2005下运行，如果有谁能得到MYSQL,ORACL,SQL 2000请告诉我。(广告：请大家帮测下 www.onsbar.cn 它正是采用该工具生成)

 

                            谢绝转载

 

                                                                                    江    2008-10-30