/*我是用这样方法搞定视图刷新问题, 有没有类似的办法*/ ALTER proc sp_RefreshAllViews as declare @viewName varchar(100), @sql varchar(8000) declare cur cursor for select Name from sysobjects where type = 'v' order by name open cur fetch next from cur into @viewName while @@fetch_status = 0 begin if @viewName not in ('syssegments', 'sysconstraints') begin set @sql = 'sp_refreshview '''+@viewName+'''' print('refreshing '+@viewName) exec(@sql) end fetch next from cur into @viewName end close cur deallocate cur
select [存储过程名]=object_name(ID),* from syscomments where text like '%表名%' and id in (select id from sysobjects where type='P')
想到办法了create proc sp_refreshProc @objectName varchar(255) as declare @text varchar(8000) select @text = text from syscomments where id = object_id(@objectName)
存储过程是根据表结构开发的,想不改过表结构后让存储过程自动刷新好像不太可能。
------
就是楼上这位兄弟的意思,
视图也是根据表结构开发的,它可通过sp_refreshview来刷新
当存储过程时就什么不知道哪条
--------------
楼主这句话,硬是没有念通。。其他的还勉强可以猜出意思。。这句就
========
呵呵,近几年来,打字就是那不顺手,-_-!当存储过程时就什么不知道哪条
==>
当存储过程很多时就不知道要改哪条,只有重新一条条去修过去,判断有没有错
select [存储过程名]=object_name(ID),* from syscomments where text like '%表名%'
----------
楼主这样检测可用;;
sysobject\syscolumns关于字符指定赋值测
有没有类似的办法*/
ALTER proc sp_RefreshAllViews
as
declare @viewName varchar(100),
@sql varchar(8000) declare cur cursor for
select Name from sysobjects where type = 'v' order by name
open cur fetch next from cur into @viewName
while @@fetch_status = 0
begin
if @viewName not in ('syssegments', 'sysconstraints')
begin
set @sql = 'sp_refreshview '''+@viewName+''''
print('refreshing '+@viewName) exec(@sql)
end
fetch next from cur into @viewName
end close cur
deallocate cur
and id in (select id from sysobjects where type='P')
@objectName varchar(255)
as
declare @text varchar(8000)
select @text = text from syscomments where id = object_id(@objectName)
declare @sql varchar(8000),
@sqlBegin varchar(8000),
@sqlEnd varchar(8000) set @sqlBegin = left(@text, charindex('create ', @text) - 1) set @sqlEnd = right(@text, len(@text) - charindex('create ', @text) -5 )
set @sql = @sqlBegin+ 'alter '+@sqlEnd
-- print(@sql)
exec(@sql)------然后再写个存储过程用游标循环一下跟 sp_refreshAllViews
------然后再写个存储过程用游标循环一下跟 sp_refreshAllViews
==>------然后再写个存储过程用游标循环一下跟 sp_refreshAllViews 一要--谢谢roy_88 ,cxmcxm 两位提示=》谢谢roy_88 ,cxmcxm 两位的提示 ========
真不怎么搞的,打字越来越不行