-------------------------------------------------- ---------------------------------- Refresh SP to Database ---- ---- & Old & Source Database Name ---- & New & Target Database Name ------ Table name in the target database Name as the user-defined SP, Fn, FN, etc. in the source database (can be added to the prompt - after adding) ---- Table The column name is its parameters -------- Huang Zong Yin -------------------------- -------------------------------------------------- -------- ALTER procedure dbo.a_refas declare @sql nvarchar (4000)
- Create if NOT EXISTS (SELECT [Name] from master.dbo.sysdatabases where [name] Like '& new & new & end
- Remove existing table 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 ('Deleting the existing table% s failed!', 11, 1, @del) Return End
Fetch next from @Delcursor @Del End
- Remove Sp, Fn, TF, Name, ID Declare @TBL CURSOR SET @TBL = CURSOR LOCAL SCROLL for SELECT [Name], [ID] from & Old & .dbo.sysObjects - To add refresh types, please modify here (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
- Create a table in @TBLNAME for the name Open @tbl fetch next from @TBLID while (@@ fetch_status = 0) begin - Skip if the parameters ((Select Count (*) from & Old & Old & Old & OLD & OL .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
- Presence input parameter IF (@count> 0) Begin - Remove the column name and its type 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) -. configured SQL statements 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) - Skip repeated column IF (@colname not limited @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 ') - Execute SQL statement exec sp_executesql @SQL if (@@ error <> 0) Begin raiserror ('creation table% s failed! ', 11, 1, @TBLNAME) RETURN End end
Select @count = count (*) from & old & .dbo.syscolumns where [name] Like '@%' and [id] = @tblid and [isoutparam] <> 0
- Presence output parameter IF (@count> 0) Begin - Remove the column name and its type DECLARE @col_out cursor set @col_out = cursor local scroll for select & = curd & .dbo.sysColumns. [Name], & .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) - configured SQL statements 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) - Skip repeated column IF (@colname not limited @colnamelast begin set @SQL = @sql '[' @colname ']' @Coltype ',' set @colnamelast = @colname end fetch next fr @COL_OUT INTO @colname, @coltype end set @SQL = Substring (@SQL, 1, LEN (@SQL) -1) set @SQL = @SQL ')' - Execute SQL Statement EXEC SP_EXECUTESQL @SQL IF ( @@ error <> 0) Begin raiserror ('creation table% s failed! ', 11, 1, @TBLNAME) RETURN END END - Create the next table fetch next from @TBL INTO @tblname, @tblid end
