SQL Server 2008 R2:纯SQL查看数据库描述文档
来源:互联网 发布:冬天保湿面霜推荐 知乎 编辑:程序博客网 时间:2024/05/01 01:07
/***********************************
描述:纯SQL查看数据库描述文档
作者:jehn
联系:jehn@foxmail.com
时间:2011-12-07 14:33:30
************************************/
SELECT
(case when a.colorder=1 then
(
select CAST(表序号 as nvarchar) from
(SELECT d.name,
row_number() over(order by d.name) 表序号
FROM syscolumns a join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where a.colorder=1) as tmp where name=d.name
)
else '' end) 表序号,
(case when a.colorder=1 then d.name else '' end) 表名,
(case when a.colorder=1 then
(
select value from sys.extended_properties where minor_id=0 and major_id= OBJECT_ID(d.name)
) else '' end) 表描述,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in (SELECT name
FROM 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) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
--isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
COLUMNPROPERTY(a.id,a.name,'Scale') 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id AND a.colid = g.minor_id --where d.name='T_SickRoom'
order by d.name,a.colorder
------where d.name='T_SickRoom' 也可以指定单张表
- SQL Server 2008 R2:纯SQL查看数据库描述文档
- SQL Server 2008 R2:纯SQL查看数据库描述文档
- sql server 2008 r2 查看端口号
- sql server 2008 R2不能新建数据库
- Sql Server 2008 R2连接远程数据库
- java连接sql server 2008 R2数据库
- SQL Server 2008 R2 主从数据库同步
- SQL Server 2008 R2 新建数据库
- SQL SERVER 2008 R2 数据库自动备份
- SQL SERVER 2008 R2 远程备份数据库
- SQL SERVER 2008 R2 数据库下载
- SQL Server 2008R2 数据库邮件
- SQL Server 2008 R2数据库安装
- SQL Server 2008 R2 主从数据库同步
- SQL Server 2008 R2 AdventureWorks 数据库安装
- JFinal连接SQL Server 2008 R2数据库
- SQL Server 2008 /SQL Server 2008 R2 配置数据库邮件
- SQL Server 2008、SQL Server 2008R2 自动备份数据库
- http://www.cnblogs.com/zc22/archive/2007/12/07/986978.html
- 单链表和双链表的区别
- Visual Edit教程之创建一个登陆页面
- 【多媒体封装格式详解】---FLV
- VC++ MFC文件的移动复制删除更名遍历操作
- SQL Server 2008 R2:纯SQL查看数据库描述文档
- Hibernate的merge()方法
- ural 1078 Segments
- Nhibaernate 在使用Iset<>Invalid Cast错误
- 如何在ashx页面获取Session值
- 【Android进阶】嵌套TabHost (TabHost中放TabHost,类似二级目录、二级树)
- 选择排序法 (Selection Sort)
- event.altKey、event.ctrlKey、event.shiftKey 属性
- Android Textview 延时加载图片