Select * From master.dbo.Sysdatabasesこのデータベース情報のクエリー
転載先:https://blog.csdn.net/Aug0st/article/details/42241255
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 .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 .