IT狂想曲~

--SQL 2000

Declare @bastable Varchar(50)
Set @bastable = 'Table1'
Select dt.name [Table_name], C.name [Column_name],
    Case
  When T.name In ('datetime', 'smalldatetime', 'int', 'smallint')
      Then T.name
  When T.name IN ( 'numeric' )
      Then T.name + '(' + CAST(ISNULL(C.prec, C.length) AS varchar(50)) + ',' + CAST(C.scale as varchar(50) ) + ')'
  When T.name IN ( 'nvarchar' )
      Then T.name + '(' + CAST(C.length/2 AS varchar(50)) + ')'
  Else
      T.name + '(' + CAST(C.length AS varchar(50)) + ')'
    End [Type],
    Case
        When isnullable = 0 THEN 'NOT NULL'
        When isnullable = 1 THEN 'NULL'
    End [IsNULL] ,
    Convert(varchar(100) ,P.value)  [Desc1]
From  ( Select id , name FROM SysObjects WHERE xtype = 'U' ) dt
Left Join SysColumns C On C.id=dt.id
Left Join SysProperties P On C.id=P.id and C.colid=P.smallid
LEFT JOIN SysTypes T ON C.xtype = T.xusertype
Where dt.name = @bastable
Order By C.colorder


--SQL 2005
Declare @bastable Varchar(50)
Set @bastable = 'Table1'

Select dt.name [Table_name], C.name [Column_name],
    Case
  When T.name In ('datetime', 'smalldatetime', 'int', 'smallint')
      Then T.name
  When T.name In ( 'numeric' )
      THEN T.name + '(' + CAST(ISNULL(C.prec, C.length) AS varchar(50)) + ',' + CAST(C.scale as varchar(50) ) + ')'
  When T.name In ( 'nvarchar' )
      THEN T.name + '(' + CAST(C.length/2 AS varchar(50)) + ')'
  Else
      T.name + '(' + CAST(C.length AS varchar(50)) + ')'
    End [Type] ,
    Case
        WHEN isnullable = 0 THEN 'NOT NULL'
        WHEN isnullable = 1 THEN 'NULL'
  End [IsNULL],
  convert(varchar(100) ,P.value)  [Desc1]
From ( Select id , name FROM sys.SysObjects WHERE xtype = 'U' ) dt
Left Join sys.SysColumns C On C.id=dt.id
Left Join sys.extended_properties P On C.ID=P.Major_Id And C.ColId=P.Minor_id
Left JOIN sys.SysTypes T ON C.xtype = T.xusertype
Where dt.name = @bastable
order by C.colorder