How to Modify SPs automatically to include the change of tableSchema

Question

How to Modify SPs automatically to include the change of tableSchema

PavanKK

Old Hand

Points: 375
More actions
November 26, 2009 at 1:21 am

#206773

Hi All,
I am new to adminstration. can you help me with following.....
My application contains 300 tables & 1000procedures. Now am adding additional column to some tables ( say 180 ). Now i need to change the SPs which referes these tables, to accept one extra parameter and the logic of the existing sps need to be changed to include the filter condition based on the new parameter.
Is there anyway that i can automate this or do I need to do it manually by selecting the dependencies of each tale one by one.
Thanks for you help....:)

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

PavanKK (11/26/2009)
Hi All,
I am new to adminstration. can you help me with following.....
My application contains 300 tables & 1000procedures. Now am adding additional column to some tables ( say 180 ). Now i need to change the SPs which referes these tables, to accept one extra parameter and the logic of the existing sps need to be changed to include the filter condition based on the new parameter.
Is there anyway that i can automate this or do I need to do it manually by selecting the dependencies of each tale one by one.
Thanks for you help....:)

not enough detailed information. what is the new column? it's purpose? why do the sp's need to be modified? what's new that they need to use the new column to take into consideration?

if you are just adding a datetime column, are you putting a default value of getdate()?

in the title, it kinda of implied the schema(newschema vs dbo?); need some clarity, and real details before we can offer advice.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

You cannot rely on object dependency information kept by SQL Server. You must review every piece of source code you have to do what you ask.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

PavanKK Old Hand Points: 375 More actions · Answer 3

Thanks for your time & suggestions.. I already started working by examining each SP seperatly.........

🙂

raj acharya SSCrazy Points: 2805 More actions · Answer 4

hi,

i can understand what you feel when is there any change into one table you need to do changes into lot of sp for one column .......

i have one suggestion from now on the table in which you think that frequently changes made then make one sp of insertion for that table and use that sp into all other sp but this will be only useful when you don't need to change input parameter means for your table input parameter will be fixed like that.

Raj Acharya

raj acharya SSCrazy Points: 2805 More actions · Answer 5

hi,

i can understand what you feel when is there any change into one table you need to do changes into lot of sp for one column .......

i have one suggestion from now on the table in which you think that frequently changes made then make one sp of insertion for that table and use that sp into all other sp but this will be only useful when you don't need to change input parameter means for your table input parameter will be fixed like that.

Raj Acharya

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 6

In my organisation, we store all the database objects in version control, its always a best practice to use version control, sp_depends procedure will not return you name of the procedures in which your table is refferd in dynamic query. I would recommend using version control and find the table refrence manually.

sejal p gudhka SSCrazy Points: 2102 More actions · Answer 7

One simple way I think of is to make is easy.

Script out all sps together in one .sql file. Search for tablename in that .sql file where you have done that change.

Than change accordingly change the code.

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 8

if it's sql 2000 then you just need to select all the sp's and then script however its quite tricky in sql 2005, you will have to make use of sys.procedures catalog view to get the name of sp's and then pass the name to sp_helptext... if you need exact script let me knwo

PavanKK Old Hand Points: 375 More actions · Answer 9

I am halfway done in my task.. It would be helpful if you pass the script..

Thank you very much for everyone who tried to contribute..:-):-)

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 10

use this, it will give you text for all the sp's and then you can serach your table name, please ensure that your query result mode is text (press ctrl + T)

declare @STR varchar(max)

select @STR = isnull(@str,'') + ' execute sp_helptext ' + name from sys.procedures

exec(@str)

Ritesh Medhe SSCommitted Points: 1941 More actions · Answer 11

use this, it will give you text for all the sp's, you can then find name of your table and make the changes accordingly, please ensure that your query result is set to text mode (press ctrl + t)

declare @STR varchar(max)

select @STR = isnull(@str,'') + ' execute sp_helptext ' + name from sys.procedures

exec(@str)

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 12

If you want to script out the stored procedures and other objects to 1 file or multiple files you can do so using the "Generate Scripts" menu command in SSMS 2005.

Here is a site that gives a short tutorial:

http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/

Note: I believe a base install of SSMS 2005 without service packs will not have the "file per object" (script to multiple files) option. It will only allow you to script to a single file. That option seems to be added in a later service pack. I am not sure which one but if you have your SSMS updated to the latest one you won't have an issue.