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

2024-07-21 02:06:05

字体：大中小

来源：转载

供稿：网友

----------------------------------------------------------------------------------
--        刷新sp到数据库         --
--                    --
-- &old& 来源数据库名               --
-- &new& 目标数据库名               --
--                    --
-- 目标数据库中表名为来源数据库中的用户自定义sp、fn等(可按提示添加--详细提示以后加) --
--    表中的列名则为其参数            --
--                    --
-- 黄宗银                  --
-- 2005.01.19                 --
----------------------------------------------------------------------------------
alter procedure dbo.p_ref
as
declare @sql nvarchar(4000)

-- 不存在数据库则创建
if not exists (
  select [name]
  from master.dbo.sysdatabases
  where [name] = '&new&'
  )
begin
  create database &new&
end

-- 取出sp、fn、tf其name、id
declare @tbl cursor
set  @tbl = cursor local scroll for
select [name], [id]
from &old&.dbo.sysobjects
  -- 要增加刷新类型请修改这里
where ([name] like 'p%' or [name] like 'f%' or [name] like 'tf%')
  and (type = 'p' or type = 'fn' or type = 'tf' )

declare @tblname nvarchar(100)
declare @tblid  int

-- 以@tblname为名创建表
open @tbl
fetch next from @tbl into @tblname, @tblid
while( @@fetch_status = 0 )
begin
  -- 已存在该表则删除
  if exists
   (
    select [name] from &new&.dbo.sysobjects
    where [name] = @tblname
     and type = 'u'
   )
  begin
   set @sql = 'drop table ' + '&new&' + '.dbo.' + @tblname
   exec sp_executesql @sql
   if( @@error <> 0 )
   begin
    raiserror( '删除已存在的表%s失败！', 11, 1, @tblname )
    return
   end
  end

  -- 如果没有参数则跳过
  if( (select count(*) from dbo.syscolumns where [name] like '@%' and [id] = @tblid) = 0 )
  begin
   fetch next from @tbl into @tblname, @tblid
   continue
  end

  -- 取出列名及其类型
  declare @col cursor
  set  @col = cursor local scroll for
  select &old&.dbo.syscolumns.[name], &old&.dbo.systypes.[name]
  from &old&.dbo.syscolumns left outer 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
  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 <> @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

  -- 创建下一个表
  fetch next from @tbl into @tblname, @tblid
end

return @@error

上一篇：如何隐藏窗口的TITLE标题？（原创）

下一篇：在数据窗口中进行行选择操作