常用sql语句

因为常用，先记录下。

 

--总是记不清参数顺序的replace，substring，charindex

replace("待搜索的","要查找的","用来替换的")

substring("待截取字符串",start,length)--start从1开始

substring("待截取字符串",start)--返回从start位置开始的后边所有字符
charindex("目的字符或字符串","待搜寻字符串")--返回字符或字符串在另一字符串中的起始位置

 

--checksum加newid生成20位主键值
select  left((convert(varchar(100),getdate(),112)+cast(abs(checksum(newid())) as nvarchar(max))+'0000000000000000'),20)

 

--@@记一次日志收缩不成功之后的相关处理sql@@

--查看当前数据库的reuse wait状态，可知晓日志无法收缩的原因
select name,log_reuse_wait_desc from sys.databases where name='dbname'

--显示当前处于打开状态的活动事务
dbcc opentran ('dbname') 
checkpoint --执行检查点  执行检查点之后便于文件收缩

--删除当前数据库中所有复制对象  
exec sp_removedbreplication dbname 

--假装备份日志
 BACKUP LOG dbname TO DISK='NUL:'

--连续2次收缩文件
DBCC SHRINKFILE (2, EMPTYFILE);--2为fileId，可从sysfiles查询
DBCC SHRINKFILE (2, 1); 

 

--查看ag延迟
SELECT  ar.replica_server_name AS [副本名称] ,
        DB_NAME(dbr.database_id) AS [数据库名称] ,
        ISNULL(CASE dbr.redo_rate WHEN 0 THEN -1
               ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rate
               END, -1) AS [Redo延迟(秒)] ,
        ISNULL(CASE dbr.log_send_rate WHEN 0 THEN -1
               ELSE CAST(dbr.log_send_queue_size AS FLOAT) / dbr.log_send_rate
               END, -1) AS [Log传送延迟(秒)] ,
        dbr.redo_queue_size AS [Redo等待队列(KB)] ,
        dbr.redo_rate AS [Redo速率(KB/S)] ,
        dbr.log_send_queue_size AS [Log传送等待队列(KB)] ,
        dbr.log_send_rate AS [Log传送速率(KB\S)]
FROM    [master].sys.availability_replicas AS AR
INNER JOIN [master].sys.dm_hadr_database_replica_states AS dbr ON ar.replica_id = dbr.replica_id
WHERE   dbr.redo_queue_size IS NOT NULL

 

--找到ag中延迟较高的数据库实例 清除缓存页面数据
use master
dbcc dropcleanbuffers

 

        --删除约束 DF为约束名称前缀
	declare @constraitName nvarchar(100)
	select @constraitName=b.name from syscolumns a,sysobjects b where a.id=object_id(@tableName) 
	and b.id=a.cdefault and a.name='FlowEmps' and b.name like 'DF%'
	--select @constraitName

	if exists (select * from  sys.check_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
	or exists(select * from  sys.default_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
	or exists(select * from  sys.edge_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
    or exists(select * from  sys.key_constraints where object_id =
	object_id(@constraitName) and parent_object_id = object_id(@tableName))
	begin
	set @sql='alter table '+@tableName+' drop constraint '+@constraitName
	--select @sql
	exec(@sql)
	end

　　

--统计字符ch在字符串中出现的次数
select LEN(columnname)-LEN(REPLACE(columnname,N'ch',N'')) from tableName

--decimal(a,b),a指定小数点左边和右边可以存储的十进制数字的最大个数，最大精度38。
--b指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从 0 到 a之间的值。默认小数位数是 0.
--可以通过decimal来保留较多小数位数的浮点数转换至字符串时的位数
cast(Convert(decimal(18,6),a.Float_X) as varchar)

--判断字段值不是由纯数字构成

select * from 表名 where PATINDEX('%[^0-9]%',列名)>0

--分组后 row_number

select ROW_NUMBER() over (partition by 列名1,列名2 order by 列名3) as row_num from 表名

--查找字段值带%的记录

select * from 表名 where 列名 like '%[%]%'

--attatch 同名数据库文件

use [master]
create database [123] on
(FileName=N'D:\Data.MDF'),
(FileName=N'D:\Log.LDF')
for attach
go