Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Background: I develop a system that has almost 2000 stored procedures.</p> <p>The critical thing I have found is to treat the database as an application. You would never open an EXE with a hex editor directly and edit it. The same with a database; just because you can edit the stored procedures from the database does not mean you should.</p> <p>Treat the copy of the stored procedure in source control as <em>the</em> current version. It is your source code. Check it out, edit it, test it, install it, and check it back in. The next time it has to be changed, follow the same procedure. Just as an application requires a build and deploy process, so should the stored procedures.</p> <p>The code below is a good stored procedure template for this process. It handles both cases of an update (<code>ALTER</code>) or new install (<code>CREATE</code>). </p> <pre><code>IF EXISTS(SELECT name FROM sysobjects WHERE name = 'MyProc' AND type = 'P' AND uid = '1') DROP PROCEDURE dbo.MyProc GO CREATE PROCEDURE dbo.MyProc AS GO </code></pre> <p>However following sample is better in situations where you control access to the stored procedures. The <code>DROP</code>-<code>CREATE</code> method loses <code>GRANT</code> information.</p> <pre><code>IF NOT EXISTS(SELECT name FROM sysobjects WHERE name = 'MyProc' AND type = 'P' AND uid = '1') CREATE PROCEDURE dbo.MyProc AS PRINT 'No Op' GO ALTER PROCEDURE dbo.MyProc AS GO </code></pre> <p>In addition, creating a process to build the database completely from source control can help in keeping things controlled.</p> <p>Create a new database from source control. Use a tool like Red Gate SQL Compare to compare the two databases and identify differences. Reconcile the differences.</p> <p>A cheaper solution is to simply use the "Script As" functionality of SQL Management Studio and do a text compare. However, this method is real sensitive to the exact method SSMS uses to format the extracted SQL.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to add Stored Procedures to Version Control
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USbluish
    1. USDarryl Peterson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to add Stored Procedures to Version 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. COInteresting, I haven't encountered Red Gate before. I'll give it a look see. It's hard trying to keep everyone in check though
      singulars
      1. PO
      1. USJonn
    2. CO@Darryl - SQL Server won't like the second sample because "CREATE PROCEDURE" needs to be the first statement in the batch.
      singulars
      1. PO
      1. USMoe Sisko
    3. COAnother cheaper option is JetBrains' PhpStorm (and possibly others from their *Storm lineup) which has a surprisingly good database compare tool included. It's also a decent editor with autocomplete for various SQL flavors.
      singulars
      1. PO
      1. USAron
 

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