Naming Shared Objects within a database

[Ps119]

We have a small shop of programmers and are just getting into using web frontends and database Administrative frontends for our databases. We've run into the problem of naming views and stored procedures so we know who wrote them and where they're being used. If one person needs to modify a view or stored procedure we need to know the impact of the changes being made and can communicate these changes as necessary.

Currently we use vw as tags for views and sp as tags for stored procedures. We were using vwAcc as tags for views for Access frontends and vwWeb as tags for views for web frontends. However, that doesn't always give us the necessary information anymore.

Does anyone have any suggestions on how to better do this? We were thinking maybe comments within the stored procedure or properties within a view????

It will be nice to know how the rest of you handle this problem.

[antares686]

First a comment it is best not to use sp as the prefix in Stored Procs. If someone uses sp_ the SP will take a performance hit. Most folks adopt either usp_ or ip_ to identify them.

Then I would make comments in SPs, Views, Triggers, UDFs, ect.

But the best way to handle things is to not let more than one or two people make changes to the production server and setup a test and if possible a stagging server for everyone else to make changes. Then you make them responsible for documenting the details for the change to the DBA(s) (fewer better) and the DBA responsible for filing these changes.

I always find that even with a good group that someone is bound to make changes that relate to them and take away from the others needs and they spend time trying to figure it out. If a DBA is in the way there is a better trail.