【sqlServer】常用语句整理

---------------------------------------------------------
--1：查询数据库中包含某字段的所有表名
--objName表名 根据xtpe='u'这个是查询表名
use BigData_ODS
GO
select object_name(id) objName,Name as 列名
from syscolumns
where (name like'字段名') and id in(select id from sysobjects where xtype='u')

order by objname
------------------------------------------------------------
--2：查看所有表对应的数据量
SELECT a.name AS 表名, MAX(b.rows) AS 记录条数
FROM sys.sysobjects AS a INNER JOIN
sys.sysindexes AS b ON a.id = b.id
WHERE (a.xtype = 'u')
GROUP BY a.name
ORDER BY 记录条数 DESC
------------------------------------------------------------
--3：查看数据库的总数据量
SELECT SUM(记录条数) AS 总记录数
FROM (SELECT TOP (10000) a.name AS 表名, MAX(b.rows) AS 记录条数
FROM sys.sysobjects AS a INNER JOIN
sys.sysindexes AS b ON a.id = b.id
WHERE (a.xtype = 'u')
GROUP BY a.name
ORDER BY 记录条数 DESC) AS t1
------------------------------------------------------------
--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 = 扩展存储过程

--type可以是下列值之一：
-- C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束
-- FN = 标量函数 IF = 内嵌表函数 K = PRIMARY KEY 或 UNIQUE 约束
-- L = 日志 P = 存储过程 R = 规则 RF = 复制筛选存储过程
--S = 系统表 TF = 表函数 TR = 触发器 U = 用户表 V = 视图 X = 扩展存储过程
------------------------------------------------------------

--4：时间查询--
--获取今天的时间 0点0时0分
select DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))

--本月第一天 返回格式 yyyy-MM-dd
select Convert(char(10),dateadd(dd,-day(getdate())+1,getdate()) ,126)

--当前时间两小时前
--这个小时不支持0.5这种格式 可以用SELECT DATEADD(MINUTE,-30,GETDATE()) 替代
SELECT DATEADD(hour,-1, getdate())

--a. 本月的第一天 返回格式yyyy-MM-dd HH:mm:ss
select dateadd(mm, datediff(mm,0,getdate()), 0) AS 本月的第一天
--b. 本月的最后一天
select dateadd(ms,-3,dateadd(mm, datediff(mm,0,getdate())+1, 0)) AS 本月的最后一天
--c. 上个月的最后一天
select dateadd(ms,-3,dateadd(mm, datediff(mm,0,getdate()), 0)) AS 上个月的最后一天
--d. 本周的星期一
select dateadd(wk, datediff(wk,0,getdate()), 0) AS 本周的星期一
--e. 本季度的第一天
select dateadd(qq, datediff(qq,0,getdate()), 0) AS 本季度的第一天
--f. 本季度的最后一天
select DATEADD(ms,-3,dateadd(qq, datediff(qq,0,getdate())+1, 0)) AS 本季度的最后一天
--g. 本月的第一个星期一
select dateadd(wk, datediff(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())), 0) AS 本月的第一个星期一
--h. 本年的第一天
select DATEADD(yy, DATEDIFF(yy,0,GETDATE()), 0) AS 本年的第一天
SELECT DATEDIFF(yy,0,GETDATE())
--i. 本年的最后一天
select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate())+1, 0)) AS 本年的最后一天
--f. 去年的最后一天
select dateadd(ms,-3,dateadd(yy, datediff(yy,0,getdate()), 0)) AS 去年的最后一天

--计算哪一天是本周的星期一

Select   DATEADD(wk,   DATEDIFF(wk,0,getdate()),   0)  

 

 

--5：查询数据库的连接情况：（数据库SQL可直接执行）
SELECT [dec].client_net_address,
[des].[program_name],
[des].[host_name],
Count([dec].session_id) AS connection_count
FROM sys.dm_exec_sessions AS [des]
INNER JOIN sys.dm_exec_connections AS [dec]
ON [des].session_id = [dec].session_id
GROUP BY [dec].client_net_address,
[des].[program_name],
[des].[host_name]
ORDER BY [des].[program_name],
[dec].[client_net_address]

-:6：查看锁活动情况
select * from sys.dm_tran_locks

--8查看事务活动情况--要有权限
dbcc opentran

-:7查看事务当前隔离级别
DBCC USEROPTIONS

9：事务与锁
https://www.cnblogs.com/knowledgesea/p/3714417.html

以下数据来源地址：
https://mp.weixin.qq.com/s/4KxLdDw3phWvRJimBGpTZg

10：查看数据库缓存的SQL

use master
declare @dbid int
Select @dbid = dbid from sysdatabases
where name = 'SQL_ROAD'--修改成数据库的名称

select
dbid,UseCounts ,RefCounts,CacheObjtype,ObjType,
DB_Name(dbid) as DatabaseName,SQL
from syscacheobjects
where dbid=@dbid
order by dbid,useCounts desc,objtype

11：常用系统检测脚本

--查看内存状态
dbcc memorystatus

--查看哪个引起的阻塞，blk
EXEC sp_who active

--查看锁住了那个资源id，objid
EXEC sp_lock

还有如何查看查询分析器的SPID，可以在查询分析器的状态栏看到，比如sa(57),这就表示当前查询分析器SPID为57,这样在使用profile的时候就可以指定当前窗体进行监控。状态栏在查询窗口的右下角。

 

12：获取脚本执行时间

declare @timediff datetime
select @timediff=getdate()
select * from Suppliers（查询语句）
print '耗时:'+ convert(varchar(10),datediff(ms,@timediff,getdate()))

 13：连接远程数据库

select *  from openrowset(
'SQLOLEDB',
'server=192.168.0.1;uid=sa;pwd=password',
'SELECT * FROM dbo.test')

--方法2：
select *  from openrowset(
'SQLOLEDB',
'192.168.0.1';
'sa';
'password',
'SELECT * FROM dbo.test')

14：查询一个表内相同记录 HAVING

表数据结构

 

 

一个Id可以区分的时候

用title可以区分时：

select * from HR.Employees
where title in (
select title from HR.Employees
group by title
having count(1)>1)

多个id才可以区分时：

用title和titleofcourtesy才能区分相同记录

select * from HR.Employees
where title+titleofcourtesy in
(select title+titleofcourtesy
from HR.Employees
group by title,titleofcourtesy
having count(1)>1)

 15：事务

有四个命令用于控制事务：

• COMMIT：提交更改；

• ROLLBACK：回滚更改；

• SAVEPOINT：在事务内部创建一系列可以 ROLLBACK 的还原点；

• SET TRANSACTION：命名事务

 SAVEPOINT 是事务中的一个状态点，使得我们可以将事务回滚至特定的点，而不是将整个事务都撤销。

 SAVEPOINT 命令的记录如下所示：

SAVEPOINT SAVEPOINT_NAME;

   回滚至某一保存点的语法如下所示：

ROLLBACK TO SAVEPOINT_NAME;