Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>We choose to script everything, and that includes all stored procedures and schema changes. No wysiwyg tools, and no fancy 'sync' programs are necessary.</p> <p>Schema changes are easy, all you need to do is create and maintain a single file for that version, including all schema and data changes. This becomes your conversion script from version x to x+1. You can then run it against a production backup and integrate that into your 'daily build' to verify that it works without errors. Note it's important not to change or delete already written schema / data loading sql as you can end up breaking any sql written later.</p> <pre><code>-- change #1234 ALTER TABLE asdf ADD COLUMN MyNewID INT GO -- change #5678 ALTER TABLE asdf DROP COLUMN SomeOtherID GO </code></pre> <p>For stored procedures, we elect for a single file per sproc, and it uses the drop/create form. All stored procedures are recreated at deployment. The downside is that if a change was done outside source control, the change is lost. At the same time, that's true for any code, but your DBA'a need to be aware of this. This really stops people outside the team mucking with your stored procedures, as their changes are lost in an upgrade. </p> <p>Using Sql Server, the syntax looks like this:</p> <pre><code>if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_MyProc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [usp_MyProc] GO CREATE PROCEDURE [usp_MyProc] ( @UserID INT ) AS SET NOCOUNT ON -- stored procedure logic. SET NOCOUNT OFF GO </code></pre> <p>The only thing left to do is write a utility program that collates all the individual files and creates a new file with the entire set of updates (as a single script). Do this by first adding the schema changes then recursing the directory structure and including all the stored procedure files.</p> <p>As an upside to scripting everything, you'll become much better at reading and writing SQL. You can also make this entire process more elaborate, but this is the basic format of how to source-control all sql without any special software.</p> <p>addendum: Rick is correct that you will lose permissions on stored procedures with DROP/CREATE, so you may need to write another script will re-enable specific permissions. This permission script would be the last to run. Our experience found more issues with ALTER verses DROP/CREATE semantics. YMMV</p>
    singulars
    1. This table or related slice is empty.
    1. POStored procedures/DB schema in source control
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRobert Paulson
    1. USRobert Paulson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POStored procedures/DB schema in source control
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHmmm. I didn't intend it to be spammy. Apologies if it did. And no, I didn't disapprove of your term. I was just making you aware of an improved implementation of this technology that I believe is relevant to your answer. Your first sentence implies that these tools are almost a burden in some way, which is fine, but that was nearly two years ago! I've removed my comment as you find it objectionable.
      singulars
      1. PO
      1. USDavid Atkinson
    2. CO@David I didn't find your comment objectionable. I just didn't want to start a comments conversation not directly related to my answer.
      singulars
      1. PO
      1. USRobert Paulson
    3. COSpeaking of making this process more elaborate - we created a TFS workitem and service to support this process and added some automated testing. It's on codeplex: [link](http://tfscs.codeplex.com)
      singulars
      1. PO
      1. USgordy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload