Schema version and its use

SQL server 2005 has DDL trigger, which can be used to monitor when a database object like table, stored procedure, udf is changed. In SQL server 2000, it does not track this inofrmation and there is no way to know exactly when a database object is changed.

However, in sysobjects table there are two columns base_schema_ver and schema_ver, which are of int data type. Whenever a database object is changed, both columns value will increase. base_schema_ver is reserved and for internal use only. We can query schema_ver from sysobjects periodically and compare its value. If it’s changed, the object is modified.

You may say it doesn’t provide enough information, e.g. who and when the object is changed. No, it doesn’t, and it’s not a proper solution for source control. But it can be very usful in some scenario. I used this when I build store procedure and udf dependecies.

SQL server DO have a table to keep object dependencies. Due to late object resolving, if you create a stored procedure SP1 that calls non-exist stored procedure SP2, SQL server does not report error. Instead it gives a warning:
"Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object ‘dbo.SP2’. The stored procedure will still be created."
So the information in sysdepends table is very inaccurate and inreliable. If you have thousands of stored procedures and udfs, the warning messages are really anoying. So I have a routine to build my stored procedure and udf dependecies. The job runs daily and only checks dependencies for those SPs and udfs that their schema_ver is changed.
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s