ALTER PROC QueryMultiDB @dbs VARCHAR(2000),@sql VARCHAR(5000)
AS
DECLARE @dbName VARCHAR(15)
DECLARE @idx INT
DECLARE @tempSql VARCHAR(5000)
SET @tempSql=@sql
--判断sql语句是否有dbo, 如果没有则提示并返回. 这个判断也不够精确,靠自己输入把握了.
IF(CHARINDEX(' dbo.',@sql)<=0)
BEGIN
RAISERROR( 'sql语句中的所有表名前必须加上dbo., 例如: select * from dbo.tableName',16,1)
RETURN
END
WHILE (LEN(@dbs) > 0)
BEGIN
SET @idx = CHARINDEX(',', @dbs)
IF (@idx > 0)
BEGIN
SET @dbName = SUBSTRING(@dbs, 0, @idx)
SET @dbs = SUBSTRING(@dbs, @idx + 1, LEN(@dbs) -@idx)
END
ELSE
SET @dbName = @dbs
SET @dbName=LTRIM(RTRIM(@dbName))
PRINT '----' + @dbName + '----'
SET @sql=REPLACE(@tempSql,' dbo.',' '+@dbName+'.dbo.')
--注: 某些地方需要显示dbName, 传入的sql语句应类似: 'SELECT ''@dbName'' as webid,* FROM dbo.table'
SET @sql=REPLACE(@sql,'@dbName',@dbName)
--print (@sql) --用于调试, 判断动态生成的sql语句是否正确
EXEC(@sql) --执行动态sql
IF (@idx <= 0)
BREAK
END
--执行 EXEC QueryMultiDB 'db01,db02' ,'SELECT top 5 ''@dbName'' as webid,* FROM dbo.tableName'
最佳搭档: