Add one text to all stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Add one text to all stored procedure

Author

Topic

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-31 : 08:58:12

Hi all,

Is it possible to add texts to all procedure in one go?
Actually I have 300 SP in my DB and I want to add "with encryption" to encrypt
all SP. is it possible in one go?

I may b stupid here but curious.

Thanks
Pushp

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-31 : 09:25:11

you should have first saved your stored procedure source in file. You will not be able to access it in the database once you create it "with encryption"

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-31 : 09:30:48

if you want to *encrypt* them all then you could script out the objects. Then do a find / replace on the file / files

then run the script.

HOWEVER, as Khtan says. make sure you have some un-encryted copy of them first!

Transact Charlie
Msg 3903, Level 16, State 1, Line 1736:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
http://nosqlsolution.blogspot.co.uk/

pushp82
Yak Posting Veteran

83 Posts

Posted - 2012-05-31 : 11:36:18

I could find out only one script, not sure if this gonna work for me and thanks "khtan" we should have back-up for the SP before encryption

IF OBJECT_ID('tempdb..#backup', 'U') IS NOT NULL

BEGIN

DROP TABLE #backup

END

CREATE TABLE #backup

(

id BIGINT IDENTITY(1, 1),

sptext NVARCHAR(MAX) NOT NULL,

spname NVARCHAR(100) NOT NULL,

encrypttext NVARCHAR(MAX) NULL,

encryptstatus BIT NOT NULL

DEFAULT ( 0 )

)

DECLARE @sptexttable TABLE

(

id BIGINT IDENTITY(1, 1),

sptext NVARCHAR(MAX),

spname NVARCHAR(100)

)

INSERT INTO @sptexttable ( sptext, spname )

SELECT [text], [name] FROM syscomments

JOIN sysobjects ON syscomments.id = sysobjects.id

AND sysobjects.xtype = 'p'

DECLARE @sptext NVARCHAR(MAX)

DECLARE @spname NVARCHAR(100)

DECLARE @counter INT

SET @counter = 1

WHILE @counter <= ( SELECT MAX(id) FROM @sptexttable )
BEGIN
BEGIN TRY
INSERT INTO #backup ( sptext, spname )
SELECT sptext,spname FROM @sptexttable WHERE id = @counter
END TRY

BEGIN CATCH
END CATCH

IF NOT EXISTS ( SELECT [name] FROM sysobjects WHERE [name] = 'ce_LastIndexOf' AND xtype = 'FN' )

BEGIN

EXEC

( 'CREATE FUNCTION ce_LastIndexOf

????(@strValue VARCHAR(4000),

????@strChar VARCHAR(50))

RETURNS INT
AS
BEGIN
DECLARE @index INT
SET @index = 0
WHILE CHARINDEX(@strChar, @strValue) > 0

BEGIN

????????SET @index = @index + CASE WHEN CHARINDEX(@strChar, @strValue) > 1 THEN

???????????????????(LEN(@strValue) - LEN(SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + LEN(@strChar),LEN(@strValue))))
?????????????????? ELSE 1 END

????????SET @strValue = SUBSTRING(@strValue,CHARINDEX(@strChar, @strValue) + len(@strChar),LEN(@strValue))????

END

RETURN @index

END'

)

END

DECLARE @tempproc NVARCHAR(MAX)

DECLARE @procindex INT

DECLARE @beginindex INT

DECLARE @header NVARCHAR(MAX)

DECLARE @asindex INT

DECLARE @replacetext NVARCHAR(MAX)

SET @tempproc = ( SELECT sptext FROM @sptexttable WHERE id = @counter
)

IF ( SELECT CHARINDEX('CREATE PROC', UPPER(@tempproc))) > 0

BEGIN

BEGIN TRY

SELECT @procindex = CHARINDEX('PROC', UPPER(@tempproc))

PRINT @procindex

SELECT @beginindex = CHARINDEX('BEGIN', UPPER(@tempproc))

PRINT @beginindex

SELECT @header = SUBSTRING(@tempproc, @procindex,

@beginindex - @procindex)

SELECT @asindex = ( SELECT dbo.ce_lastindexof(@header, 'AS') - 2

)

SELECT @replacetext = STUFF(@header, @asindex, 10,

CHAR(13) + 'WITH ENCRYPTION'

+ CHAR(13) + 'AS' + CHAR(13))

SET @tempproc = REPLACE(@tempproc, @header, @replacetext)

END TRY

BEGIN CATCH

END CATCH
END

UPDATE @sptexttable SET sptext = @tempproc WHERE id = @counter

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-31 : 11:51:39

you could just right click on the database in ssms -> tasks -> generate scripts.....

Then generate an alter for all the objects you want.
Save the script
Find replace on the script

Run it.

Transact Charlie
Msg 3903, Level 16, State 1, Line 1736:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
http://nosqlsolution.blogspot.co.uk/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources