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.Database Backup到遠程電腦，通過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