SQL2000、SQL2005和SQL2008用户表字典视图

 SQL2000用户表字典视图

CREATE VIEW [dbo].[v_table_filed]
 AS
SELECT     c.name AS tables, a.name AS field, b.name AS datatype, STR(COLUMNPROPERTY(a.id, a.name, 'PRECISION')) AS length, a.isnullable,
                      ep.value AS ExtProperty
FROM         syscolumns AS a LEFT OUTER JOIN
                     systypes AS b ON a.xtype = b.xusertype INNER JOIN
                     sysobjects AS c ON a.id = c.id AND c.xtype = 'U' AND c.name <> 'dtproperties' LEFT OUTER JOIN
                     sysproperties AS ep ON ep.id = c.id AND ep.smallid = a.colid

 

SQL2005和SQL2008用户表字典视图

CREATE VIEW [dbo].[v_table_filed]
AS
SELECT     c.name AS tables, a.name AS field, b.name AS datatype, STR(COLUMNPROPERTY(a.id, a.name, 'PRECISION')) AS length, a.isnullable,
                      ep.value AS ExtProperty
FROM         sys.syscolumns AS a LEFT OUTER JOIN
                      sys.systypes AS b ON a.xtype = b.xusertype INNER JOIN
                      sys.sysobjects AS c ON a.id = c.id AND c.xtype IN ('U', 'V') AND c.name <> 'dtproperties' LEFT OUTER JOIN
                      sys.extended_properties AS ep ON ep.major_id = c.id AND ep.minor_id = a.colid