Change in all store procedures

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

SQL Server 2012 Forums

Transact-SQL (2012)

Change in all store procedures

Author

Topic

samir.first
Starting Member

34 Posts

Posted - 2014-01-21 : 01:38:32

I need change type Decimal (18,5) to Decimal (25,10) in all Procedure
and you know Decimal (18,5) may be:
Decimal (18,5)
Decimal (18 ,5)
Decimal (18, 5)
Decimal (18,5 )
Decimal ( 18,5)
Decimal ( 10,2)
etc
.....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-21 : 06:19:26

why not do it by generating code using catalog view
ie use a code like


SELECT 'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' Decimal(25,10) ' + CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'Decimal'
AND NUMERIC_PRECISION = 18
AND NUMERIC_SCALE = 5

the above will generate code
choose results to text option in SSMS and run the above query
copy results to new window and execute

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-21 : 11:59:38

I don't know of a good way to update that across stored procedures. Are you using a tool for managing your database projects? If you are using SQL Serer Data Tools (SSDT), it does some checking/"compiling" during the build process that might help detect problems, but I believe this is till a manual update to your code.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-22 : 07:33:02

ah..procedures.. i missed that
then best thing is to use sys.sql_procedures to search for instances with decimal(18,5)
see
http://visakhm.blogspot.com/2012/03/advantages-of-using-syssqlmodules-view.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2014-01-25 : 14:46:39

We store all our Sprocs in separate files (one Sproc per file) on our filesystem (and in a revision control system too).

In that way it is easy to use a programmer's editor with suitable regular expressions to make global changes like that.

If all your Sprocs are in the database then you could script them out (one per file) and then run an editor across them. The ones that are changed will have a newer date, and you can re-create just those.

You can also run a DIFF tool, prior to re-creating the Sprocs, to compare the Original / Modified files to make sure you are happy with the global changes that the editor has made (before committing them to the database)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-01-27 : 01:38:22

Can you also use user defined datatype so that it is easy to change in one place?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources