We all know that system stored procedures are not exported by tools (you can try > tasks > Generate SQL Scripts)
Because the system stored procedures are generally not allowed to be modified by the developer.
Need knowledge:
1, the use of xp_cmdshell command
2, the sp_ms_marksystemobject marking system stored Procedure method
3, DOS command, such as type,>>, etc.
4. Use of bcp command
Copy Code code as follows:
Use master
Go
If object_id (' Pr_proctosql ') is not null drop proc Pr_proctosql
Go
Create proc Pr_proctosql
(
@ server name varchar (100)
, @ user name varchar (100)
, @ Password varchar (100)
, @path varchar (200)
, @database varchar (200)
, @sysproc int= ' 0 '--is marked as system function 1: Yes, 0: No
, @proc_name varchar (100) = '--default is all, can blur the search
, @savetype varchar = '. SQL '--Save as SQL script by default
)
As
/*
Version: V1
Author: dharma
Date: 2012-04-13
Function:
1\ batch makefile of system stored procedures in master library (system stored procedures cannot be automatically exported)
2\ can export all types of stored procedures
3\ can mark up system stored procedures
Call:
exec pr_proctosql '. ', ' sa ', ' h4ymh@ $RTd ', ' e:\tom\master\ ', ' Master ', ' 1 ', '
exec pr_proctosql '. ', ' sa ', ' a123456 ', ' e:\sql\ ', ' Agt_trad ', ', ' pr_ ', '. sql '
*/
SET NOCOUNT ON
Declare @sp nvarchar, @s nvarchar (watts), @row int, @id int, @s_add varchar (2000)
Set @s= ' use ' + @database
EXEC (@s)
If object_id (' tempdb.. #t ') is not null drop table tempdb. #t
CREATE TABLE tempdb ... #t (name varchar (2000)
, id int IDENTITY (1,1) NOT NULL
)
EXEC ('
Insert INTO tempdb ... #t (name)
Select Name
--into TEMPDB. #T
From ' + @database + '. sysobjects where xtype= ' ' p ' and name like ' + @proc_name + '% '
')
Select @row =count (*) from tempdb. #t
print ' generates [' +cast (@row as varchar) + '] stored procedures '
Set @id =1
While @row >= @id
Begin
Select top 1 @sp =name from tempdb. #T where id= @id
If object_id (' tempdb.. Test ' is not null drop table tempdb ... Test
--Add Use master go
Set @s_add = ' echo use [' + @database + ']>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo go>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N ' "[dbo].[') + @sp + '] ') and type in (n ' ' ' P ', n ' ', ' PC ')) >> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo DROP PROCEDURE [dbo]. [' + @sp + ']>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo go>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo Set ansi_nulls on>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo go>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo SET QUOTED_IDENTIFIER on>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo go>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Select @s= '
Select text into tempdb ... Test
From ' + @database + '. syscomments
where id=object_id (' + @database + ' ... ') + @sp + ' ")
'
EXEC (@s)
--select * from tempdb. Test
Select @s= ' exec xp_cmdshell ' + ' bcp tempdb ... Test out ' + @path + @sp +cast (@id as varchar) + @savetype + '-c-s ' +@ server name + '-U ' +@ username + '-P ' +@ password + ' '
EXEC (@s)
--Append previous use master information to the front
Set @s_add = ' type ' + @path + @sp +cast (@id as varchar) + @savetype + ' >> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo go>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
If @sysproc = ' 1 '
Begin
--At the back with the mark as system stored procedure
Set @s_add = ' echo exec sp_ms_marksystemobject ' [' + @sp + '] ' >> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
Set @s_add = ' echo go>> ' + @path + @sp + @savetype
EXEC xp_cmdshell @s_add
print ' tag [' +cast (@id as varchar) + '] is a system stored procedure: ' + @sp
End
Set @s_add = ' del ' + @path + @sp +cast (@id as varchar) + @savetype
EXEC xp_cmdshell @s_add
print ' generates a [' +cast (@id as varchar) + '] stored procedure: ' + @sp
Delete from tempdb. #T where id= @id
Set @id = @id +1
End
This stored procedure can be a perfect feature
1. Generate View
2. Generating function
3, build the table structure of the specified library
4. Generate constraints on the specified library for batch build upgrade scripts
5. Scripts for building upgrades in the database
You are welcome to help me think, is there any other way? Hope to add QQ282329611 Exchange.
Build the results as shown in figure:
