| Author |
Topic |
|
brandonl
Yak Posting Veteran
58 Posts |
 Posted - 2005-01-17 : 00:54:43
|
Recently, here, I read that for naming stored procedures, you should not start them off with sp_ (as that queries the master database). I can't remember who posted it (I apologize!  ) I have a database with the following conventions for stored procedures and views:sp_APPNAME_FUNCTIONv_APPNAME_FUNCTIONtables are just table names:APPNAME_TABLEFUNCTIONNAMEIs there anything wrong with putting v_ in front of the view names?I don't yet have any UDF's (but will have them in the future, probably). Any caveats in their naming?I've read quite a few docs on naming conventions, but wanted to see what you all think as well.Thanks in advance!~BrandonL |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-17 : 01:03:22
|
| You'll get a lot of disagreement over this one. Some people say it's HORRIBLE to put v_, etc before your objects. Others use a standard such as usp for user procs, v for view, t for table, udf for UDFs, etc. The main thing you need to remember is consistency. You can always pull the object type when looking at objects, so that's really more preference then anything else. Try to keep the rest of the naming consistent though. I like using SAAreaActionActionDescriptionSA = Subject AreaArea = Business Process, Table Affected, FuntionAction = Whatever it's doing (Insert, Update, Delete, Process, Concatenate, etc)ActionDescription = Further describes the actionThis helps me keep consistent. There are a million good standards documents out there though, including one posted on this website.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-01-17 : 01:31:22
|
| one quick one,don't use sp_ as prefix to your stored procedures, if you do, sql searches for system SPs before searching user SPs--------------------keeping it simple... |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-17 : 01:41:16
|
| You could abbreviate the APPNAME to a 3 letter code for brevity.rockmoose |
|
|
|
brandonl
Yak Posting Veteran
58 Posts |
Posted - 2005-01-17 : 02:30:30
|
quote:
Originally posted by jen
one quick one,don't use sp_ as prefix to your stored procedures, if you do, sql searches for system SPs before searching user SPs--------------------keeping it simple...
Probably was you that I saw post that the other day. If it was, thanks for putting my mind into looking into other naming convention problems!  ~BrandonL |
|
|
|
brandonl
Yak Posting Veteran
58 Posts |
Posted - 2005-01-17 : 02:34:20
|
quote:
Originally posted by rockmoose
You could abbreviate the APPNAME to a 3 letter code for brevity.rockmoose
True. I have one procedure named: sp_Packaging_System_PackageDetailsTechnicalInformation. PS_TechnicalInformationDetails might be a lot easier :) I looked up sp_rename, and it renames the procedure name in the EM view, but the actual code inside the procedure still has the old name. Will this create a problem, or should I just export all the SP creation code to a .sql file, find and replace, and then just rerun it? Downtime on the site will not be an issue, as I've slotted several hours because the entire site is changing formats. So, no biggie there.~BrandonL |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-17 : 03:04:36
|
| I would do:One drop script( no search and replace ).One create script, search and replace the names.Of course, a backup wouldn't hurt when you are doing this.rockmoose |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-01-17 : 03:12:28
|
if you don't have any script for user permissions,1. script the user permissions if you don't have any2. script the objects to be renamed with drop option3. reapply the user permissionsor you can1. script the objects to be renamed2. replace Create with Alter3. replace the old name with new namefinally run the scripti personally prefer alter instead of drop as this eliminates one step (reapplying permissions)  --------------------keeping it simple... |
|
|
|
MaverickUK
Yak Posting Veteran
89 Posts |
Posted - 2005-01-17 : 05:50:09
|
| The standard I've come to use for stored procedures is to prefix them with the intials of the developer who created themsppb_UsersGetAll (Created by Peter Bridger)spjd_UsersDeleteAll (Created by John Doe) |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-01-17 : 09:42:10
|
quote:
prefix them with the intials of the developer who created them
That's what source control is for. Your app shouldn't need to know.Damian |
|
|
|
groston
Yak Posting Veteran
61 Posts |
Posted - 2005-01-17 : 10:13:48
|
| Dumb question alert: How does one use source control in conjunction with SQL database objects? |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2005-01-17 : 10:17:07
|
| Save your database code in script files, check them into source control. Use the script file as the master copy, and verbally abuse people if you see them writing a proc in Enterprise Manager (I'm serious).Damian |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-01-17 : 14:18:55
|
quote:
Originally posted by MaverickUK
The standard I've come to use for stored procedures is to prefix them with the intials of the developer who created themsppb_UsersGetAll (Created by Peter Bridger)spjd_UsersDeleteAll (Created by John Doe)
That should be put in the descriptive header of the proc.along with create date and modifications.And other metadata that the coding standards dictate.rockmoose |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-01-17 : 18:17:39
|
| "v_APPNAME_FUNCTION"I'm not mad about this as we sometimes use VIEWs as replacements for tables.For example, we want to restructure a table - perhaps into two separate tables, or to significantly rename the columns.We move the old table to the new, renaming everything etc.Then we create a View with the original table name, and columns names, to "mimic" the original table - so that legacy code will continue to work.If my VIEWs were called "V_xxx" then this would fall outside that naming convention and would probably catch me out because I would expect views to be named that way Kristen |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-01-17 : 20:32:46
|
| There have been some good points made on this post. I disklike the idea of using the developer initials in the name. I have a standard header that lists the change history, including the developer/dba making the change. It also includes dates created and changed, purpose, example usage, and syntax. Kristen also has a good point. Many times views are presented to applications as the name of a former table to allow partitioning, migration of data, etc.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-18 : 04:58:19
|
| seehttp://www.mindsdoor.net/DMO/DMOScriptAllDatabases.htmlFor source control. If your environment is uncontrolled it at least give s a method of detecting and rolling back changes and can give a basis for release control.I like to prefix views as I want developers to know when they are using a view.SPs I prefix with s_ so it's easy to search throgh application code for db calls (apps only have acces to SPs).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Naming conventions NOT to use