指定数据库批量生成 .sql文件代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】指定数据库批量生成 .sql文件
作者:弘恩 / 发布于2013/10/23/ 978
指定数据库，指定存储过程，将存储过程定义语句批量生成 .sql文件

	USE [master]
	GO
	if object_id('[dbo].[sp_get_proc_to_sql_file]','P' ) is not null
	drop proc [dbo].[sp_get_proc_to_sql_file]
	go
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE PROC [dbo].[sp_get_proc_to_sql_file]
	 (
	 @servername VARCHAR(100) ,
	 @login_user VARCHAR(100) ,
	 @password VARCHAR(100) ,
	 @path VARCHAR(200) ,
	 @proc_name VARCHAR(255) = '' , --默认是所有,可以模糊搜索
	 @savetype VARCHAR(200) = '.sql' --默认保存为sql脚本
	 )
	AS /*
	作者：弘恩
	调用:

	exec [sp_get_proc_to_sql_file]
	@servername='192.168.1.210',
	@login_user='sa_updater210',
	@password='sa_updater210',
	@path='e:getsqlfile',
	@proc_name='sp_get_proc_to_sql_file',
	@savetype='.sql'

	*/

	 SET nocount ON
	 DECLARE @sp VARCHAR(255) ,--过程名字
	 @s VARCHAR(8000) ,--执行的动态串
	 @row INT , --需要生成存储过程数量
	 @id INT ,
	 @s_add VARCHAR(8000),
	 @sysproc INT = '0' , --是否标记为系统函数 1:是,0:否，
	 @database VARCHAR(255)--数据库名

	SET @database = DB_NAME();
	 --SET @s = ' USE ' + @database;
	 --EXEC(@s)

	
	 IF OBJECT_ID('tempdb..#t') IS NOT NULL
	 DROP TABLE #t

	 SELECT CAST(o.name AS VARCHAR(255)) AS name , o.is_ms_shipped ,
	 IDENTITY( INT ,1,1 ) AS id
	 INTO #t
	 FROM sys.objects o,sys.sql_modules s
	 WHERE o.object_id = s.object_id
	 AND o.name LIKE @proc_name + '%'
	 AND s.definition IS NOT NULL --非加密的
	 AND o.type = 'p'
	
	 SELECT @row = @@rowcount

	 SET @id = 1
	 IF @row > 0
	 BEGIN
	 SET @path = @path + '' + DB_NAME() + '..' ;
	 IF OBJECT_ID('tempdb..sp_get_proc_to_sql_file_my_uptoproc') IS NULL
	 CREATE TABLE tempdb..sp_get_proc_to_sql_file_my_uptoproc ( [text] NVARCHAR(MAX) )
	
	 WHILE @row >= @id
	 BEGIN
	 SELECT @sp = name ,@sysproc = is_ms_shipped FROM #t WHERE id = @id

	 --增加USE DATABASENAME go
	 SET @s_add = 'echo USE [' + @database + ']>>' + @path + @sp + @savetype
	 --PRINT '@s_add0:' + @s_add
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add1'
	 SET @s_add = 'echo GO>>' + @path + @sp + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add2'
	 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
	 --PRINT '@s_add3'
	 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
	 --PRINT '@s_add4'
	 SET @s_add = 'echo SET ANSI_NULLS ON>>' + @path + @sp + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add5'
	 SET @s_add = 'echo GO>>' + @path + @sp + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_ad6'
	 SET @s_add = 'echo SET QUOTED_IDENTIFIER ON>>' + @path + @sp + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add7'
	 SET @s_add = 'echo GO>>' + @path + @sp + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add8'
	
	 TRUNCATE TABLE tempdb..sp_get_proc_to_sql_file_my_uptoproc

	 INSERT INTO tempdb..sp_get_proc_to_sql_file_my_uptoproc ( [text] )
	 SELECT definition AS [text] /*into sp_get_proc_to_sql_file_my_uptoproc*/
	 FROM sys.sql_modules
	 WHERE object_id = OBJECT_ID(@database + '..' + @sp)
	
	 --将存储过程定义的语句，导出到 proc_name+'1' 的文件里
	 --因为定义语句，可能非常长，而xp_cmdshell却不能定义为nvarchar(max)
	 SELECT @s = ' exec xp_cmdshell '
	 + ''' bcp tempdb..sp_get_proc_to_sql_file_my_uptoproc out ' + @path + @sp
	 + CAST(@id AS VARCHAR) + @savetype + ' -c -S '
	 + @servername + ' -U ' + @login_user + ' -P '
	 + @password + ''''
	 --PRINT '--bcp:' + CHAR(13) + CHAR(10) + @s
	 EXEC(@s)

	 --从 proc_name+'1' 的文件里 导出到 proc_name 里面
	 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
	 -- proc_name+'1' 文件进行删除
	 SET @s_add = 'del ' + @path + @sp + CAST(@id AS VARCHAR) + @savetype
	 EXEC xp_cmdshell @s_add
	 IF @sysproc = '1'
	 BEGIN
	 --在最后面加上标记为系统存储过程
	 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
	 END

	 SET @id = @id + 1
	 END
	 END

	
	GO
	if object_id('[dbo].[sp_get_db_all_proc_to_file]','P') is not null
	drop proc [dbo].[sp_get_db_all_proc_to_file]
	go
	create PROC [dbo].[sp_get_db_all_proc_to_file]
	 (
	 @servername VARCHAR(100) ,
	 @login_user VARCHAR(100) ,
	 @password VARCHAR(100) ,
	 @path VARCHAR(200) ,
	 @proc_name VARCHAR(255) = '' , --默认是所有,可以模糊搜索
	 @savetype VARCHAR(200) = '.sql' --默认保存为sql脚本
	 )
	AS /*

	调用:
	exec xp_cmdshell ' bcp tempdb..my_uptoproc out
	e:\getsqlfile\sp_get_proc_to_sql_file1.sql -c -S 192.168.1.210 -U sa_updater210 -P sa_updater210'
	exec [sp_get_db_all_proc_to_file]
	@servername='192.168.1.210',
	@login_user='sa_updater210',
	@password='sa_updater210',
	@path='e:\getsqlfile\',
	@proc_name='sp_get_',
	@savetype='.sql'

	*/

	 SET nocount ON
	 DECLARE @sp VARCHAR(255) ,--过程名字
	 @s VARCHAR(8000) ,
	 @row INT , --需要生成存储过程数量
	 @id INT ,
	 @s_add VARCHAR(8000),
	 @sysproc INT , --是否标记为系统函数 1:是,0:否，
	 @database VARCHAR(255)

	SET @database = DB_NAME();
	 IF OBJECT_ID('tempdb..#t') IS NOT NULL
	 DROP TABLE #t
	 SELECT CAST(name AS VARCHAR(255)) AS name , is_ms_shipped ,IDENTITY( int,1,1 ) AS id
	 INTO #t
	 FROM sys.objects
	 WHERE type = 'p'
	 AND name LIKE @proc_name + '%'
	
	 SELECT @row = @@rowcount

	 SET @id = 1
	 IF @row > 0
	 BEGIN
	 SET @path = @path + CASE WHEN @path NOT LIKE '%\' THEN '\' ELSE '' END + DB_NAME() + '..' ;
	 IF OBJECT_ID('tempdb..my_uptoproc') IS NOT NULL
	 DROP TABLE tempdb..my_uptoproc
	 CREATE TABLE tempdb..my_uptoproc ( [text] text )
	
	 WHILE @row >= @id
	 BEGIN
	 SELECT @sp = name ,@sysproc = is_ms_shipped FROM #t WHERE id = @id

	 --增加USE DATABASENAME go
	 SET @s_add = 'echo USE [' + @database + ']>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 --PRINT '@s_add0:' + @s_add
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add1'
	 SET @s_add = 'echo GO>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add2'
	 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 + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add3'
	 SET @s_add = 'echo DROP PROCEDURE [dbo].[' + @sp + ']>>' + @path +'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 SET @s_add = 'echo GO>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add4'
	 SET @s_add = 'echo SET ANSI_NULLS ON>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add5'
	 SET @s_add = 'echo GO>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_ad6'
	 SET @s_add = 'echo SET QUOTED_IDENTIFIER ON>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add7'
	 SET @s_add = 'echo GO>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 --PRINT '@s_add8'
	
	 TRUNCATE TABLE tempdb..my_uptoproc

	 INSERT INTO tempdb..my_uptoproc
	 ( [text] )
	 SELECT definition AS [text] /*into my_uptoproc*/
	 FROM sys.sql_modules
	 WHERE object_id = OBJECT_ID(@database + '..' + @sp)
	
	 --将存储过程定义的语句，导出到 proc_name+'1' 的文件里
	 --因为定义语句，可能非常长，而xp_cmdshell却不能定义为nvarchar(max)
	 SELECT @s = ' exec xp_cmdshell '
	 + ''' bcp tempdb..my_uptoproc out ' + @path + @sp
	 + CAST(@id AS VARCHAR) + @savetype +' -c -S '
	 + @servername + ' -U ' + @login_user + ' -P '
	 + @password + ''''
	 PRINT '--bcp:' + CHAR(13) + CHAR(10) + @s
	 EXEC(@s)
	 --WAITFOR DELAY '00:00:10'

	 --从 proc_name+'1' 的文件里 导出到 proc_name 里面
	 SET @s_add = 'type ' + @path + @sp + CAST(@id AS VARCHAR) + @savetype + '>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add

	 SET @s_add = 'echo GO>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 -- proc_name+'1' 文件进行删除
	 SET @s_add = 'del ' + @path + @sp + CAST(@id AS VARCHAR) + @savetype
	 EXEC xp_cmdshell @s_add
	 IF @sysproc = '1'
	 BEGIN
	 --在最后面加上标记为系统存储过程
	 SET @s_add = 'echo exec sp_MS_marksystemobject ''[' + @sp + ']''>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 SET @s_add = 'echo GO>>' + @path + 'all_proc'/*@sp*/ + @savetype
	 EXEC xp_cmdshell @s_add
	 END

	 SET @id = @id + 1
	 END
	 END
	GO
试试其它关键字
　数据库　
同语言下
可能有用的
弘恩贡献的其它代码(1)
.指定数据库批量生成 .sql文件