Click here to Skip to main content
15,896,063 members
Search within:


Articles / Database Development / SQL Server / SQL Server 2008
 

Library for scripting SQL Server database objects with examples

Rate me:
Please Sign up or sign in to vote.
4.93/5 (138 votes)
8 Nov 2011CPOL13 min read 234.1K   13.3K   252  
This article is about library for scripting SQL Server database objects and examples representing how this library can be used.
 
<?xml version="1.0" encoding="utf-8"?><html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:svg="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"><head xmlns:svg="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5" /><link rel="stylesheet" href="sqlspec.css" /><title>AdventureWorks - Database overview</title><script language="JavaScript" src="resort_table.js"></script><script language="javascript">
//
// this hides the SVG if you are on IE, 
// and hides the VML if you are NOT on IE.
//
function init()
{
  var node = null;
  var isIE = navigator.userAgent.indexOf("MSIE") != -1;

  showhideIfIE('FK_SVG', isIE, true);
  showhideIfIE('DEPENDENCY_SVG', isIE, true);
  showhideIfIE('MODEL_SVG', isIE, true); 
  showhideIfIE('FK_VML', isIE, false);
  showhideIfIE('DEPENDENCY_VML', isIE, false);
  showhideIfIE('MODEL_VML', isIE, false);
}   

function showhideIfIE(elementName, isIE, hide)
{
  node = document.getElementById(elementName);
  if (node != null) 
  {
    if (hide) 
      node.style.display = isIE ? 'none' : 'block';
    else
      node.style.display = isIE ? 'block' : 'none';
  }
}
      </script></head><body onload="init()"><div id="nsbanner" xmlns:svg="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"><div id="bannerrow1"><table class="bannerparthead" cellspacing="0" id="Table1"><tr id="hdr"><td class="runninghead">Database reference - AdventureWorks</td><td class="product" /></tr></table></div><div id="TitleRow"><h1 class="dtH1">Database overview</h1></div></div><div id="nstext"><h2 class="dtH2">Documentation</h2><p><span class="desc"><span>AdventureWorks Sample OLTP Database 
  </span></span></p><p>Documentation generated on: Feb 25 2011 10:36PM</p><h2 class="dtH2">Database objects</h2><table class="dtTABLE"><thead><tr valign="top"><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">type</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)" sortType="number">count</th></tr></thead><tbody><tr valign="top"><td><a href="allTables.htm">tables</a></td><td>70</td></tr><tr valign="top"><td><a href="allViews.htm">views</a></td><td>17</td></tr><tr valign="top"><td><a href="allIndexes.htm">indexes</a></td><td>169</td></tr><tr valign="top"><td><a href="allSprocs.htm">procedures</a></td><td>9</td></tr><tr valign="top"><td><a href="allUDFs.htm">functions</a></td><td>11</td></tr><tr valign="top"><td><a href="allTableColumns.htm">all columns</a></td><td>725</td></tr><tr valign="top"><td><a href="allProcedureParameters.htm">all parameters</a></td><td>47</td></tr><tr valign="top"><td><a href="allUDTs.htm">types</a></td><td>6</td></tr><tr valign="top"><td><a href="allTriggers.htm">triggers</a></td><td>12</td></tr><tr valign="top"><td><a href="allDefaults.htm">defaults</a></td><td>147</td></tr><tr valign="top"><td><a href="allSchemas.htm">schemas</a></td><td>18</td></tr><tr valign="top"><td><a href="allAssemblies.htm">assemblies</a></td><td>1</td></tr></tbody></table><h2 class="dtH2">Database users</h2><table class="dtTABLE"><thead><tr valign="top"><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">name</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">role membership</th></tr></thead><tbody><tr valign="top"><td>dbo</td><td>db_owner</td></tr><tr valign="top"><td>guest</td><td>public</td></tr><tr valign="top"><td>INFORMATION_SCHEMA</td><td>public</td></tr><tr valign="top"><td>sys</td><td>public</td></tr></tbody></table><h2 class="dtH2">Roles</h2><table class="dtTABLE"><thead><tr valign="top"><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">name</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">role id</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">is application role</th></tr></thead><tbody><tr valign="top"><td>public</td><td>0</td><td>0</td></tr><tr valign="top"><td>db_owner</td><td>16384</td><td>0</td></tr><tr valign="top"><td>db_accessadmin</td><td>16385</td><td>0</td></tr><tr valign="top"><td>db_securityadmin</td><td>16386</td><td>0</td></tr><tr valign="top"><td>db_ddladmin</td><td>16387</td><td>0</td></tr><tr valign="top"><td>db_backupoperator</td><td>16389</td><td>0</td></tr><tr valign="top"><td>db_datareader</td><td>16390</td><td>0</td></tr><tr valign="top"><td>db_datawriter</td><td>16391</td><td>0</td></tr><tr valign="top"><td>db_denydatareader</td><td>16392</td><td>0</td></tr><tr valign="top"><td>db_denydatawriter</td><td>16393</td><td>0</td></tr></tbody></table><h2 class="dtH2">Database files</h2><table class="dtTABLE"><thead><tr valign="top"><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">file</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">file group</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)" sortType="number">size</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)" sortType="number">max size</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)" sortType="number">growth</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">usage</th></tr></thead><tbody><tr valign="top"><td>C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Data.mdf 
                      </td><td>PRIMARY 
                      </td><td>174080 KB 
                      </td><td>Unlimited 
                      </td><td>16384 KB 
                      </td><td>data only 
                      </td></tr><tr valign="top"><td>C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Log.ldf 
                      </td><td> 
                      </td><td>2048 KB 
                      </td><td>2147483648 KB 
                      </td><td>16384 KB 
                      </td><td>log only 
                      </td></tr></tbody></table><h2 class="dtH2">Database properties</h2><table class="dtTABLE"><thead><tr valign="top"><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">name</th><th style="cursor:pointer;cursor:hand;" onclick="resortColumn(this)">value</th></tr></thead><tbody><tr valign="top"><td>name</td><td>AdventureWorks</td></tr><tr valign="top"><td>server</td><td>SPRING\KATMAI</td></tr><tr valign="top"><td>size</td><td>172.00 MB 
                    </td></tr><tr valign="top"><td>created</td><td>Mar 19 2009  9:08PM</td></tr><tr valign="top"><td>status</td><td>ONLINE</td></tr><tr valign="top"><td>owner</td><td>NT AUTHORITY\SYSTEM</td></tr><tr valign="top"><td>version</td><td>Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86) 
	Sep 16 2010 20:09:22 
	Copyright (c) 1988-2008 Microsoft Corporation
	Developer Edition on Windows NT 6.0 &lt;X86&gt; (Build 6002: Service Pack 2)
