查找不能转化为数字的列
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的四种隔离级别知识点整理及设置
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
收缩日志操作:
右键点数据库-属性-所有任务-收缩数据库-文件-选择文件-确定。