分享：sp_get_object_denifiction_to_file 获取存储过程函数的定义语句并生成文件-优快云博客

本文介绍了一个SQL Server存储过程，用于批量导出数据库对象（如存储过程、函数、触发器等）的定义到文件中。支持全部对象一次性导出到一个文件或每个对象单独导出到不同文件。
摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >
USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_get_object_definition_to_file]   Script Date: 05/10/2013 18:20:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('[dbo].[sp_get_object_definition_to_file]') IS NOT NULL
DROP PROC     [dbo].[sp_get_object_definition_to_file] 
GO 
--------------存储过程

CREATE  PROCEDURE [dbo].[sp_get_object_definition_to_file]
    @object NVARCHAR(128) = '' ,			--对象名
    @object_type NVARCHAR(2) = '',			--对象类型
	@file NVARCHAR(128) = 'ALL' ,			--获取文件方式，ALL一个文件全部将对象语句保存下来,SINGLE将对象分别保存到单独文件
	@file_type nvarchar(128) = '.sql',		--保存格式
    @path VARCHAR(255) = 'D:\DBGetObjectSQL'  --保存的文件夹路径
AS
/*
作者：陈恩辉-弘恩
示例：
EXEC [dbo].[sp_get_object_definition_to_file] '','','ALL','.sql','D:\DBGetObjectSQL'
EXEC [dbo].[sp_get_object_definition_to_file] '','','SINGLE','.sql','D:\DBGetObjectSQL'
EXEC [dbo].[sp_get_object_definition_to_file] '','P','ALL','.sql','D:\DBGetObjectSQL'
EXEC [dbo].[sp_get_object_definition_to_file] '','FN','SINGLE','.sql','D:\DBGetObjectSQL'

*/
IF UPPER(ISNULL(@object_type,'')) NOT IN ('P','FN','TR','TF','V','') --不符合要求返回
BEGIN 
	SELECT ' @object_type 只能是P或FN或TR或TF或V ' return_result 
	RETURN 
END
IF UPPER(@file) NOT IN('ALL','SINGLE') --不符合要求返回
BEGIN 
	SELECT ' @file 只能是ALL或SINGLE' return_result 
	RETURN 
END

EXEC xp_create_subdir @path; --创建新文件夹，文件夹存在则不创建
DECLARE @newline CHAR(2) ,
		@bcp_sql VARCHAR(1000) 

SET  @newline = CHAR(13) + CHAR(10) ;--换行

