在SQL Server 2000 和SQL Server 2005中导出表结构

SQL Server 2000

SELECT
    表名       = case when a.colorder=1 then d.name else '' end,
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,
    字段名     = a.name,
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
                     SELECT name FROM sysindexes WHERE indid in(
                        SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
    类型       = b.name,
    占用字节数 = a.length,
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    默认值     = isnull(e.text,''),
    字段说明   = isnull(g.[value],'')
FROM
    syscolumns a
left join
    systypes b
on
    a.xusertype=b.xusertype
inner join
    sysobjects d
on
    a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties'
left join
    syscomments e
on
    a.cdefault=e.id
left join
    sysproperties g
on
    a.id=g.id and a.colid=g.smallid 
left join
    sysproperties f
on
    d.id=f.id and f.smallid=0
where
    d.name='LW_Equipment_Stock_List'    --如果只查询指定表,加上此条件

 

SQL Server 2005

SELECT 
    TableName               =   CASE WHEN A.COLORDER=1 THEN D.NAME ELSE ' ' END,
    TableDescription        =   CASE WHEN A.COLORDER=1 THEN ISNULL(F.VALUE, ' ') ELSE   ' '   END,
    [ColumnSort]            =   A.COLORDER,
    FieldName                =   A.NAME,
    IsIdentity                =   CASE   WHEN   COLUMNPROPERTY(   A.ID,A.NAME, 'ISIDENTITY ')=1   THEN   '√ 'ELSE   ' '   END,
    IsPrimaryKey            =   CASE   WHEN   EXISTS(SELECT   1   FROM   SYSOBJECTS   WHERE   XTYPE= 'PK '   AND   PARENT_OBJ=A.ID   AND   NAME   IN   (
        SELECT   NAME   FROM   SYSINDEXES   WHERE   INDID   IN(
        SELECT   INDID   FROM   SYSINDEXKEYS   WHERE   ID   =   A.ID   AND   COLID=A.COLID)))   THEN   '√ '   ELSE   ' '   END,
    DataType               =   B.NAME,
    [SetLength]               =   A.LENGTH,
    [DataLength]           =   COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '),
    [DecimalDigit]           =   ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0),
    [IsNull]               =   CASE   WHEN   A.ISNULLABLE=1   THEN   '√ 'ELSE   ' '   END,
    [DefaultValue]         =   ISNULL(E.TEXT, ' '),
    [ColumnDescription]       =   ISNULL(G.[VALUE], ' ')
FROM 
    SYSCOLUMNS A
    LEFT JOIN 
    SYSTYPES B 
ON 
    A.XUSERTYPE=B.XUSERTYPE
    INNER JOIN 
    SYSOBJECTS D 
ON 
    A.ID=D.ID AND D.XTYPE= 'U ' AND D.NAME <> 'DTPROPERTIES '
    LEFT JOIN 
    SYSCOMMENTS E 
ON 
    A.CDEFAULT=E.ID
    LEFT JOIN 
        sys.extended_properties G 
ON 
    A.ID=G.major_id AND A.COLID=G.minor_id   
    LEFT JOIN 
        sys.extended_properties F 
ON 
    D.ID=F.major_id AND F.minor_id=0
ORDER BY 
    A.ID,A.COLORDER