MSSQL--查看表结构

SELECT syscolumns.name,
       systypes.name,
       syscolumns.isnullable,
       syscolumns.length
FROM   syscolumns, systypes
WHERE  syscolumns.xusertype = systypes.xusertype
       AND syscolumns.id = object_id('BasicRoomType')
       order by syscolumns.name;

 

字段描述信息：

DECLARE @table_name AS VARCHAR (MAX);
SET @table_name = 'Hotel';

SELECT   sys.columns.name,
         sys.types.name,
         sys.columns.max_length,
         sys.columns.is_nullable,
         (SELECT count(*)
          FROM   sys.identity_columns
          WHERE  sys.identity_columns.object_id = sys.columns.object_id
                 AND sys.columns.column_id = sys.identity_columns.column_id) AS is_identity,
         (SELECT value
          FROM   sys.extended_properties
          WHERE  sys.extended_properties.major_id = sys.columns.object_id
                 AND sys.extended_properties.minor_id = sys.columns.column_id) AS description
FROM     sys.columns, sys.tables, sys.types
WHERE    sys.columns.object_id = sys.tables.object_id
         AND sys.columns.system_type_id = sys.types.system_type_id
         AND sys.tables.name = @table_name
ORDER BY sys.columns.column_id;