获取所有表及属性

create VIEW [dbo].[VTableInfo]
AS
SELECT DISTINCT
                      obj.name AS TableName, col.colid AS ColumnIndex, col.name AS ColumnName, scol.DATA_TYPE, col.length AS ColumnLength, CONVERT(varchar(500),
                      ISNULL(prop.value, N' ')) AS ColumnDesc
FROM         sys.sysobjects AS obj INNER JOIN
                      sys.syscolumns AS col ON col.id = obj.id INNER JOIN
                      INFORMATION_SCHEMA.COLUMNS AS scol ON scol.TABLE_NAME = obj.name AND scol.COLUMN_NAME = col.name LEFT OUTER JOIN
                      sys.extended_properties AS prop ON prop.major_id = obj.id AND prop.minor_id = col.colid AND prop.name = 'MS_Description'

GO

获取所有表及属性，方便整个库的管理