MS- SQL数据库系统表的总结与应用

有一个是用Rollback Transaction来回滚操作

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' and status>0代表是用户建立的表，对象名就是表名，对象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 也使用.

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lhsxsh/archive/2009/03/02/3948634.aspx