Category Archives: CodeSnippets

Bulk documenting common columns in Sql Server 2008

Recently I have just discovered the very cool app that is DataDictionaryCreator http://datadictionary.codeplex.com/. For those of you don’t know, it is a windows application that allows you to document your database with the documentation stored as extended properties within the database itself.

Almost every table that we have in our database contains 2 common fields that have the same meaning called “CreateDate” and “ModifyDate”, I’ll leave you with the hard task of trying to work out what they are for. Anyway to save myself manually adding the same description for these I knocked up a little sql script to write the data to the db (I did cheat a bit and had a peak at the sql exported from DataDictionaryCreator).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
use [DATABASE_NAME]
go

set nocount on;

declare @ColumnName nvarchar (128)
declare @TableName nvarchar (128)
declare @MS_Description nvarchar (500)
declare @Debug bit = 0
declare @SchemaName nvarchar (500)

declare @CommonColumnDocumentation table
(
    ColumnName nvarchar (500),
    MS_Description nvarchar(500)
)

declare @TablesToProcess table
(
    ColumnName nvarchar (500),
    TableName nvarchar(500),
    SchemaName nvarchar(500)
)


insert into @CommonColumnDocumentation
select 'CreateDate', 'This is the date the row was created. It is populated via a default constraint.' union all
select 'ModifyDate', 'This is the date the row was last modified. It is populated via an update trigger (If it is not being updated correctly please check the trigger exists).'

insert into @TablesToProcess
(
    ColumnName,
    TableName,
    SchemaName
)
select
    sc.name as ColumnName,
    so.Name as TableName,
    ss.Name as SchemaName
from syscolumns sc
 join sys.all_objects so on sc.id = so.object_id
 join sys.schemas ss on so.schema_id = ss.schema_id
where so.type = 'U'
    and sc.Name in (select ColumnName from @CommonColumnDocumentation)

while exists (select * from @TablesToProcess)
    begin
        select top 1
            @SchemaName = ttp.SchemaName,
            @ColumnName = ccd.ColumnName,
            @TableName = ttp.TableName,
            @MS_Description = ccd.MS_Description           
        from @TablesToProcess ttp
            join @CommonColumnDocumentation ccd on ccd.ColumnName = ttp.ColumnName
           
        if (@ColumnName is not null and @TableName is not null and @MS_Description is not null)
            begin
                print 'Updating documentation for ' + @SchemaName + '.' + @TableName + '.' + @ColumnName
               
                if(@Debug <> 1)
                    begin
                    if exists ( select 1 from ::fn_listextendedproperty (
                                    N'MS_Description',
                                    'Schema', @SchemaName,
                                    'Table', @TableName,
                                    'Column', @ColumnName))
                        begin
                            exec sp_dropextendedproperty
                              @name = 'MS_Description',
                              @level0type = 'Schema', @level0name = @SchemaName,
                              @level1type = 'Table', @level1name = @TableName,
                              @level2type = 'Column', @level2name = @ColumnName;
                        end
                       
                        exec sp_addextendedproperty
                            @name = N'MS_Description',
                            @value = @MS_Description,
                            @level0type = N'Schema', @level0name = @SchemaName,
                            @level1type = N'Table',  @level1name = @TableName,
                            @level2type = N'Column', @level2name = @ColumnName;
                    end
            end
           
            delete from @TablesToProcess
            where TableName = @TableName
                and ColumnName = @ColumnName       
       
    end

As always use at your own risk, and if anyone has any suggestions…

Using PowerShell to record ping failures to a logfile

Recently we’ve been having some major problems with our websites not being able to connect to our Sql2008 cluster. Part of the analysis I was asked to run (yes I do seem to do everything here!!) was a simple ping test. We started off with the classic command line ping to file using the pipeline (ping IPADDRESS -t > LogFile.txt). This is good but has one major flaw, the results don’t record the date and time to file, all you can see is that it DID fail. So I decided to use my favourite scripting language to knock up something a bit better and this is what I came up with:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
$serverName = "[IpAddress]";
$showAllResults = $false;
$logFilename = "[PathToLogFile]";
$ping = new-object Net.NetworkInformation.Ping;
$minRoundTripTime = 500;

function WriteToOutput($filename, $text)
{
    $text | Out-File $filename -Append;
    Write-Host $text;
}

WriteToOutput $logFilename "$(Get-Date): Starting ping logging...`nPress any key to stop!";

do
{
    $result = $ping.send($serverName);
    if($showAllResults -or $result.Status -ne "Success" -or $result.RoundtripTime -gt $minRoundTripTime)
    {          
        WriteToOutput $logFilename  "$(Get-Date) -Status: $($result.Status) -time: $($result.RoundTripTime)`n";
    }
}
until ($Host.UI.RawUI.KeyAvailable);

WriteToOutput $logFilename "$(Get-Date): Ending ping logging...`n`n";

Let me know if you have any suggestions to improve it!! 🙂

Killing database connections with PowerShell

Recently I have been setting up a PowerShell script to automatically setup our PY (usually aka SIT) environment. One of the tasks in the script is to Kill all the connections to a database so I can restore a backup. I managed to find a function that met my needs using the SMO libraries. Unfortunately as we are still on Sql2000 (I know, I know! Its not for much longer now tho! 😀 ) the SMO Libraries were not available on our database server so I had to come up with an alternative. Basically I decided to go the route that required the least dependancies and this is what I came up with!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
function KillDBConnections([string]$serverName, [string]$DBName)
{
    $ConnectionString = "Data Source=$serverName;Initial Catalog=master;Integrated Security=SSPI"
    $connection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString);
    $command = New-Object System.Data.SqlClient.SqlCommand;

    $command.Connection = $connection;
    $command.CommandType = [System.Data.CommandType]::Text;
    $command.CommandText = "SELECT spid FROM master..sysprocesses WHERE dbid=db_id('$DBName')";

    $connection.open();
    $reader = $command.ExecuteReader();
    $stringBuilder = New-Object System.Text.StringBuilder

    while ($reader.Read())
    {
        $stringBuilder.AppendFormat("kill {0};", $reader.GetValue(0));
    }

    $reader.Close();

    $command.CommandText = $stringBuilder.ToString();

    if($command.CommandText)
    {
        $command.ExecuteNonQuery();
    }
   
    $connection.Dispose();
}