有一个是用Rollback Transaction来回滚操作
Select * From master.dbo.sysservers 查询链接服务器信息
Select * From master.dbo.sysdatabases 查询本数据库信息
---------------------------------------------------------------------------------------------------------------------------
Sysobjects：SQL-SERVER的每个数据库内都有此系统表，它存放该数据库内创建的
所有对象，如约束、默认值、日志、规则、存储过程等，每个对象在表中占一行。
对象类型(xtype)。可以是下列对象类型中的一种：
C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程
当xtype='U' 代表是用户建立的表，对象名就是表名，对象ID就是表
---------------------------------------------------------------------------------------------------------------------------
syscolumns ：每个表和视图中的每列在表中占一行，存储过程中的每个参数在表
Select c.*,t.name
From dbo.syscolumns c left join dbo.systypes t on c.xtype=t.xtype
where c.id in (Select id From sysobjects
where name='NET_User')
---------------------------------------------------------------------------------------------------------------------------
Select * From sysaltfiles 主数据库 保存数据库的文件
Select * From syscharsets 主数据库 字符集与排序顺序
Select * From sysconfigures 主数据库 配置选项
Select * From syscurconfigs 主数据库 当前配置选项
Select * From sysdatabases 主数据库 服务器中的数据库
Select * From syslanguages 主数据库 语言
Select * From syslogins 主数据库 登陆帐号信息
Select * From sysoledbusers 主数据库 链接服务器登陆信息
Select * From sysprocesses 主数据库 进程
Select * From sysremotelogins 主数据库 远程登录帐号
Select * From syscolumns 每个数据库 列
Select * From sysconstrains 每个数据库 限制
Select * From sysfilegroups 每个数据库 文件组
Select * From sysfiles 每个数据库 文件
Select * From sysforeignkeys 每个数据库 外部关键字
Select * From sysindexes 每个数据库 索引
Select * From sysmembers 每个数据库 角色成员
Select * From sysobjects 每个数据库 所有数据库对象
Select * From syspermissions 每个数据库 权限
Select * From systypes 每个数据库 用户定义数据类型
Select * From sysusers 每个数据库 用户
--------------------------------------------------------------------------------------------------
sql server系统表详细说明
sysaltfiles 主数据库 保存数据库的文件
syscharsets 主数据库字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库当前配置选项
sysdatabases 主数据库服务器中的数据库
syslanguages 主数据库语言
syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
sysprocesses 主数据库进程
sysremotelogins主数据库 远程登录帐号
syscolumns 每个数据库 列
sysconstrains 每个数据库 限制
sysfilegroups 每个数据库 文件组
sysfiles 每个数据库 文件
sysforeignkeys 每个数据库 外部关键字
sysindexs 每个数据库 索引
sysmenbers 每个数据库角色成员
sysobjects 每个数据库所有数据库对象
syspermissions 每个数据库 权限
systypes 每个数据库 用户定义数据类型
sysusers 每个数据库 用户
---------------------------------------------------------------------------------------------------------------------------
SELECT OBJECT_NAME (id) [OBJECT_NAME] FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xusertype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
where d.name='NET_Department' --如果只查询指定表,加上此条件
order by a.id,a.colorder
显示每个表当前有多少行
SELECT TOP 100 Percent sysobjects.name,sysindexes.rows
FROM sysindexes with(nolock)
JOIN sysobjects with(nolock) ON sysindexes.id = sysobjects.id AND sysobjects.xtype = 'u'
WHERE sysindexes.indid in(0, 1)
ORDER By sysobjects.name ASC
-------------------------------------------------------------------------------------------------------------------
SELECT ---2005
TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
Column_id=C.column_id,
ColumnName=C.name,
Type=T.name,
Length=C.max_length,
Precision=C.precision,
Scale=C.scale,
NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
[Default]=ISNULL(D.definition,N''),
ColumnDesc=ISNULL(PFD.[value],N''),
Create_Date=O.Create_Date,
Modify_Date=O.Modify_date
FROM sys.columns C
INNER JOIN sys.objects O
ON C.[object_id]=O.[object_id]
AND O.type='U'
AND O.is_ms_shipped=0
INNER JOIN sys.types T
ON C.user_type_id=T.user_type_id
LEFT JOIN sys.default_constraints D
ON C.[object_id]=D.parent_object_id
AND C.column_id=D.parent_column_id
AND C.default_object_id=D.[object_id]
LEFT JOIN sys.extended_properties PFD
ON PFD.class=1
AND C.[object_id]=PFD.major_id
AND C.column_id=PFD.minor_id
LEFT JOIN sys.extended_properties PTB
ON PTB.class=1
AND PTB.minor_id=0
AND C.[object_id]=PTB.major_id
--where O.name='GoToneCustomService' -- 要查询的表名 如果不加默认是该库底下的所有表
---------------------------------------------------------------------------------------------------------------------------
4.如何得到服务器的IP地址
create table #ip(id int identity(1,1),re varchar(200))
declare @s varchar(1000)
set @s='ping '+left(@@servername,charindex('/',@@servername+'/')-1)+' -a -n 1 -l 1'
insert #ip(re) exec master..xp_cmdshell @s
select 服务器名=@@servername,IP地址=stuff(left(re,charindex(']',re)-1),1,charindex('[',re),'')
from #ip
where id=2
drop table #ip
---------------------------------------------------------------------------------------------------------------------------
9.如何知道哪些触发器被禁用?
--将trigger 在sysobjects 表中 status字段的值转换为二进制的，第12位为1则表示禁止，为0表示允许
select 表名=object_name(parent_obj),触发器名=name
,状态=case status & power(2,11) when 0 then N'启用' else N'禁用' end
from sysobjects where type='TR'
---------------------------------------------------------------------------------------------------------------------------
select j.name as jobName,step_id,s.step_name,command,database_name from sysjobs j
right outer join sysjobsteps s
on j.job_id= s.job_id
--where subsystem ='tsql'
order by j.job_id,s.step_id
---------------------------------------------------------------------------------------------------------------------------
作业：
SELECT 作业的名称 = name,
对作业的说明 = description,
计划运行作业的下一个日期 = (SELECT top 1 left(left(next_run_date,4)+'-'+right(next_run_date,4),7)+'-'+right(next_run_date,2)
FROM msdb.dbo.sysjobschedules
WHERE job_id = sysjobs.job_id),
计划运行作业的时间 = (SELECT top 1 left(left(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),4),5)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)
FROM msdb.dbo.sysjobschedules
WHERE job_id = sysjobs.job_id),
作业的执行状态 = CASE (SELECT top 1 run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
WHEN 0 THEN '失败'
WHEN 1 THEN '成功'
WHEN 2 THEN '重试'
WHEN 3 THEN '已取消'
WHEN 4 THEN '正在进行中'
END,
作业或步骤开始执行的日期 = (SELECT top 1 left(left(run_date,4)+'-'+right(run_date,4),7)+'-'+right(run_date,2)
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
作业或步骤开始的时间 = (SELECT top 1 left(left(left('000000',6-len(run_time))+ltrim(run_time),2)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),4),5)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),2)
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
执行作业或步骤所花费的时间 = (SELECT top 1 left(left(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'小时'+right(left('000000',6-len(run_duration))+ltrim(run_duration),4),5)+'分钟'+right(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'秒'
FROM msdb.dbo.sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
FROM msdb.dbo.sysjobs
---------------------------------------------------------------------------------------------------------------------------
SQL server 中的作业信息查询
作业信息存储在MSDB中.可以运行以下存储过程,监视作业的执行情况.
可以参考如下SQL:
SELECT 作业的名称 = name,
对作业的说明 = description,
计划运行作业的下一个日期 = (SELECT next_run_date
FROM sysjobschedules
WHERE job_id = sysjobs.job_id),
计划运行作业的时间 = (SELECT next_run_time
FROM sysjobschedules
WHERE job_id = sysjobs.job_id),
作业的执行状态 = CASE (SELECT top 1 run_status
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
WHEN 0 THEN '失败'
WHEN 1 THEN '成功'
WHEN 2 THEN '重试'
WHEN 3 THEN '已取消'
WHEN 4 THEN '正在进行中'
END,
作业或步骤开始执行的日期 = (SELECT top 1 run_date
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
作业或步骤开始的时间 = (SELECT top 1 run_time
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
执行作业或步骤所花费的时间 = (SELECT top 1 run_duration
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
FROM sysjobs
如果监视备份作业的执行计划,可以在描述中输入:"备份" 等字样,查询时候可以用description 描述信息过滤.如下:
(注意:对某些字段尽心了翻译和转换)
SELECT 作业的名称 = name,
对作业的说明 = description,
计划运行作业的下一个日期 = (SELECT left(left(next_run_date,4)+'-'+right(next_run_date,4),7)+'-'+right(next_run_date,2)
FROM sysjobschedules
WHERE job_id = sysjobs.job_id),
计划运行作业的时间 = (SELECT left(left(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),4),5)+':'+right(left('000000',6-len(next_run_time))+ltrim(next_run_time),2)
FROM sysjobschedules
WHERE job_id = sysjobs.job_id),
作业的执行状态 = CASE (SELECT top 1 run_status
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
WHEN 0 THEN '失败'
WHEN 1 THEN '成功'
WHEN 2 THEN '重试'
WHEN 3 THEN '已取消'
WHEN 4 THEN '正在进行中'
END,
作业或步骤开始执行的日期 = (SELECT top 1 left(left(run_date,4)+'-'+right(run_date,4),7)+'-'+right(run_date,2)
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
作业或步骤开始的时间 = (SELECT top 1 left(left(left('000000',6-len(run_time))+ltrim(run_time),2)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),4),5)+':'+right(left('000000',6-len(run_time))+ltrim(run_time),2)
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
执行作业或步骤所花费的时间 = (SELECT top 1 left(left(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'小时'+right(left('000000',6-len(run_duration))+ltrim(run_duration),4),5)+'分钟'+right(left('000000',6-len(run_duration))+ltrim(run_duration),2)+'秒'
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
FROM sysjobs
WHERE description LIKE '%备份%'
这样就可以监视到备份数据库计划的执行,前台页面就可以用Ajax来无刷新监视备份情况了.
测试后,SQL server 2005 也使用.