代码改变世界

用游标循环读取数据库名称，逐个打开数据库实现跨库查询

2015-03-02 09:27  马尔代夫_珍  阅读(243)  评论(0)  编辑  收藏  举报

DECLARE @SQL VARCHAR(MAX)

DECLARE test_cursor CURSOR

SCROLL FOR

SELECT PFDB_No from dbo.PFDBInfoTable

OPEN test_cursor

DECLARE @PFDBNo nchar(11)

FETCH NEXT FROM test_cursor INTO @PFDBNo

WHILE

@@FETCH_STATUS=0

BEGIN

 PRINT @PFDBNo    

SET @SQL='SELECT * FROM '+@PFDBNo+'.dbo.BarNoTable'  EXEC (@SQL)

 FETCH NEXT FROM test_cursor INTO @PFDBNo END

CLOSE test_cursor

DEALLOCATE test_cursor