컴퓨토피아

declare @tab varchar(256)
declare tabinfo cursor for
select name from sysobjects where xtype='u' order by name --사용자 정의 테이블
open tabinfo
fetch from tabinfo into @tab
while @@fetch_status=0
begin
 select @tab

 select
  syscolumns.name,
  ISNULL(value, '') as FLD_DESC,
  systypes.name,
  syscolumns.length,
  case isnullable when 0 then 'NOT NULL' else 'NULL'
  end 'Nullable'
 from
  systypes,
  syscolumns left outer join
  ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table',@tab, 'column', default) c
  on (syscolumns.name = c.objname collate Korean_Wansung_CI_AS )
 where
  syscolumns.id=object_id(@tab)
 and syscolumns.xusertype =systypes.xusertype
 order by colid
 fetch from tabinfo into @tab
end
close tabinfo
deallocate tabinfo

출처 : http://kr.blog.yahoo.com/fazio127/7

ALTER PROC USP_TAB_INFO (
   @DBNAME VARCHAR(100)=''
 , @TABLENAME VARCHAR(100) = ''
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL VARCHAR(4000)
IF @TABLENAME=''
             set @sql='select b.name ColumnName, c.name TypeName, b.length ColumnLength
                                        , Case b.IsNullable when 1 then '''' else ''Not Null'' end Nullable
                                        , Case When g.name is null Then '''' Else ''PK'' End PKwithCidx
                                        , IsNull(d.value,'''') Caption 
                           from '+@DbName+'..sysobjects a
                                        JOIN '+@DbName+'..syscolumns b ON (a.id = b.id and a.type=''U'' and a.status > 0)
                                        JOIN '+@DbName+'..systypes c ON (b.xtype = c.xtype and c.name<>''sysname'')
                                        LEFT JOIN '+@DbName+'..sysproperties d ON  (b.id=d.id and b.colid = d.smallid)
                                        LEFT JOIN (select name,e.id,colid from '+@DbName+'..sysindexes e join '+@DbName+'..sysindexkeys f on e.id=f.id
                                                     where e.id > 10000000 and e.indid=1 and f.indid=1) g ON (g.id=b.id and g.colid=b.colid)
                           order by a.name, colorder'
ELSE
             set @sql='select   b.name ColumnName, c.name TypeName, b.length ColumnLength
                                        , Case b.IsNullable when 1 then '''' else ''Not Null'' end Nullable
                                        , Case When g.name is null Then '''' Else ''PK'' End PKwithCidx
                                        , IsNull(d.value,'''') Caption 
                           from '+@DbName+'..sysobjects a
                                        JOIN '+@DbName+'..syscolumns b ON (a.id = b.id and a.type=''U'' and a.status > 0 and a.name='''+@TableName+''')
                                        JOIN '+@DbName+'..systypes c ON (b.xtype = c.xtype and c.name<>''sysname'')
                                        LEFT JOIN '+@DbName+'..sysproperties d ON  (b.id=d.id and b.colid = d.smallid)
                                        LEFT JOIN (select name,e.id,colid from '+@DbName+'..sysindexes e join '+@DbName+'..sysindexkeys f on e.id=f.id
                                                     where e.id > 10000000 and e.indid=1 and f.indid=1) g ON (g.id=b.id and g.colid=b.colid)
                           order by a.name, colorder'
EXEC(@SQL)
SET NOCOUNT OFF
END
--USE INSIDER
--GO
--EXEC USP_TAB_INFO 'INSIDER', 'INSIDER_1'