分享：sp_get_object_denifiction_to_file获取存储过程函数的定义语句并生成文件(二)
ction_single') IS NULL
CREATE TABLE tempdb.dbo.tmp_get_object_denifiction_single ( definition NVARCHAR(MAX))
DECLARE @object_denifiction NVARCHAR(MAX)
DECLARE C CURSOR FAST_FORWARD FOR
SELECT 'USE ['+DB_NAME() + ']' + @newline
+ 'GO' + @newline
+ 'IF OBJECT_ID(''' + name + ''',''' + @object_type + ''') IS NOT NULL ' +@newline
+ 'DROP ' + CASE WHEN @object_type = 'P' THEN ' PROCEDURE ' ELSE ' FUNCTION ' END +'[' + 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 ,
a.name
FROM sys.objects a
JOIN sys.sql_modules b ON a.object_id = b.object_id
WHERE type = @object_type AND a.name LIKE @object + '%' AND b.definition IS NOT NULL
OPEN C
FETCH NEXT FROM c INTO @object_denifiction,@object
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 + '\' + db_name() + '.' + @object_type + '.' + @object + @file_type +' -c -T'
EXEC xp_cmdshell @bcp_sql;
TRUNCATE TABLE tempdb.dbo.tmp_get_object_denifiction_single ;
FETCH NEXT FROM c INTO @object_denifiction,@object
END
CLOSE C;
DEALLOCATE C;
IF OBJECT_ID( 'tempdb.dbo.tmp_get_object_denifiction_single' ) IS NOT NULL
DROP TABLE tempdb.dbo.tmp_get_object_denifiction_single ;
END
GO
EXEC sp_MS_marksystemobject [sp_get_object_denifiction_to_file]