《得到SQL数据库中所有表字段及字段中文描述》由会员分享，可在线阅读，更多相关《得到SQL数据库中所有表字段及字段中文描述（3页珍藏版）》请在金锄头文库上搜索。
1、文档供参考，可复制、编制，期待您的好评与关注！ 得到SQL数据库中所有表字段及字段中文描述SQL2000 写法：?1234567891011121314151617181920212223242526272829303132333435363738sql SELECT(case when a.colorder=1 then d.name else end) N表名, a.colorder N字段序号, a.name N字段名, (case when COLUMNPROPERTY( a.id,a.name,IsIdentity)=1 then else end) N标识, (case when
2、(SELECT count(*) FROM sysobjects WHERE (name in(SELECT nameFROM sysindexes WHERE (id = a.id) AND (indid in(SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name) AND(xtype = PK)0 then else end) N主键, b.name N类型, a.length N占用字节
3、数, COLUMNPROPERTY(a.id,a.name,PRECISION) as N长度, isnull(COLUMNPROPERTY(a.id,a.name,Scale),0) as N小数位数, (case when a.isnullable=1 then else end) N允许空, isnull(e.text,) N默认值, isnull(g.value,) AS N字段说明-into #tx FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id
4、=d.id and d.xtype=U and d.namedtpropertiesleft join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorderSQL2005 写法：?123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051sql select
5、table_name= ( case when t_c.column_id=1 then t_o.nameelse end), column_id=t_c.column_id, column_name=t_c.name, type=t.name, max_length=t_c.max_length, precision=isnull(t_c.precision,0), scale=isnull(t_c.scale,0), is_identity=case when t_c.is_identity=1 then else end, is_primary= ( case when exists (
6、 select 1 from sys.indexes i,sys.index_columns ic,sys.objects o where o.type=PK and o.name=i.name and i.index_id=ic.index_id and i.object_id=ic.object_id and ic.column_id=t_c.column_id and o.parent_object_id=t_c.object_id ) then else end), is_nullable=case when t_c.is_nullable=1 then else end, defau
7、lt_value=isnull(c.definition,), description=isnull(e.value,), fk_column_name=isnull(f_c.name,), fk_table_name=isnull(f_o.name,) from sys.columns t_c inner join sys.objects t_o on t_c.object_id=t_o.object_id left join sys.types t on t.system_type_id=t_c.system_type_id and t.user_type_id=t_c.user_type
8、_id left join sys.default_constraints c on c.object_id=t_c.default_object_id and c.parent_object_id=t_c.object_id and c.parent_column_id=t_c.column_id left join sys.extended_properties e on e.major_id=t_c.object_id and e.minor_id=t_c.column_id left join( select parent_object_id,referenced_object_id,
9、column_id=min(key_index_id) from sys.foreign_keys group by parent_object_id,referenced_object_id )f on f.parent_object_id=t_c.object_id and f.column_id=t_c.column_id left join sys.columns f_c on f_c.object_id=f.referenced_object_id and f_c.column_id=f.column_id left join sys.objects f_o on f_o.object_id=f.referenced_object_id where t_o.type=U and t_o.namesysdiagramsorder by t_o.name,t_c.column_id /
