0

We're trying to add versioning to a ms sql database. Since the database has now been deployed to over 400 branches and with the clients insistence in staggered rollouts it has become very difficult to manage which branches have which db fixes.

I've proposed we add a release_version extended property to all the stored procs in the databases and use that to help with the management. After scanning the web I've come up with the following script but can't seem to get it working right; can anybody assist in getting the script right?

SELECT 'EXEC sys.sp_addextendedproperty
       @name = N''Release_Version'', 
       @value = N''1.0.0'',
       @level0type = N''SCHEMA'', 
       @level0name = [' + ROUTINE_SCHEMA + '],
       @level1type = N''PROCEDURE'', 
       @level1name = [' + ROUTINE_NAME + '];'
 FROM information_schema.routines where ROUTINE_TYPE = 'PROCEDURE' ORDER BY ROUTINE_NAME

When I execute this I get a resultset of the correct execution statements but how do I actually execute them?

EXEC sys.sp_addextendedproperty            @name = N'Release_Version', @value = N'1.0.0',            @level0type = N'SCHEMA', @level0name = [dbo],            @level1type = N'PROCEDURE', @level1name = [sp_AlterTicketHistoryTable];
EXEC sys.sp_addextendedproperty            @name = N'Release_Version', @value = N'1.0.0',            @level0type = N'SCHEMA', @level0name = [dbo],            @level1type = N'PROCEDURE', @level1name = [sp_AlterTicketTransTable];
EXEC sys.sp_addextendedproperty            @name = N'Release_Version', @value = N'1.0.0',            @level0type = N'SCHEMA', @level0name = [dbo],            @level1type = N'PROCEDURE', @level1name = [sp_BackupAcknowledgementHistory];
...

[NOTE]: My mistake ... I was under the impression that the statements would execute automatically, instead I need to loop through and specifically execute.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Tal Even-Tov
  • 153
  • 2
  • 11
  • Writing code that writes code........Well played! Yeah, you gotta execute it :> – granadaCoder Mar 26 '13 at 13:29
  • It sounds like you know how to do this, yes? It would be super easy to do with a cursor since you can use the select statement in the cursor `FOR` logic and then just have a simple `Exec sp_executeSQL @cursorVariable` and a `FETCH NEXT FROM` in the cursor. Let us know if you need help with the cursor, but I'm guessing you don't. – Eric J. Price Mar 26 '13 at 18:17

0 Answers0