[SQL Server] 테이블목록,필드정보 가져오는 프로시저생성

MS-SQL / SQL Server

테이블목록,필드정보 가져오는 프로시저생성

 

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

create   proc sp_tableform
as
declare @no varchar(35), @yes varchar(35), @none varchar(35)
 select @no = name from master.dbo.spt_values where type = 'B' and number = 0
 select @yes = name from master.dbo.spt_values where type = 'B' and number = 1
 select @none = name from master.dbo.spt_values where type = 'B' and number = 2
 declare @numtypes nvarchar(80)
  select @numtypes = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney'
 select
   so.name as 'table_name', 
   sc.name as 'colum_name' ,
   type_name(sc.xusertype)  as 'Type',
   case when sc.iscomputed = 0 then @no else @yes end as 'Computed',
   convert(int, sc.length) as 'Length',
    case when charindex(type_name(sc.xtype), @numtypes) > 0
   then convert(char(5),ColumnProperty(sc.id, sc.name, 'precision'))
   else '     ' end  as 'Prec',
    case when charindex(type_name(sc.xtype), @numtypes) > 0
   then convert(char(5),OdbcScale(sc.xtype,sc.xscale))
   else '     ' end  as 'Scale',
   case when sc.isnullable = 0 then @no else @yes end  as 'Nullable',
   sp.value 'value'
  from sysobjects so, syscolumns sc,sysproperties sp
   where so.xtype='U' and sc.id = so.id  and so.id*=sp.id and sc.colid *= sp.smallid
   and sc.number = 0 order by so.name, sc.colid

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO