获取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
投稿
猜你喜欢
- 游戏规则:双方轮流选择棋盘的列号放进自己的棋子,若棋盘上有四颗相同型号的棋子在一行、一列或一条斜线上连接起来,则使用该型号棋子的玩家就赢了!
- 目录什么是分区表分区表应用场景分区表的限制分区类型分区表的使用1、范围分区2、列表分区(list分区)3、列分区4、hash分区5、秘钥分区
- 一、cache介绍由于Django是 * 站,所有每次请求均会去数据进行相应的操作,当程序访问量大时,耗时必然会更加明显,最简单解决方式是使
- 先看看那种容易被注入的SQL id = 11001 sql = """
- 本文介绍了纯python进行矩阵的相乘运算的方法示例,分享给大家,具体如下:def matrixMultiply(A, B):
- 在用户体验量化方法研究(一)中,我们以用户体验层次模型为基础,提出了三种量化用户体验的方法:以任务为中心、以行为为中心以及以体验为中心的量化
- 软件测试面试如何测试网页的登录页面这个面试题碰到过很多次, 再次总结下来。具体需求: 有一个登陆页面, 上面有2个textbox, 一个提交
- 看了大神统计voc数据集标签框后,针对自己标注数据集,灵活应用 ,感谢!看代码吧~import reimport osimport xml.
- 我就废话不多说了,直接上代码吧!#!/usr/bin/env python# coding:UTF-8"""@
- 前言当前很多人在闲暇时喜欢听音乐,那么基于这种现象,我也是肝了几个小时完成了基于python的个性化音乐播放器,现在分享给你们。开发组件py
- 本文实例为大家分享了python使用Plotly绘图工具绘制柱状图的具体代码,供大家参考,具体内容如下使用Plotly绘制基本的柱状图,需要
- 工厂方法(Factory Method)模式又称为虚拟构造器(Virtual Constructor)模式或者多态工厂(Polymorphi
- 1.过滤器的使用1.过滤器和测试器在Python中,如果需要对某个变量进行处理,我们可以通过函数来实现。在模板中,我们则是通过过滤器来实现的
- 功能描述数据库间数据同步方式很多,在上篇博文中有总结。本文是用py程序实现数据同步。A数据库中有几十张表,要汇聚到B数据库中,且表结构一致,
- 增强的toggleClass()1.toggleClass也可以和addClass一样,用空格分隔多个class名称了。.toggleCla
- #coding=gbkfrom xml.dom import minidom,Nodeimport urllib2,re,osdef rea
- 在Python中,json指的是符合json语法格式的字符串,可以单行或者多行。它可以方便的在使用在多种语言中,这里介绍的是在python中
- 外网python2.7 虚拟环境中安装了 flask 模块,期望在内网使用,如何迁移外网的虚拟环境到内网呢?1 进入外网python虚拟环境
- 今天来认识一下两个我不太常用到的html标签:sub标记和sup标记。定义和用法:<sub> 标签可定义下标文本。<sup
- 1.前期准备用户models.pyclass User(models.Model): username = mod