CREATE PROCEDURE GenerateSQLScript
AS

------ складывает скрипты

DECLARE @SPName VARCHAR(255),
@PString VARCHAR(4000),
@Ptr BINARY(16),
@Return VARCHAR(2),
@D_Day VARCHAR(5),
@D_Month VARCHAR(5),
@D_Year VARCHAR(5),
@Query VARCHAR (255)

SET @D_Day = CONVERT(VARCHAR(5),DATEPART(day,GETDATE()))
SET @D_Month = CONVERT(VARCHAR(5),DATEPART(Month,GETDATE()))
SET @D_Year = CONVERT(VARCHAR(5),DATEPART(Year,GETDATE()))
SET @D_Day = @D_Day + '00'
SET @D_Month = @D_Month + '00'

SET @D_Day = SUBSTRING(@D_Day,1,2)
SET @D_Month = SUBSTRING(@D_Month,1,2)

SET @D_Day = REVERSE(@D_Day)
SET @D_Month = REVERSE(@D_Month)

SET @Query = 'SPADAM_' + @D_Year + '_' + @D_Month + '_' + @D_Day + '.sql'

SET @Return = '' --CHAR(13)+CHAR(10)

IF OBJECT_ID('SP_Script') IS NULL
CREATE TABLE SP_Script (IDRow INT IDENTITY PRIMARY KEY CLUSTERED,
_Text VARCHAR(512))


DELETE FROM SP_Script

DECLARE Run CURSOR FOR
SELECT [NAME] FROM sysobjects WHERE type = 'p' ORDER BY [NAME]

OPEN Run
FETCH NEXT FROM Run INTO @SPName
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO SP_Script (_Text) VALUES ('SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON '+@Return )
INSERT INTO SP_Script (_Text) VALUES ('GO' + @Return )
INSERT INTO SP_Script (_Text) VALUES ('SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON '+ @Return )
INSERT INTO SP_Script (_Text) VALUES ('GO' + @Return )
INSERT INTO SP_Script (_Text) VALUES ('DROP PROCEDURE '+@SPName + @Return )
INSERT INTO SP_Script (_Text) VALUES ('GO' + @Return )

INSERT INTO SP_Script
EXEC sp_helptext @SPName

INSERT INTO SP_Script (_Text) VALUES ('GO')
FETCH NEXT FROM Run INTO @SPName
END

CLOSE Run
DEALLOCATE Run

UPDATE SP_Script SET _Text = REPLACE (_Text,CHAR(10),'')

UPDATE SP_Script SET _Text = REPLACE (_Text,CHAR(13),'')


SET @Query = 'bcp.exe "SELECT _Text FROM Valter.dbo.SP_Script ORDER BY IDRow" queryout \forix\personals\BillGates\' + @Query

SET @Query = @Query + ' -SBillGates -Usa -Pdirtynigger -n -c'

EXEC master..xp_cmdshell @Query, no_output

DROP TABLE SP_Script





вызывается из робота.