常用sql语句

来源：互联网发布：陌陌推广软件编辑：程序博客网时间：2024/04/25 23:12

查找不能转化为数字的列

select ISNUMERIC(id), id from dbo.Archives_CM group by id having ISNUMERIC(id)!=1

select * from dbo.Archives_CM where (right(villagecode,4)+homecode) like '%[^0-9]%'

除去左右空格

select rtrim(ltrim( ' a bc d ef g '))

批量设置户主

update Archives_cy set humancode ='01' from Archives_cy t where humancode=(select top 1 humancode from Archives_cy where id = t.id and cmcode ='3707860001000001' order by humancode)

查找字段中的某个字符函数：

CHARINDEX(查找字符串,字段名)

获取准确的生日，如果生日错误返回默认时间：

select top 2 CAST(left(Year(GetDate()),4)as int)- cast(left((case when isdate(Birthday)=1 then Birthday else '1900-01-01 00:00:00' end),4) as int) as age from dbo.Archives_CM

修改数据库名：

EXEC sp_renamedb 'cyhzyl_2011','cyhzyl_2012'

解除死锁：

EXEC sp_configure 'max degree of parallelism', 1
RECONFIGURE WITH OVERRIDE
GO

数据库权限设置：

GRANT SELECT , UPDATE , INSERT ,DELETE ON dbo.mz_jk_fy TO jk

sql server 隔离级别

SQL Server的四种隔离级别知识点整理及设置

SET TRANSACTION ISOLATION LEVEL
{

READ UNCOMMITTED

| READ COMMITTED

| REPEATABLE READ

| SERIALIZABLE

}

查找某个存储过程中是否含有某个字段

select b.name
from cyhzyl_2012.dbo.syscomments a,cyhzyl_2012.dbo.sysobjects b where a.id=b.id and b.xtype='p' and a.text like '%isnull(sum(ReimburseCash),0)%'

查找某个字段存在于那个表

select tab.name table_name, col.name column_name
from sysobjects tab
left join syscolumns col on tab.id = col.id and tab.xtype = 'U'
where col.name like '%字段名%'
order by 1,2

删除重复记录：

delete from sh where id in (
select max(id) from sh group by yybm having count(yybm)>1)

清空日志：
记录一下清空日志的命令：
DUMP TRANSACTION dbname WITH NO_LOG
截断事务日志命令:
BACKUP LOG dbname WITH NO_LOG
收缩日志操作：
右键点数据库－属性－所有任务－收缩数据库－文件－选择文件－确定。