没有合适的资源?快使用搜索试试~ 我知道了~
SQL2008利用数据函数快速查看数据表字段的详细信息,方便快速查询
资源推荐
资源详情
资源评论
DECLARE @tableName VARCHAR(100)--表名
SET @tableName = '表名'
SELECT obj.name AS 表名,
col.colorder AS 序号,
col.name AS 列名,
ISNULL(ep.[value], '') AS 列说明,
t.name AS 数据类型,
col.length AS 长度,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END AS 标识,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = col.id AND sc.colid = col.colid ) THEN '√'
ELSE ''
END AS 主键,
CASE WHEN col.isnullable = 1 THEN '√'
ELSE ''
END AS 允许空,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT OUTER JOIN dbo.systypes t ON col.xtype = t.xusertype
INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0
LEFT OUTER JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT OUTER JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'
SET @tableName = '表名'
SELECT obj.name AS 表名,
col.colorder AS 序号,
col.name AS 列名,
ISNULL(ep.[value], '') AS 列说明,
t.name AS 数据类型,
col.length AS 长度,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√'
ELSE ''
END AS 标识,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = col.id AND sc.colid = col.colid ) THEN '√'
ELSE ''
END AS 主键,
CASE WHEN col.isnullable = 1 THEN '√'
ELSE ''
END AS 允许空,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFT OUTER JOIN dbo.systypes t ON col.xtype = t.xusertype
INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0
LEFT OUTER JOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFT OUTER JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'
资源评论
qq_16697501
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功