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 也使用.
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
