1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| ALTER PROCEDURE [dbo].[sp_Script_StoredProc]
-- Add the parameters for the stored procedure here
@Database NVARCHAR(50),
@FileName nvarchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Query VARCHAR(MAX)
DELETE FROM tmpScriptSp
SELECT * FROM tmpscriptsp
set @Query = 'insert into tmpScriptSp(sp_name, SaveSP, BD)
SELECT SPECIFIC_NAME AS Nom_Procedure, ROUTINE_DEFINITION AS contenu_Procedure, SPECIFIC_CATALOG AS Data_Base FROM ' + @Database + '.INFORMATION_SCHEMA.ROUTINES PROCS ORDER BY PROCS.SPECIFIC_NAME'
Print(@Query)
Execute( @Query)
DECLARE @szBCP VARCHAR(256);
SET @szBCP = 'bcp "SELECT savesp FROM ceratecp21.dbo.tmpScriptsp" queryout "C:\Scripts_SQL\' + @Filename + '" -T -c'
EXEC master.dbo.xp_cmdshell @szBCP;
END |
Partager