GO
/****** 对象: StoredProcedure [dbo].[aa] 脚本日期: 12/20/2012 14:33:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <wdfscp>
-- Create date: <2012-12-19>
-- Description: <更行数据库中所有的sysObjNo>
-- =============================================
CREATE PROCEDURE [dbo].[aa]
AS
BEGIN
declare @Table_Name varchar(50)
declare @Columns varchar(50)
declare @strsql varchar(500)
--查询系统中所有的数据表\出去视图表
--select * into #temp1 from information_schema.tables where table_type='BASE TABLE'
declare myCursor cursor for select Table_Name from information_schema.tables
where table_type='BASE TABLE' and table_name<>'pub_sys_object_inf' order by Table_Name
open myCursor
fetch next from myCursor into @Table_Name
--print @Table_Name
--print @@fetch_status
while(@@fetch_status=0)
begin
--查询循环表的字段
Select @Columns=''
Select @Columns=name from syscolumns Where ID=OBJECT_ID(@Table_Name) and lower(name)='sysobjno'
--print @Columns
if(lower(@Columns)='sysobjno')
begin
Select @strSql='update '+ @Table_Name+' set sysObjNo='+char(39)+'0008'+char(39)
--print @strSql
exec (@strSql)
end
fetch next from myCursor into @Table_Name
end
close myCursor
deallocate myCursor
END