Is there a built-in way to store a version number against a stored procedure?

My application needs to maintain a stored procedure in a SQL Server database. When the application runs, it will create the sproc if it doesn't exist, and it needs to upgrade (drop and recreate) it if a newer version is available.

So, I need some way of storing a number against that stored procedure.

Obviously, I can store a row in a table that I create with procedurename, version, and that's what I'll do if there's no better way.

However, I was wondering if SQL Server had a proper mechanism for storing meta-data l开发者_开发技巧ike this. Can I set up custom objectproperties, or something?

Use extended properties.

Here is a blog post on how to update them:
http://www.apexsql.com/help/apexsqldoc/html/Step-by-Step_Guide/Extended_Properties/Managing_Extended_Properties.htm

SQL Server has no built-in way to do what you want. They provide any number of tools, functions, and features you can use to build out such a solution, but you will have to build it yourself.

Although it is possible to use extended properties to meet your requirement, I advise you to use your own recommended method of storing procedure_name and version in a table. Experience has shown in the long run you should not rely on things which can/will/should/might break in future version. In the long run it's better to have your own infrastructure than rely on some feature which might not be compatible with different RDBMSs. Maybe you need to move your application to another RDBMS some day, just maybe, and the cost is much lower if you think forward now.