- 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.根据一个表的条件更新另外一个表
update tusers set uprovince=p_province.provincename
from tusers, p_province where uprovince=convert(varchar(50),p_province.province) - 3.根据一个加约束(默认值)
alter table tblmsg add constraint c_tblmsg_sex default getdate() for msgdate
删除
alter table tblmsg drop constraint c_tblmsg_sex -
4.去除HTML标签--SQL写法
create FUNCTION StripAllTags
(
@input VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
declare
@Result varchar(8000),
@start int,
@end int,
@len intset @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)
endRETURN replace(@Result,' ','')
ENDselect dbo.StripAllTags('<a href="user/loginout.aspx" id="TopLogin1_loginout">退出</a>')
-
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 +'%'' '
endif(@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)
endUPGetNewsAll 'jack','news'
转载于:https://www.cnblogs.com/jackcxd/archive/2009/11/20/1606670.html