IDC资讯中心
———————————————————————————-
— 刷新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
