1

I have recently changed some column data types to make the database more uniform. However I have many corresponding local variables @x for column x that was changed in many stored procedures that now do not agree, and have a length or data type mismatch with the column.

I need to make them all uniformly the same as what is in the table. Is there a way of writing a program or some looping alter statement, or a way in SQL Server to do this via the GUI? Otherwise I have to go through by hand and change 250+ stored procedures.

I have to do this for many columns and their respective local variables in the stored procedures and it amount to huge amounts of laborious work that I would rather not do.

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Nathan Fitzpatrick
  • 11
  • 2
  • maybe useful: [How do I programmatically retrieve SQL Server stored procedure source](https://stackoverflow.com/questions/467482/how-do-i-programmatically-retrieve-sql-server-stored-procedure-source-that-is-id). Search stored procedure text: [earch-text-in-stored-procedure-in-sql-server](https://stackoverflow.com/questions/14704105/search-text-in-stored-procedure-in-sql-server) – Ryan Vincent Feb 17 '15 at 17:09

1 Answers1

0

This answer may help. I have not tested it, however it appears to allow you to search for specific text (such as a variable declaration) and update it with new text.

It would seem you should be able to replace say datetime with varchar(255) if necessary.

Community
  • 1
  • 1
Phoenix
  • 1,881
  • 5
  • 20
  • 28