July 25, 2008 at 11:36 pm
Hi,
I was having a default schema (i.e., dbo ) for all my tables in a Database, now i have a particular schema for all the tables in the database namely myschema.
The problem is the Store procedures which were written for my database, for some part of the code they have used select fld1, fld2, fld3 from dbo.tblname and for few code i have written without dbo, now i need to change all the code in my store procedures, for the schema which i have created.
Will any one please help me about this, how to go and is there any way.
with Regards
July 26, 2008 at 10:15 am
You would need to pull all your code out of source control and do a search/replace and recompile the code.
July 26, 2008 at 1:45 pm
Refer to BOL and read and understand what is in sys.comments.
You could use
SELECT id,colid, text FROM syscomments WHERE text LIKE '%word or phrase you are search for%' ORDER BY id, colid
Of course you could alter the above to increase the WHERE clause in the above so as to search for multiple different words or phrases.
You order by id and colid as a single row in syscomments will only store 4000 characters for the procedures text and then it creates an additional row or row(s) for remaining text. This procedure will not perform properly for encrypted procedures.
List the procedure names that are located, then open them and do an ALTER PROCEDURE substituting the new terminology you will be using.
DO NOT ATTEMPT TO DIRECTLY ALTER ANY ENTRY IN syscomments
July 26, 2008 at 2:13 pm
On SQL 2005, rather use sys.sql_modules. There's one row per proc/view now, rather than 1 per 4000 character chunk. Makes searching through the code a little easier.
Steve: Source Control? Isn't that wishful thinking? 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply