usersp_gis_update.txt

来自「SQL语言常用的一些命令各代码」· 文本 代码 · 共 41 行

--检索所有的库，若库中含有T_MAP_STREET和T_MAP_STREETCORNER，则认为是GIS库，在其上执行相应的sql文件

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'usersp_gis_update' AND type = 'P')
   DROP PROCEDURE usersp_gis_update
go

-- 开始创建
create procedure usersp_gis_update

with ENCRYPTION

as 

begin

	SET NOCOUNT ON
        declare @dbname as varchar(100)

        --定义游标，检索所有的库
        declare dbinfo_cur cursor for select name from master..sysdatabases

        open dbinfo_cur

        fetch next from dbinfo_cur into @dbname
        WHILE @@FETCH_STATUS = 0
        begin
           if exists(SELECT * FROM INFORMATION_SCHEMA.TABLES where table_catalog=@dbname and table_name='T_MAP_STREET' )
             if exists(SELECT * FROM INFORMATION_SCHEMA.TABLES where table_catalog=@dbname and table_name='T_MAP_STREETCORNER' )
             begin
                declare @sql varchar(300)
                set @sql='osql -E -d '+@dbname+' -i C:\usersp_gis_ReadFlagInMap.sql'   --要执行的sql文件
                exec master..xp_cmdshell @sql
                select @dbname+'  success!'                    
             end
             fetch next from dbinfo_cur into @dbname
         end

        close dbinfo_cur
        deallocate  dbinfo_cur
end