sql2005获取表字段说明

 发现sysproperties表没有用啦，而是sys.extended_properties

select table_name,column_name,B.value 
from information_schema.columns A left join sys.extended_properties B
on B.major_id=object_id(A.table_name)
and A.ordinal_position=B.minor_id

附：.自动生成类

1.得到表名，对类属性名，对象类型的视图(tmp_v_generateclass)

create view [dbo].[tmp_v_generateclass] as 
select table_catalog,table_name,convert(nvarchar(200),isnull(B.value,'')) as demo,convert(varchar(200),column_name) as column_name,data_type,isnull(character_maximum_length,numeric_precision) as column_length,is_nullable ,substring(column_name,charindex('_',column_name)+1,100) as oo_name,(select oo_typename from tmp_dbtype2ootype B where B.db_typename=A.data_type) as oo_type
from information_schema.columns  A left join sys.extended_properties B
on B.major_id=object_id(A.table_name)
and A.ordinal_position=B.minor_id

2.得到最终的类的视图(tmp_v_class)

 

create view [dbo].[tmp_v_class] as 
select table_name,'private '+oo_type+' _'+oo_name+';' as ooproperty 
from tmp_v_generateclass
union all 
select table_name,'///<summary>'+char(13)+char(10)+'///'+demo+char(13)+char(10)+'/// </summary>'+char(13)+char(10)+'public '+oo_type+' '+ upper(left(oo_name,1))+substring(oo_name,2,100) +'{'+char(13)+char(10)+' get {'+char(13)+char(10)+'return _'+oo_name
+';'+char(13)+char(10)+'}'+char(13)+char(10)+'set {'+char(13)+char(10)+'_'+oo_name+'=value;}'+char(13)+char(10)+'}' as ooproperty
from tmp_v_generateclass

3.net类型与sql类型的对应关系(表tmp_dbtype2ootype)

db_typename                                        oo_typename
-------------------------------------------------- --------------------------------------------------
bigint                                             long
binary                                             int
bit                                                bool
char                                               string
datetime                                           DateTime
decimal                                            decimal
float                                              float
image                                              byte[]
int                                                int
money                                              float
nchar                                              string
ntext                                              string
numeric                                            double
nvarchar                                           string
real                                               float
smalldatetime                                      DateTime
smallint                                           short
smallmoney                                         float
sql_variant                                        Object
sysname                                            string
text                                               string
timestamp                                          string
tinyint                                            int
uniqueidentifier                                   string
varbinary                                          string
varchar                                            string
xml                                                string