Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Store Procs, Views and Tables For WHAT?

Author  Topic 

RexRacer
Starting Member

3 Posts

Posted - 2010-04-29 : 13:21:16
Hello everyone, I'm new here on SQLTEAM.com and glad to be here.

I am a DBM and I write applications in VB.net.

As I'm sure you all know writing applications become alot easy and the presentation of data alot faster when using Stored Procedures and Views from with in SQL.

Does anyone here become inundated with stored procedures and Views? Even when using good naming conventions for your stored procs and views do you forget where and why and for what you created them for? And how does one remember that after creating a view or SP for one part of your app three months ago then have the ablitiy to re - use it instead of building another view or sp that does the same thing and giving it another name?

Is there not a SQL tool out there that helps you remember why you created this view or proc and maybe search your views and procs to help determine if one is created already that you could use instead of creating another one?

Thanks inadvance to any reply.

Rex Robinson

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 02:29:30
some guidelines
1.use version control tool where you organise the objects as per projects->types,etc groupings
2. Add a comment inside objects describing project for which they are created, purpose and owner along with a brief description on logic
3. Put reusable code in a repository area created on version control. In any case you want to implement a logic check here to see if aythings already present. you may also have a Knowledge library where you put documentation on reusable ones and circulate the links across team.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

RexRacer
Starting Member

3 Posts

Posted - 2010-04-30 : 13:13:03
Which Version Control Tool do you recommend for SQL 2005?

Rex Robinson
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-04-30 : 13:38:32
We mechanically generate SProcs for CRUD (Get, Save, Delete, Find and Match [a function]) which, on an 80:20 basis, do for most of our tables particular the stupid little tables for This and That lookups that are only ever edited once in a blue moon. For the rest they get hand edited to "improve" them for the other logic that is needed.

We use SVN / Tortoise for Version Control. We don't attempt to integrate it with Visual Studio or SSMS etc (and we use it across a range of tools and filetypes).

Each Sproc, Trigger, View is help in a separate file in the filesystem (we don;t do Right-Click and Edit to change an Sproc in the database, we edit a script, save it to a file, and run that to change the DEV database - and thus we can easily repeat that on Test, Staging and Production databases)

If you are stuck for a system of knowing what your "inventory" is put an Sproc call at the top of each script file so that the relevant information is saved into a table in your database. We do this for Sproc Name + Version Number - so we can easily compare what version a client database has compared to our current DEV machine; therefore we can check what version the client has when they report an error (we might have deployed a couple of "improved" SProcs outside normal procedures), and also to check that everything was correctly deployed during an upgrade.

EXEC dbo.MySP_LogScript @strName='MySprocName', @strVersion='100430',
@strDescription='This is what it does', @strTagList='WIDGET,FOOBAR'
GO
CREATE PROCEDURE MySprocName
AS
...
GO

you can then interrogate your Script Logging table based on TAGS / Description / date / version / etc.

We also use a naming convention universally. It has three parts: Application, Module, Object. Each of these is a short 3/4 letter acronym. For example in the Accounts Application we have a Sales-Order_processing Module and that has tables for things like OrderHeader and OrderItem.

Those tables will be ACC_SOP_ORDH_OrderHeader and ACC_SOP_ORDI_OrderItem

All columns within those will be acc_sop_ordh_ID, acc_sop_ordh_Date and then where there is a join names like acc_sop_ordi_acc_sop_ordh_ID - which is the Order Header ID link in the Order Item record.

Then all SProcs related to Order Header are named ACC_SP_SOP_ORDH_XXX where "XXX" is prescribed for tasks that are common between tables - "Get", "Save" etc.

All variable names in the application use the same name as the column, as do all parameters and working variables in the Sprocs. Thus if we need to change acc_sop_ordh_ID from, say, INT to BIGINT we can do a global FIND of "acc_sop_ordh_ID" across Sprocs, Views, Application Code, Reports, TAGs in CMS, Javascript, etc. and find every related use - which will including finding FKey fields like acc_sop_ordi_acc_sop_ordh_ID

That doesn't directly help with remembering that I have written something before , but it does mean that all SProcs relating to Order Header table are all grouped alphabetically under ACC_SP_SOP_ORDH_* so they are easy to see in a diary listing.

Just my 2-pennyworth but maybe food-for-thought?
Go to Top of Page

RexRacer
Starting Member

3 Posts

Posted - 2010-04-30 : 16:06:08
Thanks Kristen for your input. Lots of good info for me to go on. After some research I've realized its just mostly personal preferences out there.

I like the Idea of placing a call at the begining of an sp to save the info in a table with the Database your working in.




Rex Robinson
Go to Top of Page
   

- Advertisement -