</td></tr><tr valign="top"><td>unallocated</td><td>0.66 MB 
                      </td></tr><tr valign="top"><td>reserved</td><td>173408 KB 
                      </td></tr><tr valign="top"><td>data size</td><td>88752 KB 
                      </td></tr><tr valign="top"><td>index size</td><td>78568 KB 
                      </td></tr><tr valign="top"><td>unused</td><td>6088 KB 
                      </td></tr><tr valign="top"><td>compatibility level</td><td>100</td></tr><tr valign="top"><td>updateability</td><td>READ_WRITE</td></tr><tr valign="top"><td>user access</td><td>MULTI_USER</td></tr><tr valign="top"><td>recovery</td><td>SIMPLE</td></tr><tr valign="top"><td>collation</td><td>SQL_Latin1_General_CP1_CI_AS</td></tr><tr valign="top"><td>SQL sort order</td><td>52</td></tr><tr valign="top"><td>auto close</td><td>no</td></tr><tr valign="top"><td>auto shrink</td><td>no</td></tr><tr valign="top"><td>in standby</td><td>no</td></tr><tr valign="top"><td>torn page detection</td><td>no</td></tr><tr valign="top"><td>ansi null default</td><td>no</td></tr><tr valign="top"><td>ansi nulls</td><td>yes</td></tr><tr valign="top"><td>ansi padding</td><td>yes</td></tr><tr valign="top"><td>ansi warnings</td><td>yes</td></tr><tr valign="top"><td>arithmetic abort</td><td>yes</td></tr><tr valign="top"><td>auto create statistics</td><td>yes</td></tr><tr valign="top"><td>auto update statistics</td><td>yes</td></tr><tr valign="top"><td>close cursors on commit</td><td>no</td></tr><tr valign="top"><td>full text</td><td>yes</td></tr><tr valign="top"><td>local cursors default</td><td>no</td></tr><tr valign="top"><td>null concat</td><td>yes</td></tr><tr valign="top"><td>numeric round abort</td><td>no</td></tr><tr valign="top"><td>quoted identifiers</td><td>yes</td></tr><tr valign="top"><td>recursive triggers</td><td>no</td></tr><tr valign="top"><td>published</td><td>no</td></tr><tr valign="top"><td>subscribed</td><td>no</td></tr><tr valign="top"><td>sync with backup</td><td>no</td></tr></tbody></table><h2 class="dtH2">Code (database)</h2><pre class="csharpcode"><span class="kwrd">CREATE</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">ON</span>  <span class="kwrd">PRIMARY</span> 
