关闭

sql2005获取表字段说明

标签: sqlstringfloattableooobject
1022人阅读 评论(0) 收藏 举报
分类:

 发现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,100as 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
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:397866次
    • 积分:6315
    • 等级:
    • 排名:第4028名
    • 原创:196篇
    • 转载:121篇
    • 译文:1篇
    • 评论:55条
    文章分类