代码生成工具系列--------（二）根据数据库，得到必要的信息
编写代码生成器，要从数据库中得到哪些信息呢？表名？存储过程名？
当然这些都是必要的。如。下面列举几个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请告诉我。(广告：请大家帮测下 http://www.onsbar.cn/ 它正是采用该工具生成)
谢绝转载
江 2008-10-30
