SQL Server 带有版本控制的监视器
监控 SQL Server 进程和作业，查看正在执行的 SQL 查询，终止进程/作业，对象资源管理器，数据库收缩/日志截断/备份/分离/附加，对象版本控制和比较
引言
这里有包含最新版本的第二篇文章：sqlmonitor.aspx。
这是一个开源项目，托管在 http://sqlmon.codeplex.com/。
它可以监控 SQL Server 进程和作业，查看正在执行的 SQL 查询，终止进程/作业，对象资源管理器，数据库收缩/日志截断/备份/分离/附加，对象版本控制和比较。
它使用 C# 和 Winform 以及 Linq，需要 .NET 3.5（客户端配置文件）。
实现起来很快，而且有些粗糙，非常直接，但我努力保持逻辑清晰并完成任务。
背景
好吧，我必须承认我有时会感到无聊，所以我只想做些事情来消磨时间。 看起来我正在重新发明轮子（复制 SQL Server Management Studio 的一部分？），嗯，实际上，我不这么认为。 我在这里处理 SQL Server Management Studio 中根本不存在的东西，至少在 2008R2 中不存在。
功能
对象资源管理器
在对象资源管理器中，您可以浏览服务器、数据库、表、视图、函数和存储过程。
//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();
}
}
数据库
对于每个数据库，您可以看到文件路径、数据库大小。
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'
对象搜索
嗯，您想使用通配符在表/视图/函数/存储过程之间搜索对象吗？ 或者您想搜索在函数或存储过程中使用的短语或对象吗？ 使用 SQL Monitor，您可以。 只需右键单击一个数据库，然后选择搜索。
--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
数据库备份
--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 a database
BEXEC sp_detach_db @dbname = 'DATABASE_NAME'
附加数据库
--attach a database
EXEC sp_attach_single_file_db @dbname = 'DATABASE_NAME', _
@physname = 'C:\YOUR_DATABASE_FILE'
截断日志
--truncate database log
DBCC SHRINKFILE ([LOG_NAME] , 0, TRUNCATEONLY)
收缩数据库
--shrink database
DBCC SHRINKDATABASE ([YOUR_DATABASE_NAME])
设置数据库联机/脱机
--set database state
ALTER DATABASE [YOUR_DATABASE_NAME] SET ONLINE
ALTER DATABASE [YOUR_DATABASE_NAME] SET OFFLINE
表格
当您选择一个数据库时，它将显示所有表，每个表将具有已用空间、记录、创建日期和上次修改日期。
当您选择一个表时，它将显示该表的脚本，而这在 SQL Server Management Studio 中是无法直接获得的。
--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
视图/函数/存储过程
当您选择一个数据库时，它将显示所有视图/函数/存储过程，每个都将具有创建日期和上次修改日期。
当您选择一个视图/函数/存储过程时，它将显示该对象的脚本。
--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
活动
在活动中，您可以浏览/终止当前正在运行的 SQL Server 进程和作业。
--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
版本控制
我从这里得到这个想法：http://www.sqlteam.com/article/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes.
基本思想是使用数据库触发器捕获 DDL 更改事件，并将它们存储在表中。
要启用版本控制，请选择一个数据库，右键单击，选择“启用版本控制”。
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
现在，它只能查看和比较版本。 在下一个版本中，它将能够回滚到某个版本。
为了进行版本比较，我使用了 C# 中的通用、可重用的 Diff 算法。
对于版本比较，它可以自动并排同步两个文本框。
除了比较版本之外，它还支持比较两个 SQL 脚本。
右键单击对象脚本文本框，然后选择“比较”，或者创建一个新的查询窗口，右键单击并选择“比较”。
关注点
待办事项
- 活动捕获和警报通知
- 分析
嗯，最初我开发这个应用程序是为了更容易地管理我们系统中复杂的数据库。 有 2000 多个存储过程，一些疯狂的表每个表拥有超过 200 亿条记录。 经常，我们需要修改我们的存储过程，我们需要搜索脚本并控制版本。 后来，我想也许还有其他数据库开发人员也想使用这个应用程序，所以我将它开源了。