sql语句搜集

1.重建数据库日志
    backup log jbERP with no_log
    dbcc shrinkdatabase('jbERP',0,notruncate)
2.查找重复记录
    select   bm   from   sell   group   by   bm   having   count(*)>1 
3.数据库备份到远程电脑，通过sql代理--作业
    declare @filename nvarchar(100)
    set @filename='//192.168.0.2/YiFeiBACKUP/data'+convert(char(10),getdate(),112)
    print @filename
    backup database jxERP to disk=@filename  with noinit,nounload,
    name=N'备份',noskip,stats=10,noformat
4.表中一字段ID，数值长度大于等于10，，寻找前八个数值相同的记录。。。。。
SELECT *
FROM INVMB AS t
WHERE EXISTS(SELECT *
             FROM INVMB
             WHERE LEFT(MB001,8)=LEFT(t.MB001,8)
                   AND MB001!=t.MB001) order by MB001 desc
5.字符串拆分
declare @s varchar(1000)
set @s='1,23,a'
declare @sql varchar(8000)
set @sql='select com='''+replace(@s,',',''' union all select ''')
+''''
exec(@sql)
6.更新a表的MB006等于b表的nam字段
  update a
  set MB006=b.NAM
  from
  INVMB a
  join
  ALEX_INV b on a.MB001=b.NUM