( NAME = N<span class="str">'AdventureWorks_Data'</span>, FILENAME = N<span class="str">'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Data.mdf'</span> , <span class="kwrd">SIZE</span> = 174080KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
 LOG <span class="kwrd">ON</span> 
( NAME = N<span class="str">'AdventureWorks_Log'</span>, FILENAME = N<span class="str">'C:\Program Files\Microsoft SQL Server\MSSQL10.KATMAI\MSSQL\DATA\AdventureWorks_Log.ldf'</span> , <span class="kwrd">SIZE</span> = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
 <span class="kwrd">COLLATE</span> SQL_Latin1_General_CP1_CI_AS
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> COMPATIBILITY_LEVEL = 100
<span class="kwrd">IF</span> (1 = FULLTEXTSERVICEPROPERTY(<span class="str">'IsFullTextInstalled'</span>))
<span class="kwrd">begin</span>
<span class="kwrd">EXEC</span> [AdventureWorks].[dbo].[sp_fulltext_database] @<span class="kwrd">action</span> = <span class="str">'enable'</span>
<span class="kwrd">end</span>
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> ANSI_NULL_DEFAULT <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> ANSI_NULLS <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> ANSI_PADDING <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> ANSI_WARNINGS <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> ARITHABORT <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> AUTO_CLOSE <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> AUTO_CREATE_STATISTICS <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> AUTO_SHRINK <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> AUTO_UPDATE_STATISTICS <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> CURSOR_CLOSE_ON_COMMIT <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> CURSOR_DEFAULT  <span class="kwrd">GLOBAL</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> CONCAT_NULL_YIELDS_NULL <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> NUMERIC_ROUNDABORT <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> QUOTED_IDENTIFIER <span class="kwrd">ON</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> RECURSIVE_TRIGGERS <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span>  ENABLE_BROKER 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> AUTO_UPDATE_STATISTICS_ASYNC <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> DATE_CORRELATION_OPTIMIZATION <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> TRUSTWORTHY <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> ALLOW_SNAPSHOT_ISOLATION <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> PARAMETERIZATION SIMPLE 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> READ_COMMITTED_SNAPSHOT <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> HONOR_BROKER_PRIORITY <span class="kwrd">OFF</span> 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span>  READ_WRITE 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> RECOVERY SIMPLE 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span>  MULTI_USER 
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> PAGE_VERIFY CHECKSUM  
<span class="kwrd">ALTER</span> <span class="kwrd">DATABASE</span> [AdventureWorks] <span class="kwrd">SET</span> DB_CHAINING <span class="kwrd">OFF</span> 
</pre><h2 class="dtH2">Code (users)</h2><pre class="csharpcode"><span class="kwrd">CREATE</span> <span class="kwrd">USER</span> [dbo] <span class="kwrd">FOR</span> LOGIN [NT AUTHORITY\SYSTEM] <span class="kwrd">WITH</span> DEFAULT_SCHEMA=[dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">USER</span> [guest] <span class="kwrd">WITH</span> DEFAULT_SCHEMA=[guest]

<span class="kwrd">CREATE</span> <span class="kwrd">USER</span> [INFORMATION_SCHEMA]

<span class="kwrd">CREATE</span> <span class="kwrd">USER</span> [sys]

</pre><h2 class="dtH2">Code (roles)</h2><pre class="csharpcode"><span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_owner] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_accessadmin] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_securityadmin] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_ddladmin] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_backupoperator] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_datareader] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_datawriter] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_denydatareader] <span class="kwrd">AUTHORIZATION</span> [dbo]

<span class="kwrd">CREATE</span> <span class="kwrd">ROLE</span> [db_denydatawriter] <span class="kwrd">AUTHORIZATION</span> [dbo]

</pre></div><br xmlns:svg="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink" /><center xmlns:svg="http://www.w3.org/2000/svg" xmlns:xlink="http://www.w3.org/1999/xlink"><span style="size:20pt">Powered by <a target="_blank" href="http://www.elsasoft.org">Yoyodyne © 1971</a></span></center></body></html>

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Kanasz Robert
Architect Marwin Cassovia Soft
Slovakia Slovakia
My name is Robert Kanasz and I have been working with ASP.NET, WinForms and C# for several years.
MCSD - Web Applications
MCSE - Data Platform
MCPD - ASP.NET Developer 3.5
- Web Developer 4
MCITP - Database Administrator 2008
- Database Developer 2008
MCSA - SQL Server 2012
MCTS - .NET Framework 3.5, ASP.NET Applications
- SQL Server 2008, Database Development
- SQL Server 2008, Implementation and Maintenance
- .NET Framework 4, Data Access
- .NET Framework 4, Service Communication Applications
- .NET Framework 4, Web Applications
MS - Programming in HTML5 with JavaScript and CSS3 Specialist

Open source projects: DBScripter - Library for scripting SQL Server database objects


Please, do not forget vote

Comments and Discussions

Permalink
Advertise
Privacy
Cookies
Terms of Use
Layout: fixed | fluid

Posted 9 Oct 2011

Article Copyright 2011 by Kanasz Robert
Everything else Copyright © CodeProject, 1999-2024

Web02 2.8:2024-04-02:1