MS Sql Server 2000:多数据库查询 [英] MS Sql Server 2000:Multiple database query

问题描述

我为每个部门都有单独的数据库.我在主数据库表中有所有部门列表.所有数据库都命名为 a1、a2 等现在我想从查询返回的所有部门数据库中获取员工详细信息.请帮助我

I am having separate database for each dept.I have all depts list in a master database table.All database are named as a1,a2 etc Now i want to fetch the employee detail from all the dept databses returned by a query.pls help me

推荐答案

类似于:

select <columnlist>, '[databasename1]' AS dbname 
  from [databasename1].[owner].[table or view name]
UNION
select <columnlist>, '[databasename2]' AS dbname  
  from [databasename2].[owner].[table or view name]
UNION 
select <columnlist>, '[databasename3]' AS dbname  
  from [databasename3].[owner].[table or view name]

实施:

DECLARE @DBTable TABLE (ID INT, DBName VARCHAR(20), DBOwner VARCHAR(10))
INSERT INTO @DBTable
SELECT 1, 'a1', 'dbo' UNION
SELECT 2, 'a2', 'dbo' UNION
SELECT 3, 'a3', 'dbo' UNION
SELECT 4, 'a5', 'dbo' 

DECLARE @Table VARCHAR(100), @Columns VARCHAR(500), @SQL VARCHAR(8000)
SELECT @Table = 'Emloyee', @Columns = 'ID, Name, Phone', @SQL = ''
SELECT @SQL = @SQL + '
SELECT '+@Columns+', '''+DBName+''' AS Database FROM ['+DBName+'].['+DBOwner+'].['+@Table+'] UNION'
FROM @DBTable
SET @SQL = SUBSTRING(@SQL, 0, LEN(@SQL) - 5)
EXEC(@SQL)

这篇关于MS Sql Server 2000:多数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！