使用SQL语句快速获取SQL Server数据字典

作者:小乔 时间:2009-01-08 16:31:00 

本文主要介绍了一个获取SQL Server数据字典的经典SQL语句,大家可以根据各自的实际情况对这段语句进行相应的修改。

SELECT sysobjects.name AS [table], sysproperties.[value] AS 表说明,
syscolumns.name AS field, properties.[value] AS 字段说明, systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name,
'Scale'), 0) AS 小数位数, syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL
THEN '' ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity')
= 1 THEN '√' ELSE '' END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = 'PK' AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN '√' ELSE '' END AS 主键
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = 'U')

标签:
0
投稿

猜你喜欢

  • python opencv鼠标画点之cv2.drawMarker()函数

    2021-11-28 18:34:40
  • mysql中group by与having合用注意事项分享

    2024-01-15 02:24:55
  • firebug1.7调试实用技巧指南

    2011-06-27 20:17:22
  • python处理xml文件操作详解

    2021-11-10 10:31:24
  • 22个HTML5的初级技巧

    2010-12-17 12:39:00
  • 使用PHP实现生成HTML静态页面

    2023-11-14 11:14:41
  • 内网ssh/mysql登录缓慢的解决方法

    2024-01-15 15:41:44
  • 如何基于python对接钉钉并获取access_token

    2023-11-27 04:25:07
  • pandas 对series和dataframe进行排序的实例

    2023-07-16 04:57:50
  • js断点调试经验分享

    2023-08-15 06:19:09
  • python 用opencv实现图像修复和图像金字塔

    2022-09-27 09:24:22
  • python多线程和多进程关系详解

    2023-03-08 22:21:54
  • 基于OpenCV python3实现证件照换背景的方法

    2023-01-30 06:25:54
  • 详解Python 定时框架 Apscheduler原理及安装过程

    2021-06-16 15:15:09
  • python numpy实现rolling滚动案例

    2023-08-24 17:12:45
  • vue实现触底查询功能

    2024-04-30 10:25:01
  • python 实现判断ip连通性的方法总结

    2023-06-24 22:36:53
  • Python操作MySQL数据库的两种方式实例分析【pymysql和pandas】

    2024-01-13 13:34:59
  • Python 实现还原已撤回的微信消息

    2023-07-07 06:07:56
  • MySQL数据库线程缓冲池详解

    2012-04-13 11:48:01
  • asp之家 网络编程 m.aspxhome.com