• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    获取SQL Server表字段的各种属性实例代码

    复制代码 代码如下:

    -- 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'为你想要查找的数据表。

    上一篇:如何将sql执行的错误消息记录到本地文件中实现过程
    下一篇:SQLServer日志清空语句(sql2000,sql2005,sql2008)
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    获取SQL Server表字段的各种属性实例代码 获取,SQL,Server,表,字段,的,