首页 > 其他 > 详细

查询表结构视图

时间:2016-09-06 12:19:51      阅读:210      评论:0      收藏:0      [点我收藏+]

/****** Object: View [dbo].[ViewTable] Script Date: 09/06/2016 11:12:28 ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: gengc
-- Create date: <2012-12-29>
-- Description: <查看表结构>
-- =============================================
CREATE VIEW [dbo].[ViewTable]
AS
SELECT
obj.name AS ‘TableName‘
,c.name AS ‘字段名称‘
,ISNULL(etp.value,‘‘) AS ‘字段描述‘
,t.name AS ‘字段类型‘
,c.Length AS ‘占用字节‘
,COLUMNPROPERTY(c.id,c.name,‘PRECISION‘) AS ‘长度‘
,ISNULL(COLUMNPROPERTY(c.id,c.name,‘Scale‘),0) AS ‘小数位数‘
,CASE(c.isnullable) WHEN ‘1‘ THEN ‘√‘ ELSE ‘‘ END AS ‘是否为空‘
,ISNULL(cm.text,‘‘) AS ‘默认值‘
,CASE(
(SELECT 1 FROM sysobjects WHERE xtype=‘PK‘ AND parent_obj=c.id AND name IN (
SELECT name FROM sysindexes WHERE indid IN(
SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid)))
) WHEN ‘1‘ THEN ‘√‘ ELSE ‘‘ END AS ‘是否主键‘
,CASE(COLUMNPROPERTY(c.id,c.name,‘IsIdentity‘)) WHEN ‘1‘ THEN ‘√‘ ELSE ‘‘ END AS ‘自动增长‘
FROM syscolumns c
INNER JOIN systypes t ON c.xusertype = t.xusertype
LEFT JOIN sys.extended_properties etp ON etp.major_id = c.id AND etp.minor_id = c.colid AND etp.name =‘MS_Description‘
LEFT JOIN syscomments cm ON c.cdefault=cm.id
LEFT JOIN sysobjects obj ON c.id=obj.id

GO

 

查询表结构视图

原文:http://www.cnblogs.com/chengeng/p/5845028.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!