mssql常用系统级查询
1、查询数据库中的所有表名称：
SELECT name FROM SysObjects Where XType='U' ORDER BY Name
2、查询数据库中指定表的表结构：
--快速查看表结构
SELECT CASE WHEN col.colorder = 1 THEN obj.name
ELSE ''
END AS 表名,
col.colorder AS 序号 ,
col.name AS 列名 ,
ISNULL(ep., '') AS 列说明 ,
t.name AS 数据类型 ,
col.length AS 长度 ,
ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数 ,
CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN'1'
ELSE ''
END AS 标识 ,
CASE WHEN EXISTS ( SELECT 1
FROM dbo.sysindexes si
INNER JOIN dbo.sysindexkeys sik ON si.id =sik.id
AND si.indid = sik.indid
INNER JOIN dbo.syscolumns sc ON sc.id =sik.id
AND sc.colid = sik.colid
INNER JOIN dbo.sysobjects so ON so.name =si.name
AND so.xtype = 'PK'
WHERE sc.id = col.id
AND sc.colid = col.colid ) THEN '1'
ELSE ''
END AS 主键 ,
CASE WHEN col.isnullable = 1 THEN '1'
ELSE ''
END AS 允许空 ,
ISNULL(comm.text, '') AS 默认值
FROM dbo.syscolumns col
LEFTJOIN dbo.systypes t ON col.xtype = t.xusertype
inner JOIN dbo.sysobjects obj ON col.id = obj.id
AND obj.xtype = 'U'
AND obj.status >= 0
LEFTJOIN dbo.syscomments comm ON col.cdefault = comm.id
LEFTJOIN sys.extended_properties ep ON col.id =ep.major_id
AND col.colid = ep.minor_id
AND ep.name = 'MS_Description'
LEFTJOIN sys.extended_properties epTwo ON obj.id =epTwo.major_id
AND epTwo.minor_id = 0
AND epTwo.name = 'MS_Description'
WHEREobj.name = 'PkAutoInc'--表名
ORDER BY col.colorder ;
/*1.查询SQLSERVER中的所有数据库*/
use master
go
select * fromsysdatabases where dbid>4/*where条件用来去掉系统数据库*/
go
/*2.查询SQLSERVER中的所有数据库(存储过程)*/
exec sp_helpdb
/*3.查询指定数据库中的表*/
use 数据库名
go
select * fromsysobjects where xtype='u'/*where条件用来去掉系统表*/
go
[删除字段]
alter table tb_commonLog --表名
drop
column l_test,--去掉字段1
columnl_test2, --去掉字段2
constraintpk_name --去掉主键
go
use data;
SELECT tb2.name AS tableName,tb1.name AStriggerName FROM Sysobjects tb1
JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.idWHERE tb1.type='TR'; --查触发器
SELECT name FROMsys.all_objectsWHEREtype_desc = N'SQL_STORED_PROCEDURE' and substring(name,1,3) notin('sp_','xp_'); --查存储过程
exec sp_helptext '客户应收账表因其修改'; -- 查内容
select * from sys.procedures where substring(name,1,3) not in('sp_');--查存储过程
SELECT * FROMsys.all_objects wheretype='FN' and name not like 'fn_%' ;--查函数
查库所有表
use data
go
select * from sys.tables
--查询数据库所有连接进程
select * from (SELECT * FROMmaster.dbo.sysprocesses WHERE dbid IN
(
SELECT dbid FROMmaster.dbo.sysdatabases
WHERE NAME='DATA' --指定的数据库名字
)) AA inner join
(SELECT S.SPID, S.LOGINAME, S.LOGIN_TIME,S.LAST_BATCH, C.CLIENT_NET_ADDRESS
FROM sys.sysprocesses S,sys.dm_exec_connections C
WHERE S.spid = C.SESSION_ID)BB on AA.spid=BB.spid
--判断游标是否存在
if exists(select * fromMASTER.dbo.syscursors where cursor_name='youbiao')
begin
deallocate youbiao
end
;
--查看死锁
SELECT ,
AS '正在阻塞其他会话的会话ID' ,
DB_NAME ( ) AS '数据库名称' ,
,
,
AS '开始时间' ,
AS '状态' ,
AS '命令' ,
dest. AS '语句' ,
AS '物理读次数' ,
AS '写次数' ,
AS '逻辑读次数' ,
AS '返回结果行数' ,
AS '等待资源类型' ,
AS '等待时间' ,
AS '等待的资源'
FROM sys. AS der
CROSS APPLY sys. ( der. ) AS dest
WHERE 1 = 1
ORDER BY DESC
----------查看作业
--SELECT * FROM sysjobs_view
--SELECT * FROM sysjobservers
--SELECT * FROM sysjobsteps
SELECT Jobs.name AS '作业名称' ,
CASE Jobs.enabled
WHEN 1 THEN '启用'
WHEN 0 THEN '未启用'
END AS '是否启用',
Jobs.description AS '描述信息',
Jobs.date_created AS '创建日期',
Jobs.date_modified AS '修改日期',
Steps.command AS '作业内容',
CASE Schedules.freq_type
WHEN 1 THEN '只运行一次'
WHEN 4 THEN '每天'
WHEN 8 THEN '每周'
WHEN 16 THEN '每月'
WHEN 32 THEN '每月'
WHEN 64 THEN '在代理服务启动时运行'
WHEN 128 THEN '在服务器空闲时运行'
END AS '频率',
CASE Schedules.freq_type
WHEN 1 THEN '只运行一次'
WHEN 4 THEN '每' + CAST(Schedules.freq_interval AS VARCHAR (10)) + '天'
WHEN 8 THEN '每周' + ( CASE freq_interval
WHEN 1 THEN '星期日'
WHEN 2 THEN '星期一'
WHEN 4 THEN '星期二'
WHEN 8 THEN '星期三'
WHEN 16 THEN '星期四'
WHEN 32 THEN '星期五'
WHEN 64 THEN '星期六'
END )
WHEN 16 THEN '每月' + CAST(Schedules.freq_interval AS VARCHAR (10)) + '日'
WHEN 32 THEN '每月' + ( CASE freq_interval
WHEN 1 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '个星期日'
WHEN 2 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '星期一'
WHEN 3 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '星期二'
WHEN 4 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '星期三'
WHEN 5 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '星期四'
WHEN 6 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '星期五'
WHEN 7 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '星期六'
WHEN 8 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一'
END ) + '天'
WHEN 9 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '工作日'
WHEN 10 THEN ( CASE freq_relative_interval
WHEN 1 THEN '第一'
WHEN 2 THEN '第二'
WHEN 4 THEN '第三'
WHEN 8 THEN '第四'
WHEN 16 THEN '最后一个'
END ) + '休息日'
END )
END AS '间隔天数',
CASE freq_subday_type
WHEN 1 THEN '指定时间'
WHEN 2 THEN '每隔' + CAST(freq_subday_interval AS VARCHAR (10)) + '秒'
WHEN 4 THEN '每隔' + CAST(freq_subday_interval AS VARCHAR (10)) + '分钟'
WHEN 8 THEN '每隔' + CAST(freq_subday_interval AS VARCHAR (10)) + '小时'
END AS '间隔时间',
DATEADD (
HOUR ,
( active_start_time / 10000 ) % 100,
DATEADD ( MINUTE, ( active_start_time / 100 ) % 100, DATEADD ( SECOND, ( active_start_time / 1 ) % 100, CAST('00:00:00' AS TIME (0))))) AS '开始执行时间',
DATEADD (
HOUR ,
( active_end_time / 10000 ) % 100,
DATEADD ( MINUTE, ( active_end_time / 100 ) % 100, DATEADD ( SECOND, ( active_end_time / 1 ) % 100, CAST('00:00:00' AS TIME (0))))) AS '截止时间',
CONVERT ( DATE, CAST(active_start_date AS VARCHAR (20)), 23 ) AS '开始日期',
CONVERT ( DATE, CAST(active_end_date AS VARCHAR (20)), 23 ) AS '截止日期'
FROM sysjobs Jobs
JOIN sysjobschedules JobsSchedules ON Jobs.job_id = JobsSchedules.job_id
JOIN sysschedules Schedules ON JobsSchedules.schedule_id = Schedules.schedule_id
JOIN sysjobsteps Steps ON Jobs.job_id = Steps.job_id
---------------------------------------------------------真秀库神操作
USE
GO
/****** Object:StoredProcedure . Script Date: 09/06/2020 15:41:14 ******/
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE .
(
@data varchar(250)=''
)
AS
BEGIN
DECLARE @sql VARCHAR(500)=''
DECLARE @com VARCHAR(250)='', @obj VARCHAR(250)='';
select @com=@data +'.dbo.syscomments a,',@obj=@data + '.dbo.sysobjects b'
SET @sql=N'use ' + @data +N';'
SET @sql= @sql +N'
select b.name AS 名称,a.text AS 内容,
( CASE b.xtype WHEN ''c'' THEN ''check约束''
WHEN ''D'' THEN ''字段默认值''
WHEN ''FN'' THEN ''标量值函数''
WHEN ''IF'' THEN ''表值函数''
WHEN ''P'' THEN ''存储过程''
WHEN ''TR'' THEN ''触发器''
WHEN ''V'' THEN ''视图''
ELSE b.xtype
END )AS 类型,
ISNULL(OBJECT_NAME(b.parent_obj),''非表项目'') AS 基表,b.crdate as 创建时间 from
'
set @sql=@sql+@com +@obj + N' where a.id=b.id ORDER BY b.xtype,b.parent_obj,b.crdate desc'
PRINT @sql
exec(@sql)
END
GO
DECLARE @tabelview TABLE( 名称 VARCHAR(250),内容 VARCHAR(max),类型 VARCHAR(250),基表 VARCHAR(250),创建时间 DATETIME)
INSERT @tabelview ( 名称, 内容, 类型, 基表, 创建时间 ) EXEC data.dbo.dataviews N'data'
SELECT * FROM @tabelview
DECLARE @cretxt VARCHAR(max)
SELECT @cretxt=内容 FROM @tabelview WHERE 类型='存储过程' AND SUBSTRING(名称,1,3)NOT IN('sp_','xp_')
PRINT @cretxt
页:
[1]