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.
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.
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.
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%'
SELECT job_id, name, date_created AS CreateDate, date_modified _
AS ModifyDate, 'Job' AS type FROM msdb.dbo.sysjobs
Database Backup
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
BEXEC sp_detach_db @dbname = 'DATABASE_NAME'
Attach Database
EXEC sp_attach_single_file_db @dbname = 'DATABASE_NAME', _
@physname = 'C:\YOUR_DATABASE_FILE'
Truncate Log
DBCC SHRINKFILE ([LOG_NAME] , 0, TRUNCATEONLY)
Shrink Database
DBCC SHRINKDATABASE ([YOUR_DATABASE_NAME])
Set Database Online / Offline
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.
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
EXEC sp_spaceused 'TABLE_NAME'
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);
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
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)
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'
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
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;
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.
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.
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
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".
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)'),
@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#.
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
- Activities capture & alert notifications
- 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
