Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

SQL Server Monitor with Version Control

4.92/5 (24 votes)
2 Dec 2011LGPL33 min read 103.1K   3.6K  
Monitor SQL Server processes and jobs, view executing SQL query, kill process / job, object explorer, database shrink/log truncate/backup/detach/attach, object version control & compare

Introduction

There is a second article here with the latest version: sqlmonitor.aspx.

It's an open source project, which is hosted at http://sqlmon.codeplex.com/.

It can monitor SQL Server processes and jobs, view executing SQL query, kill process / job, object explorer, database shrink/log truncate/backup/detach/attach, object version control & compare.

It uses C# with Winform and Linq and requires .NET 3.5 (client profile).

The implementation is quick and a little dirty, pretty straight forward, but I try to maintain the logic and get the job done.

Background

Ok, I have to admit that I got bored from time to time so I just want to make something to kill time. It looks like I am reinventing a wheel (duplicating part of SQL Server Management Studio?), mmm, actually, I don't think so. I address something here that does not exist in SQL Server Management Studio at all, at least not in 2008R2.

Functionalities

Object Explorer

In object explorer, you can browse servers, databases, tables, views, functions and stored procedures.

SQLMonitor1.png

C#
//Listing databases   
using (SqlConnection connection = NewConnection)
{
   connection.Open();
   var data = connection.GetSchema("Databases");
   data.AsEnumerable().OrderBy(r => r.Field<string>("database_name")).ForEach((d) =>
   {
        var name = d["database_name"].ToString();
   }
}
Database

For each database, you can see the file paths, database size.

SQL
SELECT DB_NAME(database_id) AS DatabaseName, Name AS Logical_Name, _
    Physical_Name, CAST(size AS decimal(30,0))*8 AS Size, _
    state FROM sys.master_files WHERE DB_NAME(database_id) = 'YOUR_DATABASE_NAME' 
Object Search

Well, do you want to search an object across tables/views/functions/stored procedures with wild char? Or do you want to search a phrase or an object that is being used in a function or stored procedure? With SQL Monitor, you can. Simply right click on a database, and choose search.

SQL
--search in script
Select s.name, s.create_date AS CreateDate, s.modify_date AS ModifyDate, _
    s.type, c.text from syscomments c left join sys.objects s _
    on c.id = s.object_id where [Text] like '%YOUR_QUERY_HERE%'

--search in jobs
SELECT job_id, name, date_created AS CreateDate, date_modified _
    AS ModifyDate, 'Job' AS type FROM msdb.dbo.sysjobs
Database Backup
SQL
--backup a database
BACKUP DATABASE DATABASE_TO_BACKUP TO DISK = 'C:\YOUR_BACKUP_FILE' _
  WITH NOFORMAT, NOINIT,  NAME = N'BACKUP_NAME', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Detach Database
SQL
--detach a database
BEXEC sp_detach_db @dbname = 'DATABASE_NAME'
Attach Database
SQL
--attach a database
EXEC sp_attach_single_file_db @dbname = 'DATABASE_NAME', _
    @physname = 'C:\YOUR_DATABASE_FILE'
Truncate Log
SQL
--truncate database log
DBCC SHRINKFILE ([LOG_NAME] , 0, TRUNCATEONLY)
Shrink Database
SQL
--shrink database
DBCC SHRINKDATABASE ([YOUR_DATABASE_NAME])
Set Database Online / Offline
SQL
--set database state
ALTER DATABASE [YOUR_DATABASE_NAME] SET ONLINE

ALTER DATABASE [YOUR_DATABASE_NAME] SET OFFLINE
Table

When you choose a database, it will show all tables, each will have spaced used, records, created date and last modified date.
When you select a table, it will show the script of the table, which we do not have in SQL Server Management Studio directly.

SQL
--To get table names and records
SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name
--To get table used space
EXEC sp_spaceused 'TABLE_NAME'
--To get table script
declare @Id int, @i int, @i2 int,@Sql varchar(max),@Sql2 varchar(max), _
    @f1 varchar(5), @f2 varchar(5), @f3 varchar(5), @f4 varchar(5), @T varchar(5)
    select @Id=object_id('YOUR_TABLE_NAME_HERE'), @f1 = char(13) + _
    char(10), @f2 = '    ', @f3=@f1+@f2, @f4=',' + @f3
    
    if not(@Id is null)
    BEGIN
    declare @Data table(Id int identity primary key, D varchar(max) not null, _
    ic int null, re int null, o int not null);
    
    -- Columns
    with c as(
        select c.column_id, Nr = row_number() over(order by c.column_id), _
        Clr=count(*) over(),
            D = quotename(c.name) + ' ' +
                case when s.name = 'sys' or c.is_computed=1 _
        then '' else quotename(s.name) + '.' end +
                case when c.is_computed=1 then '' when s.name = 'sys' _
            then t.Name else quotename(t.name) end +
                case when c.user_type_id!=c.system_type_id or c.is_computed=1 then ''
                    when t.Name in ('xml', 'uniqueidentifier', 'tinyint', _
            'timestamp', 'time', 'text', 'sysname', 'sql_variant', _
            'smallmoney', 'smallint', 'smalldatetime', 'ntext', 'money',
                                    'int', 'image', 'hierarchyid', 'geometry', _
                'geography', 'float', 'datetimeoffset', 'datetime2', _
                'datetime', 'date', 'bigint', 'bit') then ''
                    when t.Name in('varchar','varbinary', 'real', 'numeric', 'decimal', _
            'char', 'binary')
                        then '(' + isnull(convert(varchar,nullif(c.max_length,-1)), _
              'max') + isnull(','+convert(varchar,nullif(c.scale, 0)), '') + ')'
                    when t.Name in('nvarchar','nchar')
                        then '(' + isnull(convert(varchar,nullif(c.max_length,-1) / 2), _
            'max') + isnull(','+convert(varchar,nullif_
            (c.scale, 0)), '') + ')'
                    else '??'
                    end + 
                case when ic.object_id is not null then ' identity_
        (' + convert(varchar,ic.seed_value) + ',' + _
        convert(varchar,ic.increment_value) + ')' else '' end +
                case when c.is_computed=1 then 'as' + cc.definition when _
        c.is_nullable = 1 then ' null' else ' not null' end +
                case c.is_rowguidcol when 1 then ' rowguidcol' else '' end +
                case when d.object_id is not null then ' default ' + _
        d.definition else  '' end
        from sys.columns c
        inner join sys.types t
        on t.user_type_id = c.user_type_id
        inner join sys.schemas s
        on s.schema_id=t.schema_id
        left outer join sys.computed_columns cc
        on cc.object_id=c.object_id and cc.column_id=c.column_id
        left outer join sys.default_constraints d
        on d.parent_object_id=@id and d.parent_column_id=c.column_id
        left outer join sys.identity_columns ic
        on ic.object_id=c.object_id and ic.column_id=c.column_id
        where c.object_id=@Id
        
    )
        insert into @Data(D, o)
        select '    ' + D + case Nr when Clr then '' else ',' + @f1 end, 0
        from c where NOT D IS NULL 
        order by column_id
    
    -- SubObjects
    set @i=0
    while 1=1
        begin
        select top 1 @i=c.object_id, @T = c.type, @i2=i.index_id
        from sys.objects c 
        left outer join sys.indexes i
        on i.object_id=@Id and i.name=c.name
        where parent_object_id=@Id and c.object_id>@i and c.type not in('D')
        order by c.object_id
        if @@rowcount=0 break
        if @T = 'C' 
            insert into @Data 
            select @f4 + 'check ' + case is_not_for_replication when 1 _
    then 'not for replication ' else '' end + definition, null, null, 10
            from sys.check_constraints where object_id=@i
        else if @T = 'Pk'
            insert into @Data 
            select @f4 + 'primary key' + isnull(' ' + _
        nullif(lower(i.type_desc),'clustered'), ''), @i2, null, 20
            from sys.indexes i
            where i.object_id=@Id and i.index_id=@i2
        else if @T = 'uq'
            insert into @Data values(@f4 + 'unique', @i2, null, 30)
        else if @T = 'f'
            begin
            insert into @Data 
            select @f4 + 'foreign key', -1, @i, 40
            from sys.foreign_keys f
            where f.object_id=@i
            
            insert into @Data 
            select ' references ' + quotename(s.name) + '.' + _
        quotename(o.name), -2, @i, 41
            from sys.foreign_keys f
            inner join sys.objects o
            on o.object_id=f.referenced_object_id
            inner join sys.schemas s
            on s.schema_id=o.schema_id
            where f.object_id=@i
            
            insert into @Data 
            select ' not for replication', -3, @i, 42
            from sys.foreign_keys f
            inner join sys.objects o
            on o.object_id=f.referenced_object_id
            inner join sys.schemas s
            on s.schema_id=o.schema_id
            where f.object_id=@i and f.is_not_for_replication=1
            end
        else
            insert into @Data values(@f4 + _
        'Unknow SubObject [' + @T + ']', null, null, 99)
        end

    insert into @Data values(@f1+')', null, null, 100)
    
    -- Indexes
    insert into @Data
    select @f1 + 'create ' + case is_unique when 1 then _
    'unique ' else '' end + lower(s.type_desc) + ' index ' + _
    'i' + convert(varchar, row_number() over(order by index_id)) + _
    ' on ' + quotename(sc.Name) + '.' + quotename(o.name), index_id, null, 1000
    from sys.indexes s
    inner join sys.objects o
    on o.object_id=s.object_id
    inner join sys.schemas sc
    on sc.schema_id=o.schema_id
    where s.object_id=@Id and is_unique_constraint=0 and is_primary_key=0 _
    and s.type_desc != 'heap'
    
    -- columns
    set @i=0
    while 1=1
        begin
        select top 1 @i=ic from @Data where ic>@i order by ic 
        if @@rowcount=0 break
        select @i2=0, @Sql=null, @Sql2=null
        while 1=1
            begin
            select @i2=index_column_id, 
                @Sql = case c.is_included_column when 1 then _
        @Sql else isnull(@Sql + ', ', '(') + cc.Name + _
        case c.is_descending_key when 1  then ' desc' else '' end end,
                @Sql2 = case c.is_included_column when 0 then @Sql2 _
        else isnull(@Sql2 + ', ', '(') + cc.Name + _
        case c.is_descending_key when 1  then ' desc' else '' end end
            from sys.index_columns c
            inner join sys.columns cc
            on c.column_id=cc.column_id and cc.object_id=c.object_id
            where c.object_id=@Id and index_id=@i and index_column_id>@i2
            order by index_column_id
            if @@rowcount=0 break
            end
        update @Data set D=D+@Sql +')' + isnull(' include' + @Sql2 + ')', '') where ic=@i
        end
        
    -- references
    set @i=0
    while 1=1
        begin
        select top 1 @i=re from @Data where re>@i order by re
        if @@rowcount=0 break
        
        select @i2=0, @Sql=null, @Sql2=null
        while 1=1
            begin
            select @i2=f.constraint_column_id, 
                @Sql = isnull(@Sql + ', ', '(') + c1.Name,
                @Sql2 = isnull(@Sql2 + ', ', '(') + c2.Name
            from sys.foreign_key_columns f
            inner join sys.columns c1
            on c1.column_id=f.parent_column_id and c1.object_id=f.parent_object_id
            inner join sys.columns c2
            on c2.column_id=f.referenced_column_id and _
        c2.object_id=f.referenced_object_id
            where f.constraint_object_id=@i and f.constraint_column_id>@i2
            order by f.constraint_column_id
            if @@rowcount=0 break
            end
        update @Data set D = D + @Sql + ')'  where re=@i and ic=-1
        update @Data set D = D + @Sql2 + ')'  where re=@i and ic=-2
        end;
    
    -- Render
    with x as(
        select id=d.id-1, D=d.D + isnull(d2.D,'')
        from @Data d
        left outer join @Data d2
        on d.re=d2.re and d2.o=42
        where d.o=41
        
    )
    update @Data
        set D=d.D+x.D
    from @Data d
    inner join x
    on x.id=d.id
    
    delete @Data where o in(41, 42)
    
    select @Sql = 'create table ' + quotename(s.name) + '.' + _
        quotename(o.name) + '(' + @f1
    from sys.objects o
    inner join sys.schemas s
    on o.schema_id = s.schema_id
    where o.object_id=@Id
    
    set @i=0
    while 1=1
        begin
        select top 1 @I=Id, @Sql = @Sql + D from @Data order by o, _
    case when o=0 then right('0000' + convert(varchar,id),5)  else D end, id
        if @@rowcount=0 break
        delete @Data where id=@i
        end
    END
    SELECT @Sql
View/Function/Stored Procedure

When you choose a database, it will show all views/functions/stored procedures, each will have created date and last modified date.
When you select a view/function/stored procedure, it will show the script of the object.

SQL
--To get object script
SELECT DISTINCT o.name, o.xtype, c.text, colid
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id WHERE name = 'YOUR_OBJECT_NAME_HERE' ORDER BY colid

Activities

In activities, you can browse/kill currently running SQL Server processes and jobs.

SQLMonitor2.png

SQL
--To get processes
SELECT s.session_id AS spid, s.login_time, s.host_name AS hostname, _
    s.host_process_id AS hostprocess, s.login_name AS loginname, _
    s.logical_reads AS physical_io, s.cpu_time AS cpu, s.program_name, _
    0 AS dbid, s.last_request_start_time AS last_batch_begin, _
    CASE WHEN status = 'running' THEN GETDATE() _
    ELSE dateadd(ms, s.cpu_time, s.last_request_end_time) _
    END AS last_batch_end, s.status FROM sys.dm_exec_sessions s _
    JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
--To get jobs
SELECT job_id AS spid, name AS program_name, 0 AS dbid, 0 AS cpu, _
    0 AS physical_io, NULL AS login_time, NULL AS last_batch_begin, _
    NULL AS last_batch_end, NULL AS status, NULL AS hostname, _
    NULL AS hostprocess, NULL AS cmd, NULL AS loginname FROM msdb.dbo.sysjobs

Version Control

I got the idea from here: http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes.

The basic idea is to capture DDL change events using database trigger, and store them in a table.

To enable version control, select a database, right click, choose "Enable Version Control".

SQLMonitor5.png

SQL
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = _
    OBJECT_ID(N'[dbo].[VERSION_CONTROL_TABLE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[{0}](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [databasename] [varchar](256) NULL,
    [eventtype] [varchar](50) NULL,
    [objectname] [varchar](256) NULL,
    [objecttype] [varchar](25) NULL,
    [sqlcommand] [nvarchar](max) NULL,
    [loginname] [varchar](256) NULL,
    [hostname] [varchar](256) NULL,
    [PostTime] [datetime] NULL,
    [Version] [int] NOT NULL,
 CONSTRAINT [PK_VERSION_CONTROL_TABLE] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [TRG_VERSION_CONTROL_TABLE}]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
CREATE_TABLE, ALTER_TABLE, DROP_TABLE,
CREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION,
CREATE_TRIGGER, ALTER_TRIGGER, DROP_TRIGGER,
CREATE_VIEW, ALTER_VIEW, DROP_VIEW,
CREATE_INDEX, ALTER_INDEX, DROP_INDEX

AS

SET NOCOUNT ON

DECLARE @CurrentVersion int
DECLARE @CurrentID int
DECLARE @DatabaseName varchar(256)
DECLARE @ObjectName varchar(256)
DECLARE @data XML

SET @data = EVENTDATA()

INSERT INTO dbo.VERSION_CONTROL_TABLE_
    (databasename, eventtype,objectname, objecttype, sqlcommand, _
    loginname,Hostname,PostTime, Version)
VALUES(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(256)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(50)'),  -- value is case-sensitive
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(256)'), 
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(25)'), 
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'), 
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(256)'),
HOST_NAME(),
GETDATE(),
0
)

SET @CurrentID = IDENT_CURRENT('VERSION_CONTROL_TABLE')
SELECT @DatabaseName = databasename, @ObjectName = objectname _
    FROM VERSION_CONTROL_TABLE WHERE ID = @CurrentID
IF (@DatabaseName IS NOT NULL AND @ObjectName IS NOT NULL)
BEGIN
    SELECT @CurrentVersion = MAX(Version) _
    FROM VERSION_CONTROL_TABLE WHERE databasename = @DatabaseName _
    AND objectname = @ObjectName
    UPDATE VERSION_CONTROL_TABLE SET Version = _
    ISNULL(@CurrentVersion, 0) + 1 WHERE ID = @CurrentID
END
GO
ENABLE TRIGGER [TRG_VERSION_CONTROL_TABLE] ON DATABASE

Right now, it can only view and compare versions. In the next version, it will be able to rollback to a certain version.

To compare verions, I use A Generic, Reusable Diff Algorithm in C#.

SQLMonitor6.png

For version comparison, it can automatically sync two text boxes side by side.

Besides comparing version, it supports to compare two SQL scripts.

Right click on object script text box and choose "Compare" or create new query window and right click and choose "Compare".

Points of Interest

To Do

  1. Activities capture & alert notifications
  2. Analysis

Well, initially I developed this application for easier management of the complicated databases in our system. There are over 2000 stored procedures, with some crazy tables holding more than 20 billion records each table. Frequently, we need to modify our stored procedures and we need to search script and control the versions. Later on, I think that maybe there are other database developers who would like to use this application, so I made it open source.

History

License

This article, along with any associated source code and files, is licensed under The GNU Lesser General Public License (LGPLv3)