Generate multiple system stored procedures in the mater database *. SQL files are common and practical.

We all know that the system stored procedures cannot be exported using tools (you can try> task> Generate SQL scripts)
Because system stored procedures are generally not modified by developers.

Knowledge required:

1. Use the xp_cmdshell command
2. sp_MS_marksystemobject Method for marking system stored procedures
3. dos commands, such as type,>
4. Use of bcp commands
Copy codeThe Code is 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)
, @ Username varchar (100)
, @ Password varchar (100)
, @ Path varchar (200)
, @ Database varchar (200)
, @ Sysproc int = '0' -- whether to mark as system function 1: Yes, 0: No
, @ Proc_name varchar (100) = ''-- default value: All. Fuzzy search is supported.
, @ Savetype varchar (200) = '. SQL' -- saved as an SQL script by default
)
As
/*
Version: v1
Author: damo
Date: 2012-04-13
Function:
1 \ generate files in batches from the system stored procedures of the master database (the system stored procedures cannot be automatically exported)
2 \ all types of stored procedures can be exported
3 \ marking 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 (500), @ s nvarchar (2000), @ 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 'symbiosis into ['+ cast (@ row as varchar) +'] Stored Procedure'
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 the use master information to the beginning.
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
-- Mark as a system stored procedure at the end
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 'indicates the [' + cast (@ id as varchar) + '] system stored procedure:' + @ sp
End
Set @ s_add = 'del '+ @ path + @ sp + CAST (@ id as varchar) + @ savetype
Exec xp_cmdshell @ s_add
Print 'generate the ['+ cast (@ id as varchar) +'] stored procedure: '+ @ sp
Delete from tempdb .. # T where id = @ id
Set @ id = @ id + 1
End

Comprehensive Functions of this stored procedure
1. Generate a view
2. Generate Functions
3. Generate the table structure of the specified database
4. Generate the constraints of the specified database for batch generation of upgrade scripts
5. used to generate the upgrade script in the database
Please help me think about it. Is there any other way? I hope to add QQ282329611 for communication.

Generate results