Select * From master.dbo.sysdatabases 查詢本數(shù)據(jù)庫信息
---------------------------------------------------------------------------------------------------------------------------
Sysobjects：SQL-SERVER的每個數(shù)據(jù)庫內(nèi)都有此系統(tǒng)表，它存放該數(shù)據(jù)庫內(nèi)創(chuàng)建的
所有對象，如約束、默認(rèn)值、日志、規(guī)則、存儲過程等，每個對象在表中占一行。
對象類型(xtype)。可以是下列對象類型中的一種：
C = CHECK 約束
D = 默認(rèn)值或 DEFAULT 約束
F = FOREIGN KEY 約束
L = 日志
FN = 標(biāo)量函數(shù)
IF = 內(nèi)嵌表函數(shù)
P = 存儲過程
PK = PRIMARY KEY 約束（類型是 K）
RF = 復(fù)制篩選存儲過程
S = 系統(tǒng)表
TF = 表函數(shù)
TR = 觸發(fā)器
U = 用戶表
UQ = UNIQUE 約束（類型是 K）
V = 視圖
X = 擴(kuò)展存儲過程
當(dāng)xtype='U' and status>0代表是用戶建立的表，對象名就是表名，對象ID就是表
---------------------------------------------------------------------------------------------------------------------------
syscolumns ：每個表和視圖中的每列在表中占一行，存儲過程中的每個參數(shù)在表
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 主數(shù)據(jù)庫 保存數(shù)據(jù)庫的文件
Select * From syscharsets 主數(shù)據(jù)庫 字符集與排序順序
Select * From sysconfigures 主數(shù)據(jù)庫 配置選項(xiàng)
Select * From syscurconfigs 主數(shù)據(jù)庫 當(dāng)前配置選項(xiàng)
Select * From sysdatabases 主數(shù)據(jù)庫 服務(wù)器中的數(shù)據(jù)庫
Select * From syslanguages 主數(shù)據(jù)庫 語言
Select * From syslogins 主數(shù)據(jù)庫 登陸帳號信息
Select * From sysoledbusers 主數(shù)據(jù)庫 鏈接服務(wù)器登陸信息
Select * From sysprocesses 主數(shù)據(jù)庫 進(jìn)程
Select * From sysremotelogins 主數(shù)據(jù)庫 遠(yuǎn)程登錄帳號
Select * From syscolumns 每個數(shù)據(jù)庫 列
Select * From sysconstrains 每個數(shù)據(jù)庫 限制
Select * From sysfilegroups 每個數(shù)據(jù)庫 文件組
Select * From sysfiles 每個數(shù)據(jù)庫 文件
Select * From sysforeignkeys 每個數(shù)據(jù)庫 外部關(guān)鍵字
Select * From sysindexes 每個數(shù)據(jù)庫 索引
Select * From sysmembers 每個數(shù)據(jù)庫 角色成員
Select * From sysobjects 每個數(shù)據(jù)庫 所有數(shù)據(jù)庫對象
Select * From syspermissions 每個數(shù)據(jù)庫 權(quán)限
Select * From systypes 每個數(shù)據(jù)庫 用戶定義數(shù)據(jù)類型
Select * From sysusers 每個數(shù)據(jù)庫 用戶
sql server系統(tǒng)表詳細(xì)說明
sysaltfiles 主數(shù)據(jù)庫 保存數(shù)據(jù)庫的文件
syscharsets 主數(shù)據(jù)庫字符集與排序順序
sysconfigures 主數(shù)據(jù)庫 配置選項(xiàng)
syscurconfigs 主數(shù)據(jù)庫當(dāng)前配置選項(xiàng)
sysdatabases 主數(shù)據(jù)庫服務(wù)器中的數(shù)據(jù)庫
syslanguages 主數(shù)據(jù)庫語言
syslogins 主數(shù)據(jù)庫 登陸帳號信息
sysoledbusers 主數(shù)據(jù)庫 鏈接服務(wù)器登陸信息
sysprocesses 主數(shù)據(jù)庫進(jìn)程
sysremotelogins主數(shù)據(jù)庫 遠(yuǎn)程登錄帳號
syscolumns 每個數(shù)據(jù)庫 列
sysconstrains 每個數(shù)據(jù)庫 限制
sysfilegroups 每個數(shù)據(jù)庫 文件組
sysfiles 每個數(shù)據(jù)庫 文件
sysforeignkeys 每個數(shù)據(jù)庫 外部關(guān)鍵字
sysindexs 每個數(shù)據(jù)庫 索引
sysmenbers 每個數(shù)據(jù)庫角色成員
sysobjects 每個數(shù)據(jù)庫所有數(shù)據(jù)庫對象
syspermissions 每個數(shù)據(jù)庫 權(quán)限
systypes 每個數(shù)據(jù)庫 用戶定義數(shù)據(jù)類型
sysusers 每個數(shù)據(jù)庫 用戶
---------------------------------------------------------------------------------------------------------------------------
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,
標(biāo)識=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,
占用字節(jié)數(shù)=a.length,
長度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小數(shù)位數(shù)=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允許空=case when a.isnullable=1 then '√'else '' end,
默認(rèn)值=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
顯示每個表當(dāng)前有多少行
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' -- 要查詢的表名 如果不加默認(rèn)是該庫底下的所有表
---------------------------------------------------------------------------------------------------------------------------
4.如何得到服務(wù)器的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 服務(wù)器名=@@servername,IP地址=stuff(left(re,charindex(']',re)-1),1,charindex('[',re),'')
from #ip
where id=2
drop table #ip
---------------------------------------------------------------------------------------------------------------------------
9.如何知道哪些觸發(fā)器被禁用?
--將trigger 在sysobjects 表中 status字段的值轉(zhuǎn)換為二進(jìn)制的，第12位為1則表示禁止，為0表示允許
select 表名=object_name(parent_obj),觸發(fā)器名=name
,狀態(tài)=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
---------------------------------------------------------------------------------------------------------------------------
作業(yè)
SELECT 作業(yè)的名稱 = name,
對作業(yè)的說明 = description,
計(jì)劃運(yùn)行作業(yè)的下一個日期 = (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),
計(jì)劃運(yùn)行作業(yè)的時間 = (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),
作業(yè)的執(zhí)行狀態(tài) = 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 '正在進(jìn)行中'
END,
作業(yè)或步驟開始執(zhí)行的日期 = (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),
作業(yè)或步驟開始的時間 = (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),
執(zhí)行作業(yè)或步驟所花費(fèi)的時間 = (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 中的作業(yè)信息查詢
作業(yè)信息存儲在MSDB中.可以運(yùn)行以下存儲過程,監(jiān)視作業(yè)的執(zhí)行情況.
可以參考如下SQL:
SELECT 作業(yè)的名稱 = name,
對作業(yè)的說明 = description,
計(jì)劃運(yùn)行作業(yè)的下一個日期 = (SELECT next_run_date
FROM sysjobschedules
WHERE job_id = sysjobs.job_id),
計(jì)劃運(yùn)行作業(yè)的時間 = (SELECT next_run_time
FROM sysjobschedules
WHERE job_id = sysjobs.job_id),
作業(yè)的執(zhí)行狀態(tài) = 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 '正在進(jìn)行中'
END,
作業(yè)或步驟開始執(zhí)行的日期 = (SELECT top 1 run_date
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
作業(yè)或步驟開始的時間 = (SELECT top 1 run_time
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC),
執(zhí)行作業(yè)或步驟所花費(fèi)的時間 = (SELECT top 1 run_duration
FROM sysjobhistory
WHERE job_id = sysjobs.job_id
ORDER BY instance_id DESC)
FROM sysjobs
如果監(jiān)視備份作業(yè)的執(zhí)行計(jì)劃,可以在描述中輸入:"備份" 等字樣,查詢時候可以用description 描述信息過濾.如下:
(注意:對某些字段盡心了翻譯和轉(zhuǎn)換)
SELECT 作業(yè)的名稱 = name,
對作業(yè)的說明 = description,
計(jì)劃運(yùn)行作業(yè)的下一個日期 = (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),
計(jì)劃運(yùn)行作業(yè)的時間 = (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),
作業(yè)的執(zhí)行狀態(tài) = 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 '正在進(jìn)行中'
END,
作業(yè)或步驟開始執(zhí)行的日期 = (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),
作業(yè)或步驟開始的時間 = (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),
執(zhí)行作業(yè)或步驟所花費(fèi)的時間 = (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 '%備份%'
這樣就可以監(jiān)視到備份數(shù)據(jù)庫計(jì)劃的執(zhí)行,前臺頁面就可以用Ajax來無刷新監(jiān)視備份情況了.
測試后,SQL server 2005 也使用.
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
