Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Edit multiple stored procedures Expand / Collapse
Author
Message
Posted Wednesday, July 24, 2013 10:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Eugene Elutin (7/24/2013)
NineIron (7/24/2013)
Thanx. I don't think we're being served very well....................


So, if you have no choice, but to rename database (as your client/employer requires you to do), you must go inside of every stored proc, function and view and replace reference to database name to the new one.
The above is quite clear reason why the practice of naming databases to differentiate environment is road to hell...

It is your function to educate the client/employer as to WHY this methodology is bad, it is also important to recognize that in such configurations accidents are MORE likely to happen and they are going to have to live with that risk. They can set policy but that doesn't mean you can't challenge that policy or that the policy isn't wrong. Paranoia is a good trait for a DBA..

You likely need to look at the sprocs, functions, and views in ONE database to see if they use 3 part names when not needed or USE in dynamic code, if they don't then you don't need to modify anything. If you detect those then you only need to modify THAT object in all the copies. DO NOT continue the mistake of referencing the database when not needed.

Clear?

CEWII
Post #1477163
Posted Wednesday, July 24, 2013 10:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:55 AM
Points: 2,836, Visits: 5,062

...
While I generally agree lets not go off on a rant.. Perhaps I read that wrong, but we don't need to be testy.

As a rule you should never use a 3 part name when a 2 part name will do, in other words DELETE FROM SomeDB.dbo.SomeTable should not be used if the code being run is IN SomeDB, in that case DELETE FROM dbo.SomeTable is the right method.
...


1. It's not a rant, but the expression of a strong feeling about the practive to name databases per enviornment.
2. I agree, there is no need in 3-part name when you want your object to always refer to "this" database. However, quite often, large/enterprise systems use multiple databases (first example which comes to mind would be Arhive/History db's). So, you very likely to have procedures and views which require cross-database access. That's where use of 3-part name justified.



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1477164
Posted Wednesday, July 24, 2013 10:56 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Eugene Elutin (7/24/2013)

...
While I generally agree lets not go off on a rant.. Perhaps I read that wrong, but we don't need to be testy.

As a rule you should never use a 3 part name when a 2 part name will do, in other words DELETE FROM SomeDB.dbo.SomeTable should not be used if the code being run is IN SomeDB, in that case DELETE FROM dbo.SomeTable is the right method.
...


1. It's not a rant, but the expression of a strong feeling about the practive to name databases per enviornment.
2. I agree, there is no need in 3-part name when you want your object to always refer to "this" database. However, quite often, large/enterprise systems use multiple databases (first example which comes to mind would be Arhive/History db's). So, you very likely to have procedures and views which require cross-database access. That's where use of 3-part name justified.


1. I did qualify with "Perhaps I read that wrong".. As DBAs our first duty is to data protection so I understand the direction you are going.
2. I have an article in the works that discusses NEVER using 3 part names in code and using synonyms to point to objects external to the local database. But I do see your point.

CEWII
Post #1477167
Posted Wednesday, July 24, 2013 11:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:59 AM
Points: 119, Visits: 184
So, in my case, I have no choice but to call the vendor a dope and move on. I think I'm going to call the vendor and get a quote to have him do the work.
Post #1477182
Posted Wednesday, July 24, 2013 11:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
NineIron (7/24/2013)
So, in my case, I have no choice but to call the vendor a dope and move on. I think I'm going to call the vendor and get a quote to have him do the work.
I don't see it that way, you need to do a little research to see how things are. As for changes you may have to engage the vendor. But as for having prod and non-prod on the same box I think you need to have a conversation with your management on the pitfalls of that idea.

CEWII
Post #1477184
Posted Wednesday, July 24, 2013 11:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:59 AM
Points: 119, Visits: 184
Thanx for the advice. How much work is it to set up a new server? We use VM here.
Post #1477186
Posted Wednesday, July 24, 2013 11:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
Build a normal windows machine, provision disks to it, install SQL, copy logins and server permissions for the required logins, copy over the databases. I'm sure there is a bit more but this is something you should already know how to do.. The setup of the base machine is often handled by a different group but DBAs typically handle all the database portions.

CEWII
Post #1477188
Posted Wednesday, July 24, 2013 11:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:59 AM
Points: 119, Visits: 184
Thanx.
Post #1477190
Posted Wednesday, July 24, 2013 1:14 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 555, Visits: 2,580
NineIron (7/24/2013)
I need to create 5 test databases by copying the live db's. There are many stored procedures in each db. Each sp is told which db to use, Use [NEHEN_prod]. I've been told that I have to go into each sp, about 50 per db, and change the NEHEN_prod to NEHEN_test. Is this necessary? If so, is there an easy way of doing it?


Below is a script I just put together that will do through each stored procedure in a DB and perform a find/replace (replace @oldvalue with @newvalue). It works by (1) deleting the proc then recreating it with the old value (@oldvalue) replaced by the new value (@newvalue). It works as-is but I highly recommend you add some error handling...

The proc:
CREATE PROC dbo.proc_find_replace
( @oldValue varchar(100),
@newValue varchar(100))
AS
BEGIN
SET NOCOUNT ON;

-- global temp table for storing proc names and DDL definition
IF OBJECT_ID('tempdb..##sprocDef') IS NOT NULL
DROP TABLE ##sprocDef;
CREATE TABLE ##sprocDef (sproc varchar(100), ROUTINE_DEFINITION varchar(max));

DECLARE @sproc varchar(100),
@routine varchar(max),
@execSQL varchar(max),
@n int=1,
@m int=
( SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE');

-- FOR EACH proc in {yourdb}.INFORMATION_SCHEMA.ROUTINES:
-- (1) drop it
-- (2) recreate it replacing @oldValue with @newValue

WHILE @n<=@m
BEGIN
--colect the name and DDL for each proc in ##sprocDef
DECLARE @sprocDef varchar(1000)=
'INSERT INTO ##sprocDef'+CHAR(13)+
'SELECT TOP 1
SPECIFIC_SCHEMA+''.''+SPECIFIC_NAME AS Sproc,
OBJECT_DEFINITION (OBJECT_ID(SPECIFIC_SCHEMA+''.''+SPECIFIC_NAME)) AS ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND SPECIFIC_SCHEMA+''.''+SPECIFIC_NAME NOT IN (SELECT sproc FROM ##sprocDef)';
EXEC(@sprocDef);

SELECT @sproc=sproc,
@routine=ROUTINE_DEFINITION
FROM ##sprocDef;

SET @execSQL=
'IF OBJECT_ID(''ajbTest.'+@sproc+''') IS NOT NULL DROP PROC '+@sproc+';'
PRINT 'Executing:'+@execSQL
EXEC(@execSQL);

SET @execSQL=''+@routine+CHAR(13);
SET @execSQL=REPLACE(@execSQL,@oldValue,@newValue);

PRINT 'Executing:'+@execSQL
EXEC(@execSQL);

SET @n=@n+1;
END;

PRINT REPLICATE('-',80)+CHAR(13)+'ALL DONE :)'

DROP TABLE ##sprocDef;
END
GO

Example:
EXEC dbo.proc_find_replace @oldValue='NEHEN_prod', @newValue='NEHEN_test';

Lastly: backup your procs before running this script!


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1477232
Posted Wednesday, July 24, 2013 1:28 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 7:59 AM
Points: 119, Visits: 184
Thanx. Will do.
Post #1477240
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse