Creating a Random Password in SQL Server

The following SQL Server stored procedure can be used to generate a random string which can be used in as a password.

CREATE Procedure RandomPwd @pwdLength int = 8 ,@Password varchar(30) = null output AS SET NOCOUNT ON SET ROWCOUNT @pwdLength SET @Password = '' SELECT TOP 8 @password = @password + char(number) FROM master..spt_values WHERE type='p' AND ( number between 48 and 57 OR number between 65 AND 90 OR number between 97 AND 122 ) ORDER BY newid() SELECT @Password RETURN @@error GO

SQL Server Unused Indexes

The following SQL script can be used to identify the unused indexes that have the most impact.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- create temp table. This code can be used as a template to query the current database SELECT TOP 0 DB_NAME() AS DatabaseName ,SCHEMA_NAME(o.Schema_ID) AS SchemaName ,OBJECT_NAME(s.[object_id]) AS TableName ,i.name AS indexName ,s.user_updates ,(s.system_seeks + s.system_scans + s.system_lookups) AS system_usage INTO #TempUnusedIndexes FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id -- repeat for all databases EXEC sp_MSForEachDB 'USE [?]; INSERT INTO #TempUnusedIndexes SELECT TOP 20 DB_NAME() AS DatabaseName ,SCHEMA_NAME(o.Schema_ID) AS SchemaName ,OBJECT_NAME(s.[object_id]) AS TableName ,i.name AS IndexName ,s.user_updates ,(s.system_seeks + s.system_scans + s.system_lookups) AS system_usage FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id INNER JOIN sys.objects o ON i.object_id = O.object_id WHERE s.database_id = DB_ID() AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0 AND s.user_seeks = 0 AND s.user_scans = 0 AND s.user_lookups = 0 AND i.name IS NOT NULL ORDER BY s.user_updates DESC' -- show results SELECT TOP 20 * FROM #TempUnusedIndexes ORDER BY [user_updates] DESC DROP TABLE #TempUnusedIndexes

SQL Server 2008 Missing Indexes

The following SQL script uses the dynamic management views in SQL Server 2008 to determine the missing indexes which would have the biggest impact.

Impact is calculated in terms of the frequency of the index usage (scans + seeks) and the measure of query improvement (total user cost and average user impact).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT TOP 20 ROUND( s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0 ) AS total_cost , s.avg_user_impact , d.statement AS table_name , d.equality_columns , d.inequality_columns , d.included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY total_cost DESC

Loading Data into MySql Tables

The following ststement can be used to load data from a text file into a MySql table.

LOAD DATA INFILE 'tmp/datafile.txt' INTO TABLE schema.table FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

SQL Server Black Box Trace

A blackboc trace on SQL server captrues the last 5Mb of SQL server activity. This is useful for identifying what was running ast the time of any server crash. To start a blackbox trace

DECLARE @traceId int EXEC sp_trace_create @traceId output, 8 EXEC sp_trace_setstatus @traceId, 1

By default the trace file will be created in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA To load the trace files for inspection:

SELECT * INTO temp_trc FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\blackbox.trc', default);

CSS For Code Blocks

The code blocks on this blog are formatted using the following CSS.

.code { border:1px solid #E1E1E1; color:#333344; background:#FAFAFA; font-family:monospace; overflow:auto; font-size:11px; padding:0.5em; white-space: pre-wrap; /* css-3 */ white-space: -moz-pre-wrap !important; white-space: -pre-wrap; /* Opera 4-6 */ white-space: -o-pre-wrap; /* Opera 7 */ word-wrap: break-word; /* Internet Explorer 5.5+ */ }

SQL Server 2008 Index Usage

The following code can be used to determine index usage. It may then be possible to drop unnecessary indexes.

SELECT object_name(s.[object_id]) as [object name] ,i.[name] as [index name] ,user_seeks ,user_scans ,user_lookups ,user_updates FROM sys.dm_db_index_usage_stats as s INNER JOIN sys.indexes as i on i.[object_id] = s.[object_id] AND i.index_id = s.index_id WHERE s.database_id = db_id('dbname') AND objectproperty(s.[object_id],'IsUserTable') = 1 AND s.object_id = object_id('dbo.table_name')

SQL Server PIVOT

Example using the PIVOT command to construct a crosstab.

-- table definition CREATE TABLE [dbo].[server] ( [server_id] [int] IDENTITY(1,1) NOT NULL, [server_name] [varchar](30) NULL, [product] [varchar](30) NULL, [data_centre] [varchar](100) NULL, [business_unit] [varchar](100) NULL, CONSTRAINT [pk_server] PRIMARY KEY NONCLUSTERED ( [server_id] ASC) ) -- use pivot to display the number of servers by product for each business unit and data centre. DECLARE @products varchar(max) ,@sql varchar(max) SELECT @products = STUFF(( SELECT DISTINCT '],[' + product FROM dbo.[server] ORDER BY '],[' + product FOR XML PATH('') ), 1, 2, '') + ']' SET @sql = ' SELECT * FROM ( SELECT product, business_unit, data_centre FROM dbo.[server] ) t PIVOT (count(product) FOR product IN(' + @products + ')) as pvt ' EXEC (@sql)

Sample Results

business_unit	data_centre	IMAGE	MySQL	NAS	SNAP	SOLR	SQL SERVER
UnitA	Datacentre1	0	10	0	0	6	5
UnitB	Datacentre1	1	3	1	0	0	9
UnitA	Datacentre2	0	7	0	1	3	6
UnitB	Datacentre2	1	1	0	0	1	2
UnitA	Datacentre3	0	2	0	0	0	2
UnitB	Datacentre3	0	1	0	0	0	4

Database Mirror Recovery

If the primary database is still accessible then switch over to the secondary can be achieved by running the following code on the primary server for each of the failed databases.

ALTER DATABASE db_name SET PARTNER FAILOVER

If the primary database is not accessible then the secondary database can be forced on-line by removing the mirror and running the restore command for each of the failed databases.

ALTER DATABASE db_name SET PARTNER OFF RESTORE DATABASE db_name WITH RECOVERY

SQL Server Versions

To check the product version, level and edition.

SELECT @@Servername ,SERVERPROPERTY ('productversion') ,SERVERPROPERTY ('productlevel') ,SERVERPROPERTY ('edition')

Release	Product Version
SQL Server 2012 RTM	11.0.2100.6
SQL Server 2008 R2 SP2	10.50.4000.0
SQL Server 2008 R2 SP1	10.50.2500.0
SQL Server 2008 R2 RTM	10.50.1600.1
SQL Server 2008 Service Pack 2	10.00.4000.00
SQL Server 2008 Service Pack 1	10.00.2531.00
SQL Server 2008 RTM	10.00.1600.22
SQL Server 2005 Service Pack 4	9.00.5000.00
SQL Server 2005 Service Pack 3	9.00.4035
SQL Server 2005 Service Pack 2	9.00.3042
SQL Server 2005 Service Pack 1	9.00.2047
SQL Server 2005 RTM	9.00.1399
SQL Server 2000 Service Pack 4	8.00.2039
SQL Server 2000 ServicePack 3a	8.00.760
SQL Server 2000 Service Pack 2	8.00.534
SQL Server 2000 Service Pack 1	8.00.384
SQL Server 2000 RTM	8.00.194
SQL Server 7.0 Service Pack 4	7.00.1063
SQL Server 7.0 Service Pack 3	7.00.961
SQL Server 7.0 Service Pack 2	7.00.842
SQL Server 7.0 Service Pack 1	7.00.699
SQL Server 7.0 RTM	7.00.623
SQL Server 6.5 Service Pack 5a Update	6.50.479
SQL Server 6.5 Service Pack 5a	6.50.416
SQL Server 6.5 Service Pack 5	6.50.415
SQL Server 6.5 Service Pack 4	6.50.281
SQL Server 6.5 Service Pack 3	6.50.258
SQL Server 6.5 Service Pack 2	6.50.240
SQL Server 6.5 Service Pack 1	6.50.213
SQL Server 6.5 RTM	6.50.201

SQL 2008 List all DMV Views

SELECT
name, type, type_desc
FROM
sys.system_objects
WHERE
name LIKE 'dm_%'
ORDER BY
name

SQL2008 Listing the 10 Most Expensive Queries

SELECT TOP 10
substring(qt.text, (qs.statement_start_offset/2)+1
,((case qs.statement_end_offset
when -1 then datalength(qt.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2)+1)
,qs.execution_count
,qs.total_logical_reads
,qs.last_logical_reads
,qs.total_logical_writes
,qs.last_logical_writes
,qs.total_worker_time
,qs.last_worker_time
,qs.total_elapsed_time/1000000 total_elapsed_time_in_s
,qs.last_elapsed_time/1000000 last_elapsed_time_in_s
,qs.last_execution_time
,qp.query_plan
FROM
sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY
qs.total_logical_reads DESC

Data Management Policies

Data Management

Transferring and Exchanging Data
Permitting Emergency Data Amendment
Receiving Information on Disks
Setting up a New Folder / Directory
Amending Directory Structures
Sharing Data on Project Management Systems
Archiving Documents
Information Retention Policy
Setting up New Spreadsheets
Setting up New Databases
Linking Information between Documents and Files
Updating Draft Reports
Deleting Draft Reports
Using Version Control Systems
Updating Customer Information
Using Meaningful File Names
Managing Data Storage
Managing Databases
Using Headers and Footers
Using and Deleting ‘Temp’ Files
Using Customer and Other Third Party Data Files
Saving Data / Information by Individual Users

Changing a SQL Server 2008 Instance Name

To change the name of a SQL server instance.

EXEC sp_dropserver 'oldname'
GO
EXEC sp_addserver 'newname', 'local'
GO

Now restart the mssqlserver service.

Check the servername using

SELECT @@servername

Creating a Random Date in MySQL

The following code can be used to create a random date between 1930-01-01 and 2010-01-01.

SELECT '1930-01-01' + interval rand() * 29200 day;

Converting RTF to Plain Text Using Regular Expressions in MS SQL

RTF tags can be stripped from a text column in a MS SQL database by using regular expressions thorugh SQL CLR.

Using Visual Studio create a new library project with the following code.

using Microsoft.SqlServer.Server;
using System.Text.RegularExpressions;
using System.Data.SqlTypes;

public class RegularExpressions
{
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]

public static SqlBoolean RegExMatch(SqlString input, SqlString pattern)
{
if (input.IsNull || pattern.IsNull)
return SqlBoolean.False;

return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase);
}

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlString RegExReplace(SqlString input, SqlString pattern, SqlString replacement)
{
if (input.IsNull || pattern.IsNull || replacement.IsNull)
return SqlString.Null;

return new SqlString(Regex.Replace(input.Value, pattern.Value, replacement.Value, RegexOptions.IgnoreCase));
}

}

This example shows two functions, one for pattern matchign and one for replacement. Only the replacement function will be used in the RTF conversion.

Create the assembly and register the CLR functions

Enable CLR

sp_configure 'clr enabled', 1
RECONFIGURE WITH OVERRIDE

Create the Assembly

CREATE ASSEMBLY TextFunctions FROM 'c:\dba\tools\lib\TextFunctions.dll'

Create SQL Functions to Reference the CLR Functions

CREATE FUNCTION RegExMatch( @Input NVARCHAR(512)
,@Pattern NVARCHAR(127)
)
RETURNS BIT

EXTERNAL NAME TextFunctions.RegularExpressions.RegExMatch
GO

CREATE FUNCTION RegExReplace( @Input NVARCHAR(512)
,@Pattern NVARCHAR(127)
,@Replacement NVARCHAR(512)
)
RETURNS NVARCHAR(512)

EXTERNAL NAME TextFunctions.RegularExpressions.RegExReplace
GO

Using the Functions to Convert RTF to Plain Text

The regular expression specified leaves a few unwanteds characters so these are stripped using the SQL replace function. Obviously the regular expression could be extended to do this.

SELECT
   ltrim(replace(replace(replace(
       dbo.RegExReplace(note_text,'({\\)(.+?)(})|(\\)(.+?)(\b)','')
      ,'}','')
      ,char(13),'')
      ,char(10),'')
      )
FROM
   dbo.tbl_note

Converting RTF to Plain text in SSIS

RichText formatted data can easily be converted to plain text using a regular expression in the script component in SSIS.

The regular expression to strip out rtf formatting is as follows:

({\\)(.+?)(})|(\\)(.+?)(\b)

The following steps outline the creation of an SSIS package to extract records from a database table to a delimited text file where the data in one of the columns is formatted as RTF and the output file requires plain text.

1. Create a new SSIS data flow
   
   
2. Add an OLE DB Source to access the records. The column containing the rtf data is assumed to be called body.
   
   
3. Add a script component to the data flow and attach it to the output of the OLE DB Source.
   
   
4. In the script component properties ensure the body field is selected as an input column
   
   
5. In the script component properties add an output column body_converted, give it a datatype of DT_STR and a suitable length.
   
   
6. Click the Edit Script button and then use the following code in the ProcessInputRow method.
   
   
   
   Regex regExObj = new Regex("({\\\\)(.+?)(})|(\\\\)(.+?)(\\b)");
   System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
   String strBody = enc.GetString(Row.body.GetBlobData(0,int)Row.body.Length));
   Row.bodyconverted = regExObj.Replace(strBody, "").Replace("}", "");
   
   
   
7. Add aflat file destination and map the columns from the script component to it ensuring you use the body_converted column and not the original body column.
   
   
8. Run the SSIS pacakge.

You should now have a file which contains records with plain text instead of rtf.

The regular expression works on all scenarios I have tested so far, but if you come across tags where it misses then it should be a simple case of extending the expression to catch them.

Data Matching Algorithms

Researching data matching algorithms for use in an MDM project.

Levenshtein Distance in SQL

For a description of Levenshtein distance see wikipedia article

CREATE Function lev_distance ( @source VARCHAR(100) ,@target VARCHAR(100) ) RETURNS int AS BEGIN DECLARE @distance varchar(30) ,@len_source int ,@len_target int ,@counter int DECLARE @matrix TABLE(x int, y int, value int) SET @len_source = (SELECT len(@source)) SET @len_target = (SELECT len(@target)) -- initialize the first row 0..len_source SET @counter = 0 WHILE @counter <= @len_source + 1 BEGIN INSERT INTO @matrix VALUES (@counter, 0, @counter) SET @counter = @counter + 1 END -- initialize the first column 0..len_target SET @counter = 1 WHILE @counter <= @len_target + 1 BEGIN INSERT INTO @matrix VALUES (0, @counter, @counter) SET @counter = @counter + 1 END -- evaluate character distance DECLARE @cost int ,@up int ,@left int ,@diag int ,@source_index int ,@target_index int SET @source_index = 1 SET @target_index = 1 WHILE @source_index <= @len_source + 1 BEGIN WHILE @target_index <= @len_target + 1 BEGIN SET @cost = (CASE WHEN SUBSTRING(@source, @source_index, 1) = SUBSTRING(@target, @target_index, 1) THEN 0 ELSE 1 END) SET @up = (SELECT [value] FROM @matrix WHERE [x] = @source_index AND [y] = @target_index - 1) + 1 SET @left = (SELECT [value] FROM @matrix WHERE [x] = @source_index - 1 AND [y] = @target_index) + 1 SET @diag = (SELECT [value] FROM @matrix WHERE [x] = @source_index - 1 AND [y] = @target_index - 1) + @cost SET @cost = (CASE WHEN (@up <= @left) AND (@up <= @diag) THEN @up WHEN (@left <= @up) AND (@left <= @diag) THEN @left WHEN (@diag <= @up) AND (@diag <= @left) THEN @diag END) INSERT INTO @matrix VALUES (@source_index, @target_index, @cost) SET @target_index = @target_index + 1 END SET @target_index = 1 SET @source_index = @source_index + 1 END -- get result SELECT @distance = value FROM @matrix WHERE x = len(@source) AND y = len(@target) RETURN @distance END GO

Data Management Related Standards

ISO/IEC 38500
Corporate governance of information technology standard, provides a framework for effective governance of IT to assist those at the highest level of organizations to understand and fulfill their legal, regulatory, and ethical obligations in respect of their organizations’ use of IT.


ISO8000 - Data quality.


AS4590 - Interchange of Client Information

Powershell SMTP

function Send-SMTPmail($to, $from, $subject, $body, $attachment, $cc, $bcc, $port, $timeout, $smtpserver, [switch] $html, [switch] $alert) {
if ($smtpserver -eq $null) {$smtpserver = "smtp.myserver.com"}
$mailer = new-object Net.Mail.SMTPclient($smtpserver)
if ($port -ne $null) {$mailer.port = $port}
if ($timeout -ne $null) {$mailer.timeout = $timeout}
$msg = new-object Net.Mail.MailMessage($from,$to,$subject,$body)
if ($html) {$msg.IsBodyHTML = $true}
if ($cc -ne $null) {$msg.cc.add($cc)}
if ($bcc -ne $null) {$msg.bcc.add($bcc)}
if ($alert) {$msg.Headers.Add("message-id", "<3bd50098e401463aa228377848493927-1>")}
if ($attachment -ne $null) {
$attachment = new-object Net.Mail.Attachment($attachment)
$msg.attachments.add($attachment)
}
$mailer.send($msg)
}

Database Deployment scripts in Powershell

To enable all database changes to be deployed to differenet environments;

firstly ensure each change is scripted in a seperate file.

create a build file and record the name of each change file within it.

use the following powershell script to read the build file and submit the change files one at a time.

$server_name = read-host -Prompt "Servername"
$db_name = read-host -Prompt "Database"

# connect to server
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server_name
if ($windows_login -eq $false)
{
# prompt for connection details
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$username = $credential.UserName -replace("\\","")
$server.ConnectionContext.set_Login($username)
$server.ConnectionContext.set_SecurePassword($credential.Password)
}
$db = $server.Databases[$db_name]

# read the build list
$build_list = (get-content C:\Projects\build-list.txt)

foreach ($filename in $build_list)
{
write-host "running " $filename
$stream = New-Object System.IO.StreamReader($filename)
$script = $stream.ReadToEnd()

$db.ExecuteNonQuery( $script, 1 )
}
write-host "build complete"

Documenting SQL Server Jobs Using Powershell

The following script can be used to create wiki style documentation for all SQL server jobs.

# Load SMO libraries
[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

# define connection properties
[string] $server_name = '(local)'
[string] $project_name = $server_name
[string] $doc_root = "C:\dba\db_docs\jobs\"
[bool] $windows_login = $true


##############################################################
# Main
##############################################################

# connect to server
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server_name

if ($windows_login -eq $false)
{
# prompt for connection details
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$username = $credential.UserName -replace("\\","")
$server.ConnectionContext.set_Login($username)
$server.ConnectionContext.set_SecurePassword($credential.Password)
}

# create database directories, $doc-root
new-item -path $doc_root -name $project_name -type directory -force > $null
$file_name = $doc_root + "\" + $project_name + "\" + $project_name + " jobs" + ".wiki"
new-item $file_name -type file -force

add-content $file_name " || Job || Description || Created || Updated || Enabled || Schedules || "

foreach ($job in $server.jobserver.jobs)
{
$content = " | " + $job.name + " | " `
+ $job.description + " | " `
+ $job.datecreated + " | " `
+ $job.DateLastModified + " | " `
+ $job.IsEnabled+ " | "


foreach($schedule in $job.JobSchedules)
{
$content = $content + $schedule.FrequencyTypes

if($schedule.FrequencySubDayTypes -ne "once")
{
$content = $content + " every " `
+ $schedule.FrequencySubDayInterval + " " `
+ $schedule.FrequencySubDayTypes
}
}
$content = $content + " | "
add-content $file_name $content

}

Creating Database Documentation with Powershell

Creating database documentation can be time consuming and difficult to maintain. Have you ever tried to persuade developers or DBAs of the benefits of documentation. They are quick to complain when there is no documentation on a system they are forced to maintain.

The following powershell script provides a simple method of creating documentation for database objects that can then be uploaded to your wiki.

1. Load the required SMO libraries

#Load SMO libraries
[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

2. Define database connection properties

# define connection properties
[string] $server_name = '(local)'
[string] $db_name = 'master'
[string] $project_name = "MyProject"
[string] $doc_root = "C:\dba\db_docs\"
[bool] $windows_login = $false

3. I use winscp to publish the completed documentation to the wiki. The following function specifies the command line for this.

function ftp-files
{

. "C:\Program Files\WinSCP3\winscp3.exe" /console /command "option batch on" "option confirm off" "open myProfile" "put C:\dba\db_docs\$project_name\*.* /wiki/db_doc/$project_name/" "close" "exit"

}

4. This function creates the top level database page. It can be extended to add any required database or server details.

##############################################################
# doc-database
##############################################################
function doc-database
{
param($db_dir)

$file_name = $db_dir + "\" + $project_name + " Database " + ".wiki"
new-item $file_name -type file -force

add-content $file_name "h1. Overview"
add-content $file_name "{excerpt}...{excerpt}"

add-content $file_name "h1. Environment"
add-content $file_name "|| Designation || Server || Database || OS || SQL ||"

$string = "| Development | | " + $db_name + " | | |"
add-content $file_name $string
add-content $file_name "| Beta | | | | |"
add-content $file_name "| Live | | | | |"

add-content $file_name "h1. Database Objects"

$string = "h2. [Tables|" + $project_name + " Tables]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Tables}"
add-content $file_name $string

$string = "h2. [Views|" + $project_name + " Views]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Views}"
add-content $file_name $string

$string = "h2. [Stored Procedures|" + $project_name + " Stored Procedures]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Stored Procedures}"
add-content $file_name $string

$string = "h2. [Functions|" + $project_name + " Functions]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Functions}"
add-content $file_name $string

$string = "h1. [Update Process|" + $project_name + " Update Process]"
add-content $file_name $string

add-content $file_name "h1. Attachments"
add-content $file_name "{attachments}"
}

5. fucntion to extract the extended properties from stored procedure parameters.

##############################################################
# doc-params
##############################################################
function doc-params
{
param($file_name, $parameters)

add-content $file_name "|| Parameter || Data Type || Default || Description ||"
foreach ($param in $parameters)
{
$string = "| " + $param.name + " | " + $param.datatype
if ($param.datatype.name -eq 'varchar')
{
$string = $string + "(" + $param.datatype.maximumlength + ")"
}
$string = $string + " | " + $param.default
$string = $string + " | "
$string = $string + $param.ExtendedProperties["MS_Description"].value
$string = $string + " | "

add-content $file_name $string
}
}

6. Fucntion to document all indexes.

##############################################################
# doc-indexes
##############################################################
function doc-indexes
{
param($file_name, $table)

add-content $file_name 'h2. Indexes'
add-content $file_name '|| Key Name || Columns || Included || Clustered ||'

foreach($index in $table.indexes)
{
$col_list = ""
$inc_list = ""

foreach($col in $index.indexedcolumns)
{
if($col.isincluded -eq "true")
{
$inc_list = $inc_list + $col.name + " "
}
else
{
$col_list = $col_list + $col.name + " "
}
}
$string = "| "+ $index.name + "| " + $col_list +"| " + $inc_list + "| " + $index.isclustered + "|"
add-content $file_name $string
}
}

7. Function to document a table.

##############################################################
# doc-table
##############################################################
function doc-table
{
param($table_dir, $table)

$file_name = $table_dir+ "\" + $project_name + " Table - " + $table.name + ".wiki"
new-item $file_name -type file -force

add-content $file_name "h1. $($table.name) "

add-content $file_name "{excerpt}"
add-content $file_name $table.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

add-content $file_name '|| Field || Type || Nullable || Key || Default || Description ||'

foreach($column in $table.columns)
{
$column_text = "|" + $column.name + "|" + $column.datatype

if ($column.datatype.name -eq 'varchar')
{
$column_text = $column_text + "(" + $column.datatype.maximumlength + ")"
}

$column_text = $column_text + " | " + $column.nullable + "|" + $column.inprimarykey `
+ "|" + $column.default + " | " `
+ $column.ExtendedProperties["MS_Description"].value `
+ " |"

add-content $file_name $column_text
}
doc-indexes $file_name $table
}

8. Fucntion to document a view.

##############################################################
# doc-view
##############################################################
function doc-view
{
param($view_dir, $view)

$file_name = $view_dir+ "\" + $project_name + " View - " + $view.name + ".wiki"
new-item $file_name -type file -force

add-content $file_name "h1. $($view.name) "
add-content $file_name "{excerpt}"
add-content $file_name $view.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

add-content $file_name '|| Field || Type || Description ||'

foreach($column in $view.columns)
{
$column_text = "|" + $column.name + "|" + $column.datatype

if ($column.datatype.name -eq 'varchar')
{
$column_text = $column_text + "(" + $column.datatype.maximumlength + ")"
}

$column_text = $column_text + " | " `
+ $column.ExtendedProperties["MS_Description"].value `
+ " |"
add-content $file_name $column_text
}

add-content $file_name "{code}"
add-content $file_name $view.TextHeader
add-content $file_name $view.TextBody
add-content $file_name "{code}"
}

9. Fucntion to document a stored procedure. When creating stored procedures I use a standard template and add extended properties. Both of which are used when constructing the documentation page.

##############################################################
# doc-procedure
##############################################################
function doc-procedure
{
param($proc_dir, $proc)

$file_name = $proc_dir + "\" + $project_name + " Stored Procedure - " + $proc.name + ".wiki"
new-item $file_name -type file -force

# add description as excerpt
add-content $file_name "h1. $($proc.name) "
add-content $file_name "{excerpt}"
add-content $file_name $proc.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

# add parameters
add-content $file_name "h1. Parameters"
doc-params $file_name $proc.parameters

# return value
add-content $file_name "h1. Return Value"
add-content $file_name $proc.ExtendedProperties["RETURN_VALUE"].value

# result set
add-content $file_name "h1. Result Set"
add-content $file_name $proc.ExtendedProperties["RESULT_SET"].value

# add the code
add-content $file_name "h1. Syntax"
add-content $file_name "{code}"
if($proc.IsEncrypted -eq $false)
{
if ($proc.TextHeader.IndexOf("Description:") -gt -1 `
-and $proc.TextHeader.IndexOf("Parameters:") -gt -1 `
-and $proc.TextHeader.IndexOf("Parameters:", $proc.TextHeader.IndexOf("Description:")) -gt -1 `
)
{
add-content $file_name $proc.TextHeader.substring($proc.TextHeader.ToUpper().IndexOf("CREATE PROCEDURE") `
,$proc.TextHeader.length - $proc.TextHeader.ToUpper().IndexOf("CREATE PROCEDURE")).trim().trimend("AS").trimend("as")
}
else
{
add-content $file_name $proc.TextHeader.trim().trimend("AS").trimend("as")
}
}
else
{
add-content $file_name "Procedure Encrypted"
}
add-content $file_name "{code}"

}

10. Fucntion to document a function.

##############################################################
# doc-function
##############################################################
function doc-function
{
param($func_dir, $func)

$file_name = $func_dir + "\" + $project_name + " Function - " + $func.name + ".wiki"
new-item $file_name -type file -force

# add description as excerpt
add-content $file_name "h1. $($func.name)"
add-content $file_name "{excerpt}"
add-content $file_name $proc.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

# add parameters
add-content $file_name "h1. Parameters"
doc-params $file_name $func.parameters

# return value
add-content $file_name "h1. Return Value"
add-content $file_name $proc.ExtendedProperties["RETURN_VALUE"].value

# add the code
add-content $file_name "h1. Syntax"
add-content $file_name "{code}"
if($func.IsEncrypted -eq $false)
{
if ($func.TextHeader.IndexOf("Description:") -gt -1 `
-and $func.TextHeader.IndexOf("Parameters:") -gt -1 `
-and $func.TextHeader.IndexOf("Parameters:", $func.TextHeader.IndexOf("Description:")) -gt -1 `
)
{
add-content $file_name $func.TextHeader.substring($func.TextHeader.ToUpper().IndexOf("CREATE FUNCTION") `
,$func.TextHeader.length - $func.TextHeader.ToUpper().IndexOf("CREATE FUNCTION")).trim().trimend("AS").trimend("as")
}
else
{
add-content $file_name $func.TextHeader.trim().trimend("AS").trimend("as")
}
}
else
{
add-content $file_name "Function Encrypted"
}
add-content $file_name "{code}"

}

11. and this tiee it all together.

##############################################################
# Main
##############################################################

# connect to server
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server_name

if ($windows_login -eq $false)
{
# prompt for connection details
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$username = $credential.UserName -replace("\\","")
$server.ConnectionContext.set_Login($username)
$server.ConnectionContext.set_SecurePassword($credential.Password)
}

$db = $server.Databases[$db_name]

# create database directories, $doc-root
new-item -path $doc_root -name $project_name -type directory -force

$db_dir = $doc_root + $project_name
$table_dir = $db_dir + "\Tables"
$view_dir = $db_dir + "\Views"
$proc_dir = $db_dir + "\Stored Procedures"
$func_dir = $db_dir + "\Functions"

new-item $table_dir -type directory -force
new-item $proc_dir -type directory -force
new-item $func_dir -type directory -force

doc-database $db_dir

####################################
# document all tables
####################################
$file_name = $table_dir + "\" + $project_name + " Tables" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($table in $db.Tables)
{
doc-table $table_dir $table
}

####################################
# document all views
####################################
$file_name = $view_dir + "\" + $project_name + " Views" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($view in $db.Views)
{
if($view.IsSystemObject -ne "true")
{
doc-view $view_dir $view
}
}


####################################
# document all stored procedures
####################################
$file_name = $proc_dir + "\" + $project_name + " Stored Procedures" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($proc in $db.StoredProcedures)
{
if($proc.IsSystemObject -ne "true")
{
doc-procedure $proc_dir $proc
}
}

####################################
# document all functions
####################################
$file_name = $func_dir + "\" + $project_name + " Functions" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($func in $db.UserDefinedFunctions)
{
if($func.IsSystemObject -ne "true")
{
doc-function $func_dir $func
}
}

12. If you want to make use of winscp to copy files to the wiki then make a cal lto the ftp-files function.

ftp-files