Table 명세서 보기

/**********************************************

 테이블의 컬럼정보 보기

**********************************************/

declare @tb_name nvarchar(30)
declare @tb_Id    int

/******** Input Table Name ********/
set @tb_name = ''

select @tb_id = id
  from sysobjects
  where name = @tb_name

 

 

select a.colid as NO
    , a.name      as ColumnName
    , b.name      as DataType
    , CASE WHEN b.name IN ('numeric', 'decimal') --18.5 형태의 표현
      THEN convert(varchar(100),a.xprec) + '.' + convert(varchar(100),a.xscale)
     WHEN b.name IN ('int','smallint')   --해당열 empty space
      THEN ''  
     ELSE convert(varchar(100),a.length) END      as DataLength   
    , CASE a.isnullable WHEN 0 THEN 'NOT NULL'
             WHEN 1 THEN 'NULL' END   as NullValue
    , c.value       as Description
from syscolumns a
  inner join systypes b
  on a.xusertype = b.xusertype
  left outer join SYS.EXTENDED_PROPERTIES c
  on a.id = c.major_id and a.colid = c.minor_id
  where a.id = @tb_id
order by a.colid