刷新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
