查询获取表中, 字段,数据类型的sql

存档一个今天用到的sql,  可以用来查询表中, 字段, 数据类型的sql.

SELECT OBJECT_NAME(c.id) AS 表名, c.name AS 字段名, t.name AS 数据类型, 
      c.prec AS 长度
FROM syscolumns c INNER JOIN
      systypes t ON c.xusertype = t.xusertype
WHERE (objectproperty(c.id, 'IsUserTable') = 1) AND 
      (c.id = OBJECT_ID('你的表名'))

 

如果要查视图的话, 要修改一下where条件,  把where的objectproperty放到上面增加一个case判断,  AS 出类型

SELECT     CASE WHEN (OBJECTPROPERTY(c.id, 'IsUserTable') = 1) THEN 'Table' WHEN (OBJECTPROPERTY(c.id, 'IsView') = 1) THEN 'View' END AS 类型, OBJECT_NAME(c.id) 
                      AS 表名, c.name AS 字段名, t.name AS 数据类型, c.prec AS 长度
FROM         sys.syscolumns AS c INNER JOIN
                      sys.systypes AS t ON c.xusertype = t.xusertype
WHERE     (c.id = OBJECT_ID('View_1'))

(OBJECTPROPERTY(c.id, ‘IsUserTable’) = 1) 是表,  (OBJECTPROPERTY(c.id, ‘IsView’) = 1) 是视图

关于 OBJECTPROPERTY 的详细用法和参数, 请查看: http://technet.microsoft.com/zh-cn/library/ms176105(v=sql.90).aspx