IF UPPER(@file) = 'ALL' ---将所有对象写入到一个文件中
BEGIN
	IF OBJECT_ID('tempdb.dbo.tmp_get_object_denifiction_all') IS NULL
	CREATE TABLE tempdb.dbo.tmp_get_object_denifiction_all  --创建持久临时表是为了BCP命令中调用
	( definition NVARCHAR(MAX)) 

	INSERT INTO  tempdb.dbo.tmp_get_object_denifiction_all  ( definition )
	SELECT    'USE ['+DB_NAME() + ']' + @newline 
			+ 'GO' + @newline 
			+ 'IF OBJECT_ID(''' + SCHEMA_NAME(a.schema_id) +'.' + name + ''',''' + c.type + ''') IS NOT NULL ' +@newline
			+ 'DROP ' + c.object_type +'[' + name + ']' + @newline 
			+ 'GO' + @newline
			+ 'SET ANSI_NULLS ON ' + @newline 
			+ 'GO' + @newline
			+ 'SET QUOTED_IDENTIFIER ON ' + @newline 
			+ 'GO' + @newline 
			+ '/*** object: '  + a.name + '    script_datetime: ' + CONVERT(VARCHAR,GETDATE(),120) + ' ***/'  + @newline
			+ definition + @newline 
			+ 'GO' + @newline 
			+ CASE WHEN a.is_ms_shipped = 1  THEN  ' EXEC sp_MS_marksystemobject ''[' + name + ']''' + @newline + 'GO' + @newline ELSE '' END  AS definition 
	FROM    sys.objects a
			JOIN sys.sql_modules b ON a.object_id = b.object_id
			JOIN (SELECT 'TR' AS type ,' TRIGGER ' AS object_type 
					UNION 
					SELECT 'FN' AS type ,' FUNCTION ' AS object_type 
					UNION 
					SELECT 'TF' AS type ,' FUNCTION ' AS object_type 
					UNION 
					SELECT 'V'  AS type ,' VIEW '      AS object_type 
					UNION 
					SELECT 'P'  AS type ,' PROCEDURE ' AS object_type ) c ON a.type = c.type 
	WHERE   ( a.type LIKE  ( CASE WHEN @object_type = '' THEN CAST('%' AS VARCHAR) ELSE CAST(RTRIM(@object_type) AS VARCHAR) END ) )  
		AND a.name LIKE RTRIM(@object) + '%' 
		AND b.definition IS NOT NULL ;
	--bcp tempdb.dbo.tmp_get_object_denifiction_all  out c:\currency1.txt -c -T --使用信任连接
	SET @bcp_sql = 'bcp  tempdb.dbo.tmp_get_object_denifiction_all out ' + @path + REPLACE('\' + db_name() + '.' + RTRIM(@object_type) + '.all_objects'+ @file_type ,' ','' ) +' -c -T'
	EXEC xp_cmdshell @bcp_sql;
	IF OBJECT_ID('tempdb.dbo.tmp_get_object_denifiction_all') IS NOT NULL 
	DROP TABLE tempdb.dbo.tmp_get_object_denifiction_all ;
	RETURN
END

IF UPPER(@file) = 'SINGLE' --将每个对象都单独生成一个对象
BEGIN

	IF object_id('tempdb.dbo.tmp_get_object_denifiction_single') IS NULL 
	CREATE TABLE tempdb.dbo.tmp_get_object_denifiction_single --创建持久临时表是为了BCP命令中调用
	( definition NVARCHAR(MAX))
	
	DECLARE @object_denifiction NVARCHAR(MAX) ,
		@single_type NVARCHAR(2) ,
		@SCHEMA_NAME NVARCHAR(128)
	SELECT    'USE ['+DB_NAME() + ']' + @newline 
			+ 'GO' + @newline 
			+ 'IF OBJECT_ID(''[' + SCHEMA_NAME(a.schema_id) +'].[' + name + ']'',''' + c.type + ''') IS NOT NULL ' +@newline
			+ 'DROP ' + c.object_type +'[' + name + ']' + @newline 
			+ 'GO' + @newline
			+ 'SET ANSI_NULLS ON ' + @newline 
			+ 'GO' + @newline
			+ 'SET QUOTED_IDENTIFIER ON ' + @newline 
			+ 'GO' + @newline 
			+ definition + @newline 
			+ 'GO' + @newline 
			+ CASE WHEN a.is_ms_shipped = 1 THEN  'EXEC sp_MS_marksystemobject ''[' + name + ']''' + @newline + 'GO' + @newline ELSE '' END  [DEFINITION] ,
		    a.name ,
		    a.type ,
		    SCHEMA_NAME(a.schema_id) [SCHEMA_NAME]
		INTO #single_file   --写入临时表完全是为了提高速度，已经测试通过。速度将近快了1倍。
							--原来不用时，生成文件全部完成后，无法及时返回执行完成。颇有些费解。
	FROM    sys.objects a
			JOIN sys.sql_modules b ON a.object_id = b.object_id
			JOIN (  SELECT 'TR' AS type ,' TRIGGER ' AS object_type 
					UNION 
					SELECT 'FN' AS type ,' FUNCTION ' AS object_type 
					UNION 
					SELECT 'TF' AS type ,' FUNCTION ' AS object_type 
					UNION 
					SELECT 'V'  AS type ,' VIEW '      AS object_type 
					UNION 
					SELECT 'P'  AS type ,' PROCEDURE ' AS object_type ) c ON a.type = c.type 
	WHERE   ( a.type LIKE (CASE WHEN @object_type = '' THEN CAST( '%' AS VARCHAR)  ELSE CAST( @object_type AS VARCHAR) END ))  
		AND a.name LIKE RTRIM(@object) + '%' 
		AND b.definition IS NOT NULL ;
		
	DECLARE C_get_object_definition CURSOR FAST_FORWARD FOR    --声明定义游标
	SELECT [DEFINITION],name,type,[SCHEMA_NAME] 	FROM #single_file 

	OPEN C_get_object_definition --打开游标
	FETCH NEXT FROM C_get_object_definition INTO @object_denifiction,@object ,@single_type,@SCHEMA_NAME--获取游标内容
	WHILE @@FETCH_STATUS = 0
		BEGIN 
			INSERT INTO tempdb.dbo.tmp_get_object_denifiction_single ( definition ) -- 写入本次将导出对象的定义语句
			SELECT @object_denifiction 
			SET @bcp_sql = 'bcp  tempdb.dbo.tmp_get_object_denifiction_single out ' + @path + '\' + REPLACE( db_name() + '.' + RTRIM(@single_type)+ '.' + @SCHEMA_NAME + '.' + @object + @file_type ,' ','' ) +' -c -T'
			EXEC xp_cmdshell @bcp_sql; --每个对象单独生成一个文件到指定目录
			--PRINT @bcp_sql ;
			TRUNCATE TABLE tempdb.dbo.tmp_get_object_denifiction_single ; --清空表内表
			FETCH NEXT FROM C_get_object_definition INTO @object_denifiction,@object ,@single_type,@SCHEMA_NAME --继续获取游标内容
		END 
	CLOSE C_get_object_definition;--关闭游标
	DEALLOCATE C_get_object_definition;--销毁游标
	
	DROP TABLE tempdb.dbo.tmp_get_object_denifiction_single ,#single_file;--释放借助的临时表
	RETURN
END 
GO
EXEC sp_MS_marksystemobject [sp_get_object_definition_to_file]