Alter Stored Procedure in SQL AZURE

Absence of object browser in SQL Azure throws many challenges. For example, how would you update a stored procedure in your SQL Azure database? The challenge is to get the source code from the cloud database.

select * from sys.objects

will only display the objects in your database and not the source code for any stored procedure or view etc.

Here is my hack to get the source code for any stored procedure.

1. SELECT * from information_schema.Routines

Will give you the list of all stored procedures. For example you want to alter the stored procedure usp_Test. Then

SELECT routine_definition from information_schema.Routines where specific_name ='usp_Test'

Now copy the contents of routine_definition – this is the source code for usp_Test. In my case this is

CREATE PROCEDURE usp_Test AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here select count(*) as TotalVisits1 from visits END

3. format this code

CREATE PROCEDURE usp_Test
AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select count(*) as TotalVisits1 from visits
END

4. change CREATE PROCEDURE to ALTER PROCEDURE

ALTER PROCEDURE usp_Test
AS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select count(*) as TotalVisits1 from visits
END

Update the source code as required and execute the TSQL

5. This will update your stored procedure in the cloud database.

I guess, best practice would be to mantain a local copy of your database and keep it in sync with your cloud database. That way we can run the code on our local copy and just run the script against the cloud database.

Oh! Well – we are just learning and I did not have a local copy of my test database. Again, this is not an elegent solution but it does work, so if you can recommend / find any better solution – please let us know…Thanks.