获取SQL Server表字段的各种属性实例代码
发布时间:2024-01-26 13:13:11
标签:表,字段属性
-- SQL Server 2000
SELECT a.name AS 字段名, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = 'PK' AND parent_obj = a.id AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid)))
THEN '1' ELSE '0' END AS 主键, CASE WHEN COLUMNPROPERTY(a.id, a.name,
'IsIdentity') = 1 THEN '1' ELSE '0' END AS 标识, b.name AS 类型,
a.length AS 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度,
a.xscale AS 小数, a.isnullable AS 可空, ISNULL(e.text, '') AS 默认值, ISNULL(g.[value],
'') AS 字段说明
FROM syscolumns a LEFT OUTER JOIN
systypes b ON a.xusertype = b.xusertype INNER JOIN
sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
d.name <> 'dtproperties' LEFT OUTER JOIN
syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
sysproperties g ON a.id = g.id AND a.colid = g.smallid LEFT OUTER JOIN
sysproperties f ON d.id = f.id AND f.smallid = 0
WHERE (d.name = '表名称')
--2。SQL SERVER 2005
SELECT CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = 'PK' AND parent_obj = a.id AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key', CASE WHEN COLUMNPROPERTY(a.id, a.name,
'IsIdentity') = 1 THEN '1' ELSE '0' END AS 'identity', a.name AS ColName, c.name AS TypeName, a.length AS 'byte', COLUMNPROPERTY(a.id, a.name,
'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'
FROM sys.syscolumns AS a INNER JOIN
sys.sysobjects AS b ON a.id = b.id INNER JOIN
sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id
WHERE (b.name = 'keyfactory') AND (c.status <> '1')
--b.name = 'Keyfactory','Keyfactory'为你想要查找的数据表。
--2、SQL SERVER 2005
SELECT CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = 'PK' AND parent_obj = a.id AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = a.id AND colid = a.colid))) THEN '1' ELSE '0' END AS 'key', CASE WHEN COLUMNPROPERTY(a.id, a.name,
'IsIdentity') = 1 THEN '1' ELSE '0' END AS 'identity', a.name AS ColName, c.name AS TypeName, a.length AS 'byte', COLUMNPROPERTY(a.id, a.name,
'PRECISION') AS 'length', a.xscale, a.isnullable, ISNULL(e.text, '') AS 'default', ISNULL(p.value, '') AS 'comment'
FROM sys.syscolumns AS a INNER JOIN
sys.sysobjects AS b ON a.id = b.id INNER JOIN
sys.systypes AS c ON a.xtype = c.xtype LEFT OUTER JOIN
sys.syscomments AS e ON a.cdefault = e.id LEFT OUTER JOIN
sys.extended_properties AS p ON a.id = p.major_id AND a.colid = p.minor_id
WHERE (b.name = 'keyfactory') AND (c.status <> '1')
--b.name = 'Keyfactory','Keyfactory'为你想要查找的数据表。
0
投稿
猜你喜欢
- 对于小数据量,xml文件在检索更新上于ACCESS有很多优势。我曾经测试过不用数据库,把网站的会员信息,商品数据信息,交易信息,网站定制信息
- 本文实例讲述了Flask框架 CSRF 保护实现方法。分享给大家供大家参考,具体如下:Flask CSRF 保护为什么需要 CSRF?具体操
- 一、自定义分页1、基础版自定义分页data = []for i in range(1, 302): tmp = {"i
- 一. pprint美观打印数据结构pprint模块包含一个“美观打印机”,用于生成数据结构的一个美观的视图。格式化工具会生成数据结构的一些表
- 现在使用CSS网页布局,摒弃了传统Table表格布局的模式,但是Table表格在网页中还是少不了的,因为当需要输出表格类数据时,就应该使用表
- 实例如下所示:import osimport stringpath = "/Users/U/workspace/python le
- 为了降低用户注册难度,国际站的主注册表单一直在改进。主注册三月至今发生了两次较大的变化,现在对表单调整的地方分解如下:1. 两步
- 原因:list 获得的数据为空: 显示值为 [ ]不同的判断--- is None----not两者结果不一样分析:总之:not 判断的是内
- 脚本调试第一步:设置中断(鼠标左键点击)第二步:输入中断条件(可选功能,鼠标右键点击红点)第三步:触发中断(当符合条件是,中断被触发)出现中
- 一、描述:以module的方式组件python代码,在磁盘文件清理上复用性更好二、达到目标: 清空过期
- 在用到编辑器时,就会碰到一点,那就是标签的闭合问题,这个问题非常严峻,因为这可能会导致网页显示的整体样式受到破坏。这最近在PJ的functi
- 最近的答题赢钱很火爆,我也参与了几次,有些题目确实很难答,但是10秒钟的时间根本不够百度的,所以写了个辅助挂,这样可以出现题目时自动百度,这
- 除了常用的csv文件和excel文件之外,我们还可以通过PY把数据保存文npy文件格式和mat文件格式。1. npy文件npy即numpy对
- 摘要:现代网站和web应用程序趋向于依赖客户端的大量的javascript来提供丰富的交互。特别是通过不刷新页面的异步请求来返回数据或从服务
- 我们在爬虫时,经常会遇见中文乱码问题,之前都是编码格式的问题,很少遇见由于压缩格式造成的编码混乱问题,特记录下。先看下混乱的编码样式。b&#
- 本文实例讲述了Python发送邮件功能。分享给大家供大家参考,具体如下:这里以QQ邮箱为例说明登录邮箱点账号开启smtp开启时会要求你发送一
- 项目开发中,前端在配置后端api域名时很困扰,常常出现:本地开发环境: api-dev.demo.com测试环境: api-test.dem
- 最近打开电脑wifi连接老是出现各种问题,于是突发奇想,我自己能不能做一个wifi连接的小工具岂不是就没有这些麻烦了,居然成功了。为了方便不
- 本文实例为大家分享了python实现通讯录管理系统的具体代码,供大家参考,具体内容如下题目期末项目 一. 项目要求利用函数实现通讯
- 本来是只用Tenorflow的,但是因为TF有些Numpy特性并不支持,比如对数组使用列表进行切片,所以只能转战Pytorch了(pytor