SQL query question

[guest2013-1]

OOH! Naming conventions... Has there even been a standard set for naming database entities? I usually name them as follows:

Tables: TableName
Stored Procedures: sp_Entity_Action (i.e. sp_Employee_Create)
Views: sp_Entity_Details (i.e. sp_Employee_ContactDetails)
Functions: fx_Entity_Function (i.e. fx_Employee_CalculateWorkingDays)

You might want to change from sp_ to proc_ or something different. sp_ gives indication to the database server that it's a system stored procedure, so it looks it up in the master database first and then when it can't find it queries the local one.

Saves you a few queries and CPU cycles

 

[dequadin]

OOH! Naming conventions... Has there even been a standard set for naming database entities? I usually name them as follows:

Tables: TableName
Stored Procedures: sp_Entity_Action (i.e. sp_Employee_Create)
Views: sp_Entity_Details (i.e. sp_Employee_ContactDetails)
Functions: fx_Entity_Function (i.e. fx_Employee_CalculateWorkingDays)

/me Fines FarligOpptreden one intenet for poor use of Hungarian notation

 

[FarligOpptreden]

Ok ok ok... I saw the small typo. Views should read vw_Entity_Details...

Happy?

*grunts*

EDIT: My original post already read vw_Entity_Details! Only the example stated sp_Employee_ContactDetails... Necuno changed my post and then everyone just went with it!

 

[Necuno]

Ok ok ok... I saw the small typo. Views should read vw_Entity_Details...

Happy?

*grunts*

EDIT: My original post already read vw_Entity_Details! Only the example stated sp_Employee_ContactDetails... Necuno changed my post and then everyone just went with it!

Last edited by FarligOpptreden; 21-06-2009 at 08:29 PM..

you should check the edit history :erm:

anyways, this is what happens when you just copy+paste

 

[FarligOpptreden]

you should check the edit history :erm:

I *did* check the edit history. I only updated the sample I gave. The original post already stated vw_Entity_Details, whilst the example said sp_blah_blah_blah... The edit is for the example. Anyway, it doesn't matter anymore and I didn't copy-paste.

 

[Necuno]

wow taking a while renaming my procs for current project, but it will be better this way going by usp_<entity>_<action>

 

[guest2013-1]

You could always automate it

 

[FarligOpptreden]

You could always automate it

It probably all depends on the original naming conventions followed... How will you automate it to rename something like "ICanLike2ChangeDemDerWorkingPersonProc" to "sp_Employee_Update"?

 

[Necuno]

It probably all depends on the original naming conventions followed... How will you automate it to rename something like "ICanLike2ChangeDemDerWorkingPersonProc" to "sp_Employee_Update"?

with azidrazor's super duper script, the one script:

One Script to rule them all, One Script to find them, One Script to bring them all and in the darkness Rename them..

 

[guest2013-1]

with azidrazor's super duper script, the one script:

One Script to rule them all, One Script to find them, One Script to bring them all and in the darkness Rename them..

lol, I actually wrote a stored procedure that wraps on other stored procedures or normal select statements, either on the local server or linked servers (by creating the link dynamically) which gives each and every stored procedure output (or select statement output) the ability to be searched on, paged and limited in number of rows outputted.

That way you don't need silly little paging code to do the heavy lifting for you and makes a 4000-10000+ recordset down to 50 in terms of transmission between server and client...

Anyway, dynamic names without naming convention

Ever head of the Left command?

Left(namestoredinsystemdatabase),3) = "sp_" -> rename the sucka

You could also use charindex to determine the _ if that is how it's seperated between the two, or ultimately you could write the script to check for both and if none is found just add "usp_" or "proc_" infront of the rename....

Remember kids, SQL Enterprise Manager is just a WYSIWYG for SQL Server, *everything* can be done via 'n SQL statement (and is how they operate in the background)

 

[FarligOpptreden]

lol, I actually wrote a stored procedure that wraps on other stored procedures or normal select statements, either on the local server or linked servers (by creating the link dynamically) which gives each and every stored procedure output (or select statement output) the ability to be searched on, paged and limited in number of rows outputted.

That way you don't need silly little paging code to do the heavy lifting for you and makes a 4000-10000+ recordset down to 50 in terms of transmission between server and client...

Anyway, dynamic names without naming convention

Ever head of the Left command?

Left(namestoredinsystemdatabase),3) = "sp_" -> rename the sucka

You could also use charindex to determine the _ if that is how it's seperated between the two, or ultimately you could write the script to check for both and if none is found just add "usp_" or "proc_" infront of the rename....

Remember kids, SQL Enterprise Manager is just a WYSIWYG for SQL Server, *everything* can be done via 'n SQL statement (and is how they operate in the background)

*tsk* Enterprise Manager is so 2000... Management Studio is where it's at!

(...Query Analyzer FTW! )

 

[guest2013-1]

*tsk* Enterprise Manager is so 2000... Management Studio is where it's at!

(...Query Analyzer FTW! )

That too, just couldn't remember the new name