聽說幸福…在山的那邊 (浪機子)

/****** Object:  StoredProcedure [dbo].[SP_Creat_Table]    Script Date: 2017/2/18 下午 01:58:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  產生create Table的語法

-- declare @SQL as varchar(max) exec [sp_creat_table] 'DBName','TableName',@SQL OUTPUT

-- 傳入DBNAME,TableName,會組好語法回傳
-- =============================================

ALTER proc [dbo].[SP_Creat_Table]
(
@db as varchar(100)
,@tab as varchar(100)
,@SQL as varchar(MAX) OUTPUT
)
AS

--declare @SQL nvarchar(4000)=''
set @SQL=''
declare @tablename as varchar(50)
declare @tablename2 as varchar(50)
declare @columnname as varchar(50)
declare @typename as varchar(20)
declare @lenname as varchar(10)
declare @intname as varchar(3)
declare @doname as varchar(2)
declare @sqlstr as varchar(MAX)
declare @sqlstr2 as varchar(MAX)

print '[SP_Creat_Table] ' + @db + ',' + @tab + ','+ @SQL
--set @db='fz_BSP_Retail'
--set @tab='ACHMRDEP_NEW_2015'

set @tablename2='first'

exec('if object_id(''tempdb..##creat_table'') is not null
drop table ##creat_table

select a.name tablename,b.name columnname,c.name typename,b.length,b.xprec,b.xscale,b.colorder
into ##creat_table
from '+@db+'.dbo.sysobjects a,
     '+@db+'.dbo.syscolumns b,
     '+@db+'.dbo.systypes c
where a.xtype=''U''
and a.name='''+@tab+'''
and a.id=b.id and b.xtype=c.xtype and c.name<>''sysname''
order by a.name,b.colorder
')

DECLARE authors_cursor CURSOR FOR
select tablename,columnname, typename,length,xprec,xscale  from ##creat_table order by colorder

OPEN authors_cursor
FETCH NEXT FROM authors_cursor into @tablename,@columnname,@typename,@lenname,@intname,@doname
WHILE @@FETCH_STATUS = 0
Begin
 if @tablename2<>@tablename    
 begin
  set @sqlstr2='if (object_id('''+@db+'.dbo.'+@tablename+''')is not null)  drop table '+@db+'.dbo.'+@tablename
  --print (@sqlstr2)
  set @SQL += @sqlstr2
    
  if @tablename2<>'first'
  begin
   select @sqlstr=substring(@sqlstr,0,len(@sqlstr))+')'
   ---(@sqlstr)
   set @SQL += @sqlstr
   
   set @sqlstr=''
  end

  set @sqlstr=' create table '+@db+'.dbo.'+@tablename + ' ( ' + @columnname + ' ' +@typename
  if @typename in ('decimal','numeric')
  begin
   set @sqlstr=@sqlstr+' ('+@intname+','+@doname+')'
  end
  if @typename in ('varchar','char','nvarchar')
  begin
   set @sqlstr=@sqlstr+' ('+@lenname+')'
  end
  
  set @sqlstr=@sqlstr+','
  set @tablename2=@tablename
 end
 else
 begin
  set @sqlstr=@sqlstr+@columnname + ' ' +@typename
  
  if @typename in ('decimal','numeric')
  begin
   set @sqlstr=@sqlstr+' ('+@intname+','+@doname+')'
  end
  
  if @typename in ('varchar','char','nvarchar')
  begin
   set @sqlstr=@sqlstr+' ('+@lenname+')'
  end
  
  set @sqlstr=@sqlstr+','
 end
 
 FETCH NEXT FROM authors_cursor into @tablename,@columnname,@typename,@lenname,@intname,@doname
end

select @sqlstr=substring(@sqlstr,0,len(@sqlstr))+')'
--print(@sqlstr)
set @SQL += @sqlstr

CLOSE authors_cursor
DEALLOCATE authors_cursor

***********************************************************************

呼叫方式，在另一個Store Procedure的使用方式
DECLARE @ReturnSQL as nvarchar(max) 
EXEC  [SP_Creat_Table] 'DBName','TableName',@ReturnSQL OUTPUT

print @ReturnSQL  =>即是create table scrip語法

***********************************************************************