刷新SP到一数据库方便制作XSD文件

----------------------------------------------------------------------------------
--        刷新SP到数据库         --
--                    --
-- &Old& 来源数据库名               --
-- &New& 目标数据库名               --
--                    --
-- 目标数据库中表名为来源数据库中的用户自定义SP、FN等(可按提示添加--详细提示以后加) --
--    表中的列名则为其参数            --
--                    --
-- 黄宗银                  --
-- 2005.01.19                 --
----------------------------------------------------------------------------------
ALTER PROCEDURE dbo.A_Ref
AS
 DECLARE @SQL nvarchar(4000)

 -- 不存在数据库则创建
 IF NOT EXISTS (
  SELECT [name]
  FROM master.dbo.sysdatabases
  WHERE [name] LIKE '&New&'
  )
 BEGIN
  CREATE DATABASE &New&
 END

 -- 删除已存在的表
 DECLARE @Temp table
  (
   [name] nvarchar(50)
  )
 INSERT INTO @Temp
 SELECT [name]
 FROM &New&.dbo.sysobjects
 WHERE (type LIKE 'U' )
  AND ([name] LIKE 'P%' OR [name] LIKE 'F%' OR [name] LIKE 'IF%' OR [name] LIKE 'TF%')

 DECLARE @DelCursor CURSOR
 SET  @DelCursor = CURSOR LOCAL SCROLL FOR
 SELECT [name]
 FROM @Temp

 DECLARE @Del nvarchar(50)

 OPEN @DelCursor
 FETCH NEXT FROM @DelCursor INTO @Del
 WHILE( @@FETCH_STATUS = 0 )
 BEGIN
  SET @SQL = 'DROP TABLE &New&.dbo.' + @Del
  EXEC SP_ExecuteSQL @SQL
  IF( @@ERROR <> 0 )
  BEGIN
   RAISERROR( '删除已存在的表%s失败！', 11, 1, @Del )
   RETURN
  END

  FETCH NEXT FROM @DelCursor INTO @Del
 END

 -- 取出SP、FN、TF其name、id
 DECLARE @Tbl CURSOR
 SET  @Tbl = CURSOR LOCAL SCROLL FOR
 SELECT [name], [id]
 FROM &Old&.dbo.sysobjects
  -- 要增加刷新类型请修改这里
 WHERE (type LIKE 'P' OR type LIKE 'FN' OR type LIKE 'IF' OR type LIKE 'TF' )
  AND ([name] LIKE 'P%' OR [name] LIKE 'F%' OR [name] LIKE 'IF%' OR [name] LIKE 'TF%')

 DECLARE @TblName nvarchar(100)
 DECLARE @TblID  int

 -- 以@TblName为名创建表
 OPEN @Tbl
 FETCH NEXT FROM @Tbl INTO @TblName, @TblID
 WHILE( @@FETCH_STATUS = 0 )
 BEGIN
  -- 如果没有参数则跳过
  IF( (SELECT Count(*) FROM &Old&.dbo.syscolumns WHERE [name] LIKE '@%' AND [id] = @TblID) = 0 )
  BEGIN
   FETCH NEXT FROM @Tbl INTO @TblName, @TblID
   CONTINUE
  END

  DECLARE @Count int

  SELECT @Count = Count(*)
  FROM &Old&.dbo.syscolumns
  WHERE [name] LIKE '@%'
   AND [id] = @TblID
   AND [isoutparam] = 0

  -- 存在输入参数
  IF ( @Count > 0 )
  BEGIN
   -- 取出列名及其类型
   DECLARE @Col CURSOR
   SET  @Col = CURSOR LOCAL SCROLL FOR
   SELECT &Old&.dbo.syscolumns.[name], &Old&.dbo.systypes.[name]
   FROM &Old&.dbo.syscolumns LEFT JOIN
     &Old&.dbo.systypes ON &Old&.dbo.syscolumns.xtype = &Old&.dbo.systypes.xtype
   WHERE &Old&.dbo.syscolumns.[name] LIKE '@%'
    AND &Old&.dbo.syscolumns.[id] = @TblID
    AND isoutparam = 0
   ORDER BY &Old&.dbo.syscolumns.colorder
 
   DECLARE @ColName nvarchar(50)
   DECLARE @ColType nvarchar(20)
 
   -- 构造SQL语句
   SET @SQL = 'CREATE TABLE &New&.dbo.' + @TblName + '('
   OPEN @Col
   FETCH NEXT FROM @Col INTO @ColName, @ColType
   DECLARE @ColNameLast nvarchar(50)
   SET  @ColNameLast = ''
   WHILE( @@FETCH_STATUS = 0 )
   BEGIN
    SET @ColName = SubString( @ColName, 2, Len( @ColName )-1 )
    -- 跳过重复的列
    IF( @ColName NOT LIKE @ColNameLast )
    BEGIN
     SET @SQL = @SQL + '[' + @ColName + '] ' + @ColType + ','
     SET @ColNameLast = @ColName
    END
    FETCH NEXT FROM @Col INTO @ColName, @ColType
   END
   SET @SQL = SubString( @SQL, 1, Len( @SQL )-1 )
   SET @SQL = @SQL + ')'
 
   -- 执行SQL语句
   EXEC SP_ExecuteSQL @SQL
   IF( @@ERROR <> 0 )
   BEGIN
    RAISERROR( '创建表%s失败！', 11, 1, @TblName )
    RETURN
   END
  END

  SELECT @Count = Count(*)
  FROM &Old&.dbo.syscolumns
  WHERE [name] LIKE '@%'
   AND [id] = @TblID
   AND [isoutparam] <> 0

  -- 存在输出参数
  IF( @Count > 0 )
  BEGIN
   -- 取出列名及其类型
   DECLARE @Col_Out CURSOR
   SET  @Col_Out = CURSOR LOCAL SCROLL FOR
   SELECT &Old&.dbo.syscolumns.[name], &Old&.dbo.systypes.[name]
   FROM &Old&.dbo.syscolumns LEFT JOIN
     &Old&.dbo.systypes ON &Old&.dbo.syscolumns.xtype = &Old&.dbo.systypes.xtype
   WHERE &Old&.dbo.syscolumns.[name] LIKE '@%'
    AND &Old&.dbo.syscolumns.[id] = @TblID
    AND isoutparam <> 0
   ORDER BY &Old&.dbo.syscolumns.colorder
 
   DECLARE @ColName_Out nvarchar(50)
   DECLARE @ColType_Out nvarchar(20)
 
   -- 构造SQL语句
   SET @TblName = @TblName + '_Out'
   SET @SQL = 'CREATE TABLE &New&.dbo.' + @TblName + '('
   OPEN @Col_Out
   FETCH NEXT FROM @Col_Out INTO @ColName, @ColType
   SET  @ColNameLast = ''
   WHILE( @@FETCH_STATUS = 0 )
   BEGIN
    SET @ColName = SubString( @ColName, 2, Len( @ColName )-1 )
    -- 跳过重复的列
    IF( @ColName NOT LIKE @ColNameLast )
    BEGIN
     SET @SQL = @SQL + '[' + @ColName + '] ' + @ColType + ','
     SET @ColNameLast = @ColName
    END
    FETCH NEXT FROM @Col_Out INTO @ColName, @ColType
   END
   SET @SQL = SubString( @SQL, 1, Len( @SQL )-1 )
   SET @SQL = @SQL + ')'
 
   -- 执行SQL语句
   EXEC SP_ExecuteSQL @SQL
   IF( @@ERROR <> 0 )
   BEGIN
    RAISERROR( '创建表%s失败！', 11, 1, @TblName )
    RETURN
   END
  END

  -- 创建下一个表
  FETCH NEXT FROM @Tbl INTO @TblName, @TblID
 END

RETURN @@ERROR