Question

mssql查询所有数据库的所有表中的某一行怎么写？

mssql查询所有数据库的所有表中的某一行怎么写？
我的mssql里有10个数据库，每个数据库里有200个表，所有表格式相同，其中都有名为xname的列，我想列出所有数据库所有表中的xname列中含有aaa的行，这个sql要怎么写呀？

Answer 1

说一下大概语句，具体你自己该，假设10个数据库的名称是db1,db2,...,db10，这里可以根据你数据库名称改，下面的语句也要改。
创建一个表存放数据库名，表名
create table tb1
(
id [int] IDENTITY (1, 1),
dbname varchar(100),
tbname varchar(100)
)

--- 把数据库db1的所有表名插入tb1表
insert tb1 (dbname,tbname)
select 'db1',name from db1.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db2',name from db2.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db3',name from db3.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db4',name from db4.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db5',name from db5.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db6',name from db6.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db7',name from db7.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db8',name from db8.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db9',name from db9.dbo,sysobjects where xtype='u'

insert tb1 (dbname,tbname)
select 'db10',name from db10.dbo,sysobjects where xtype='u'

这样tb1表里面就有数据库名，列名。创建一个存储过程，循环搜索：
create PROCEDURE TESTPERCUDURE
AS
DECLARE @col INTEGER
DECLARE @colmax INTEGER
DECLARE @tablename varchar(100)
DECLARE @SQL AS VARCHAR(200)
BEGIN
SET @col=1
set @colmax = (select max(id) from tb1)
SET @tablename= ''
WHILE @col<=@colmax
begin
SET @tablename= (select dbname+'.dbo.'+tbname from tb1 where id = @col)
SET @SQL='select * FROM '+@tablename+' where xname = 'aaa' --通过循环获取你的表名和数据库名,之后组装成SQL语句,然后执行.
EXECUTE sp_executesql @SQL
SET @col=@col+1
END
end

追问

创建了存储过程后，怎么看回显啊？只显示命令执行成功

追答

exec TESTPERCUDURE --- 执行存储过程

追问

查询成功了，但是输出变成了几百行带空行的结果，也就是没有aaa这个数据的表的空行也显示出来了，怎么让它空行不输出，只显示有数据的表的结果？

追答

这个不行，因为是循环多次的结果，所以会出现多次的空行

Answer 2

如果所有库中要查询的表的命名附号 固定前缀+连续的数字后缀 这样的规则，可以使用以下方法 ，如不附合该规则则不能使用常规的T－sql语句来实现。：

USE master
IF object_id(N'tempdb..##temp_xname')<>0
BEGIN
DROP TABLE ##temp_xname
END
CREATE ##temp_xname --创建与要查询的表结构相同的临时表，因为在go之后还需要使用该临时表，因此用##型的。
(
Xname nvarchar(50),
……
……
)
--用游标取得所有用户数据库名，不包括系统数据库

DECLARE abc CURSOR FROM SELECT name FROM sys.databases WHERE name NOT IN(N'master',N'tempdb',N'model',N'msdb',N'ReportServer',N'ReportServerTempDB')
DECLARE @dataname varchar(50) --取数据库名
DECLARE @tablename varchar(100) --取得表的完整名，格式为：database.dbo.tablename
DECLARE @sql varchar(2000) --执行查询的字符串
DECLARE @t int --取表名后缀
OPEN CURSOR abc
FETCH next FROM abc INTO @dataname
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @t=0
SET @tablename=@dataname+'.dbo.'+'所有表名的相同前缀'+CONVERT(varchar(3),@t)
WHILE (OBJECT_ID(@tablename)<>0) --将所有表中查询到的数据保存到##临时表。
BEGIN
SET @sql ='INSERT INTO ##temp_xname select * from '+@tablename+' where xname=''aaa'''
EXEC @sql
SET @t=@t+1
SET @tablename=@dataname+'.dbo.'+'employeesalary'+CONVERT(varchar(3),@t)
END

END
CLOSE abc
SELECT * FROM ##temp_xname
DROP TABLE ##temp_xname
deallocate abc --最后要释放游标和##临时表

Answer 3

最笨的方法:
select a.* from
(
select * from 数据库名.dbo.table where xname='aaa'
union all
select * from 数据库名.dbo.table1 where xname='aaa'
.....
)a

Answer 4

用游标。。。