koosha


Hi

Is there any way to check a version of a procedure I mean I need to have version or comment for my procedure to indicate wicth procedure is currently is used in database




Re: Version for procedures

Arnie Rowland


I highly recommend adopting a rigorous configuration management routine, using a good source control tool. The latest (and free) version of SourceSafe is greatly improved and no longer deserving of most of its past criticism. You can check-in/check-out directly from SSMS. (I prefer using SQL Server for source control in [Team System for Database Professionals].)

If you are using SQL 2005, the table sys.procedures will provide the [Create_Date] and the [Modify_Date].

Also, the [Schema Change History] report provides the -[Modify_Date].

Programmatically, in both SQL 2000 and SQL 2005, you and use Extended properties to add a Version property to each object, tables, views, procedures, etc. Refer to Books Online about using:

  • sp_addextendedproperty
  • sp_dropextendedproperty
  • sp_updateextendedproperty
  • fn_listextendedproperty

In QA, you can then right click on the Procedure in Object Explorer, and select [Extended Properties].

I don't recall if EM has a facility to interact with Extended Properties.

With SSMS, there is a [Extended Properties] tab in the object Properties window.







Re: Version for procedures

koosha

Hi

I tried use sp_addextendedproperty but it seems it is not useful for my purpose.I need to have any procedures version number.so I don't know how can I use sp_addextendedproperty to do that and update value whene ever I need.






Re: Version for procedures

Arnie Rowland

Actually, it is quite simple.

1 You create an extended property with sp_addextended property.

2 You check the extended property using fn_listextendedproperty

3 You update the extended property using sp_updateextendedproperty

4 You again check the extended property using fn_listextendedproperty.

If that is too difficult for you, or doesn't provide you the functionality you need, then you may just be out of luck.