Some things to think about in naming. I agree about using USP instead of SP, but we also require the middle part of the name to specify what tasks the stored procedure does and then end with a name that will help us identify the module or function. so if the stored procedure does only select statements for the airportsearch it would be naemd something like:
USP_SELECT_AIRPORTSEARCH
but if it did an insert or an update for the restaurant admin, it would be named:
USP_INSUPD_RESTAURANTADMIN
Any object that is created stricly as an experiment or for testing purposes will have the word test in the name somewhere. This helps me identify objects that do not need to be moved to production.
Other thoughts - I require all database access to be through stored procedures and never ad hoc queries written in the user interface code. This enables me to see exactly what data is being accessed, to help optimize the code, to set permissions on stored procedures rather than directly on tables and to know what might be affected by any database changes. This is also faster for data access. The downside is that you do have to give your developers access to the database to develop from, but I believe they should have that access anyway to the development database. As long as they do not have the same rights on production, this should not create a problem. DBAs are the only people who have the right to change the production database. As far as cahnges to the database structure, only dbas can do that altough the developers can create a test table to try to figure out what they they to do a particular task. THey just can't change or create a table or view that they are really going to use. Developers can and do create stored procedures and functions. Senior developers will be allowed to create items with little supervsion by me (although I will review their code to see what is being done in the database). Junior programmers are only allowed to create simple stored procedures (which we specifically specify to them that they can create, they don't create any procedures without me knowing in advance) and all their code is checked by mee before being released to testing. When they work on something that requires a complex stored porcedure, one of the senior people writes it and then goes over it with them to ensure they understand how it works.
