创建存储过程及调用存储过程

If exists (select name from sysobjects where name='SP_TBSTRU' and xtype='P')
   Drop procedure dbo.SP_TBSTRU
go

CREATE PROCEDURE SP_TBSTRU
@Dbname  varchar(30) ='BHDB',
@Tbname  varchar(30) ='ABANK'
AS
If not object_id('tempdb..#TEMP')  is null
      Drop table #TEMP
-- set @dbname=''
-- SET @TBNAME=''

SELECT
    Dbname       = @dbname,
    Tbname       = @Tbname,--case when a.colorder=1 then LEFT(d.name,30) else '' end,
    Itm          = 100,
    Fldname     = LEFT(a.name,30),
    Fldkey       = 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,   
    Fldtype       = LEFT(b.name,20),
    Fldsize = a.length,
    Fldlength       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
    Flddec     =a.scale,
    Fldnull =a.isnullable,
    Flddefault     = CAST(isnull(e.text,'') as varchar(254)),
    Tbexplain     = CAST(case when a.colorder=1 then isnull(f.value,'') else '' end as varchar(254)),
    Fldexplain  = CAST(isnull(g.[value],'') as varchar(254))
    INTO #TEMP
 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=@TBname

SELECT * FROM #TEMP
drop table #temp

GO

--调用示例:

 exec SP_TBSTRU @Dbname ='BHDB',@Tbname='ABACC'

 

--xtype 类型说明

 xtype   char(2)   对象类型。可以是下列对象类型中的一种：    
  C   =   CHECK   约束  
  D   =   默认值或   DEFAULT   约束  
  F   =   FOREIGN   KEY   约束  
  L   =   日志  
  FN   =   标量函数  
  IF   =   内嵌表函数  
  P   =   存储过程  
  PK   =   PRIMARY   KEY   约束（类型是   K）  
  RF   =   复制筛选存储过程  
  S   =   系统表  
  TF   =   表函数  
  TR   =   触发器  
  U   =   用户表  
  UQ   =   UNIQUE   约束（类型是   K）  
  V   =   视图  
  X   =   扩展存储过程