汇出数据表字段(&属性)清单
来源:互联网 发布:机器翻译 软件 编辑:程序博客网 时间:2024/06/05 18:00
以下语句可将SQL数据库中,所有的表及字段(属性:表说明、字段名、字段类型、字段顺序、长度、是否为空、字段说明等),汇出至一个临时表。以备用。
Select 1 as Type,A.Name as tName,'表名' as Col1,'中文标题' as Col2,'说明' as Col3,
'' as Col4,'' as Col5,'' as Col6,'' as Col7,'' as Col8,'' as Col9 Into #t
From SysObjects A Where A.xtype='U'
Union
Select 2 as Type,A.Name as tName,A.Name as Col1,null as Col2,C.Value as Col3,
'' as Col4,'' as Col5,'' as Col6,'' as Col7,'' as Col8,'' as Col9
From SysObjects A Left join sysproperties C on A.id=C.id and C.smallid=0 and C.Type=3
Where A.xtype='U'
Union
Select 3 as Type,A.Name as tName,'字段' as Col1,'中文标题' as Col2,'顺序' as Col3,
'字段类型' as Col4,'字段长度' as Col5,'允许为空' as Col6,'字段说明' as Col7,'自动增量' as Col8,
'主键' as Col9
From SysObjects A Where A.xtype='U'
Union
Select 4 as Type,A.Name as tName,B.Name as Col1,'' as Col2,Convert(Nvarchar,B.ColOrder) as Col3,
D.Name+Case When D.Name in ('Numeric','Decimal') then '('+Convert(Nvarchar,B.xprec)+','+Convert(Nvarchar,B.xscale)+')'
else '' end as Col4,Convert(Nvarchar,B.Length) as Col5,
Case When B.isnullable=1 then '是' else '否' end as Col6,
C.Value as Col7,Case When B.autoval is not null then '是' else '' end as Col8,
Case When S.tName is null then '' else '是' end as Col9
From SysObjects A Inner join SysColumns B on A.id=b.id
Left join sysproperties C on A.id=C.id and B.colid=C.smallid and C.type=4
Left join systypes D on B.xtype=D.xtype and B.xUserType=D.xusertype
Left join (Select A.Name as tName,B.Name as ColName
From SysObjects A Inner join SysColumns B on A.id=b.id
Inner join SysObjects D on A.id=D.Parent_Obj and D.Xtype='PK'
Inner join sysindexes C on A.id=C.id and D.Name=C.Name
Inner join sysindexkeys S on C.id=S.id and C.indid=S.indid and B.colid=S.colid
Where A.Xtype='U') S on A.Name=S.tName and B.Name=S.ColName
Where A.xtype='U'
Order By tName,Type,Col3
Select Type,tName,Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9 From #t Order By tName,Type,Case When Type=4 then Convert(int,Col3) else 0 End
drop table #t
- 汇出数据表字段(&属性)清单
- 数据表字段属性查询
- 修改mysql数据表中的字段属性
- Activeform表单提交非数据表字段属性
- SQL批量删除指定数据表中的所有字段说明属性
- 通过SQL Script查看数据表各个字段的属性。
- mybatis中类属性和数据表字段不一致的解决方法
- SQL数据表字段类型与属性总结(DDL)
- android sqlite查询数据表的字段与相关属性
- hive的元数据表清单
- OpenX数据表添加字段
- oracle 修改数据表字段
- 更新数据表中的字段
- sql数据表字段操作
- 新增数据表或字段
- Java实体Date类型属性值无法保存到数据表对应字段的问题
- 没有主清单属性
- Django model字段类型清单
- 编译64位版本的boost
- 常见的几个Qt编程问题的处理
- ASP.NET获取IP的6种方法
- Jakarta commons——Collections
- GridView中使用DataKeyNames存储数据键值
- 汇出数据表字段(&属性)清单
- C++读写文本文件
- Avalon接口类型浅析——Avalon-MM接口与Avalon-ST接口辨析
- Qt中使用QtSql问题
- 彩色UML学习笔记
- C++中的内联函数
- 如何判断浏览器版本
- 恩开始在这里写blog了
- Linux platform 机制探讨