sql笔记

1. 1.SQLServer修改表所有者：
   批量修改：
   EXEC sp_MSforeachtable 'exec sp_changeobjectowner ''?'',''dbo'' '
   单个修改：
   exec sp_changeobjectowner '要改的表名','dbo'
   只有所有者才能更改表的所有者
   有很服务器:   消息   15001，级别   16，状态   1，过程   sp_changeobjectowner，行   38  
     对象   'user'   不存在或不是对此操作有效的对象。  
   多表是导入的 一些所有者的密码都不记得就会产生
   觉得是不是没有原来的所有者 我加了一下 嘿嘿 正常！
   exec sp_changeobjectowner '表所有者.要改的表名','dbo'
   就不需表的所有者才能修改了
2. 2.根据一个表的条件更新另外一个表
   update tusers set uprovince=p_province.provincename
   from tusers, p_province  where uprovince=convert(varchar(50),p_province.province)　　
3. 3.根据一个加约束(默认值)
   alter table tblmsg add constraint c_tblmsg_sex default getdate() for msgdate
   删除
   alter table tblmsg drop constraint c_tblmsg_sex
   

4. 4.去除HTML标签--SQL写法
   create FUNCTION StripAllTags
   (
       @input    VARCHAR(8000)
   )
   RETURNS VARCHAR(8000)
   AS
   BEGIN
       declare
       @Result varchar(8000),
       @start int,
       @end int,
       @len int

       set @input = @input+'<>'
       set @Result = ''
       set @len=len(@input)
       set @start = charindex('<',@input,1)
       set @end = charindex('>',@input,@start)
       while(@start<@end)
           begin       
               if(@start<>1)
                 set @Result = @Result + substring(@input,1,@start-1)
               set @len = @len - @end
               set @input = substring(@input,@end+1,@len)
               set @start = charindex('<',@input,1)
               set @end = charindex('>',@input,@start)
           end

       RETURN replace(@Result,'&nbsp;','')   
   END

   select  dbo.StripAllTags('<a href="user/loginout.aspx" id="TopLogin1_loginout">退出</a>')

5.  5.全站搜索

   
   alter procedure UPGetNewsAll (@sqlWhere varchar(100),@type varchar(20))
   as
   begin
   declare @sql varchar(6000)
   set @sql='select * from
   ( select nid id, ntitle title, ncontent content ,ndate ndate, ''信息来源'' as fromarea ,''news/details.aspx?nid'' as linkurl from  vCommonNews where 1=2 '
   if(@type='' or @type='news')
   begin
   set @sql=@sql+'union all   select nid , ntitle, ncontent,ndate, ''特约稿件''  ,''news/details.aspx?nid'' from  vCommonNews  where ntitle like ''%'+@sqlWhere +'%'' or  ncontent like ''%'+@sqlWhere +'%'' or nkey like ''%'+@sqlWhere +'%''  '
   set @sql=@sql+'union all   select uid,urealname,udescript,uregdate,''名师风采'' ,''teacher/teacherinfo.aspx?uid'' from tusers  where utype=1  and unike like ''%'+@sqlWhere +'%'' or  uschool like ''%'+@sqlWhere +'%'' or udescript like ''%'+@sqlWhere +'%''  '
   set @sql=@sql+'union all   select id,name,description,null,''学习测评'' ,''test/freeKnowledge.aspx?kid'' from tbknowledge  where  name like ''%'+@sqlWhere +'%'' or  description like ''%'+@sqlWhere +'%''  '
   set @sql=@sql+'union all   select aid,title,content,postdate,''精灵习作园'' ,''blog/articleDetails.aspx?aid'' from vblogArticle  where title like ''%'+@sqlWhere +'%'' or  content like ''%'+@sqlWhere +'%'' or tag like ''%'+@sqlWhere +'%''  '
   end

   if(@type='' or @type='ask')
   set @sql=@sql+'union all   select aid , atitle, acontent,adate, ''在线答疑''  ,''ask/details.aspx?aid'' from  tbask  where atitle like ''%'+@sqlWhere +'%'' or  acontent like ''%'+@sqlWhere +'%'' or akey like ''%'+@sqlWhere +'%'' or agrade like ''%'+@sqlWhere +'%'' or asubject like ''%'+@sqlWhere +'%''  '

   if(@type='' or @type='exam')
   set @sql=@sql+'union all   select newsid , ntitle, ncontent,ndate, ''试题中心''  ,''exam/details.aspx?nid'' from  vResExamVideo  where classid in
   (select cid from resclass where  cparentid=2 or   cparentid in(select cid from resclass where cparentid=2) )
   and ( ntitle like ''%'+@sqlWhere +'%'' or  ncontent like ''%'+@sqlWhere +'%'' or ngrade like ''%'+@sqlWhere +'%'' or nsubject like ''%'+@sqlWhere +'%'' or nfromarea like ''%'+@sqlWhere +'%''  or cname like ''%'+@sqlWhere +'%'')  '

   
   if(@type='' or @type='res')
   set @sql=@sql+'union all   select newsid , ntitle, ncontent,ndate, ''学习资源''  ,''rescore/details.aspx?nid'' from  vResExamVideo  where classid in
   (select cid from resclass where  cparentid=1 or   cparentid in(select cid from resclass where cparentid=1) )
   and ( ntitle like ''%'+@sqlWhere +'%'' or  ncontent like ''%'+@sqlWhere +'%'' or ngrade like ''%'+@sqlWhere +'%'' or nsubject like ''%'+@sqlWhere +'%'' or nfromarea like ''%'+@sqlWhere +'%''  or cname like ''%'+@sqlWhere +'%'')  '

   if(@type='' or @type='video')
   set @sql=@sql+'union all   select newsid , ntitle, ncontent,ndate, ''视频课堂''  ,''video/details.aspx?nid'' from  vResExamVideo  where classid in
   (select cid from resclass where  cparentid=3 or   cparentid in(select cid from resclass where cparentid=3) )
   and ( ntitle like ''%'+@sqlWhere +'%'' or  ncontent like ''%'+@sqlWhere +'%'' or ngrade like ''%'+@sqlWhere +'%'' or nsubject like ''%'+@sqlWhere +'%'' or nkey like ''%'+@sqlWhere +'%''  or ntag like ''%'+@sqlWhere +'%'')  '

   
   if(@type='' or @type='product')
   set @sql=@sql+'union all   select pid , pname, pcontent,pdate, ''亚文商城''  ,''BookShop/Books.aspx?bid'' from  mproduct  where  pname like ''%'+@sqlWhere +'%'' or  press like ''%'+@sqlWhere +'%'' or author like ''%'+@sqlWhere +'%'' or pcontent like ''%'+@sqlWhere +'%''  '

   if(@type='' or @type='bbs')
   set @sql=@sql+'union all   select aid , title, content,postdate, ''翼展论坛''  ,''bbs/detail.aspx?aid'' from  vArticle  where  title like ''%'+@sqlWhere +'%'' or  content like ''%'+@sqlWhere +'%''  '

    

   
   set @sql=@sql+') as tbtemp'

   
   --select @sql
   --select len(@sql)
   exec (@sql)
   end

   UPGetNewsAll 'jack','news'