SQL While 循环遍历数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4726899/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
While Loop to Iterate through Databases
提问by Jeff
I was wondering if someone could help me with creating a while loop to iterate through several databases to obtain data from one table from two columns. this is was I have done so far. nothing works because i do not know how to make the select statement work through each database with regards to the table that I am querying from each database (dbo.tbldoc)
我想知道是否有人可以帮助我创建一个 while 循环来遍历多个数据库以从两列的一个表中获取数据。这是我到目前为止所做的。没有任何作用，因为我不知道如何使 select 语句通过每个数据库工作，关于我从每个数据库（dbo.tbldoc）查询的表
DECLARE @Loop int
DECLARE @DBName varchar(300)
DECLARE @SQL varchar(max)
DECLARE @tableName VARCHAR(255)
SET @Loop = 1
SET @DBName = ''
WHILE @Loop = 1
BEGIN
SELECT [name] FROM sys.databases
WHERE [name] like 'z%' and create_date between '2010-10-17' and '2011-01-15'
ORDER BY [name]
SET @Loop = @@ROWCOUNT
IF @Loop = 0
BREAK
SET @SQL = ('USE ['+ @DBNAME +']')
IF EXISTS(SELECT [name] FROM sys.tables WHERE name != 'dbo.tbldoc' )
BEGIN
SELECT SUM(PGCOUNT), CREATED FROM **dbo.tbldoc**
END
ELSE
--BEGIN
PRINT 'ErrorLog'
END
回答by gbn
I would consider sp_MSForEachDBwhich is a lot easier...
我会考虑sp_MSForEachDB这要容易得多...
Edit:
编辑：
sp_MSForEachDB 'IF DB_NAME LIKE ''Z%%''
BEGIN
END
'
回答by Martin Smith
CREATE TABLE #T
(dbname sysname NOT NULL PRIMARY KEY,
SumPGCOUNT INT,
CREATED DATETIME)
DECLARE @Script NVARCHAR(MAX) = ''
SELECT @Script = @Script + '
USE ' + QUOTENAME(name) + '
IF EXISTS(SELECT * FROM sys.tables WHERE OBJECT_ID=OBJECT_ID(''dbo.tbldoc''))
INSERT INTO #T
SELECT db_name() AS dbname, SUM(PGCOUNT) AS SumPGCOUNT, CREATED
FROM dbo.tbldoc
GROUP BY CREATED;
'
FROM sys.databases
WHERE state=0 AND user_access=0 and has_dbaccess(name) = 1
AND [name] like 'z%' and create_date between '2010-10-17' and '2011-01-15'
ORDER BY [name]
IF (@@ROWCOUNT > 0)
BEGIN
--PRINT @Script
EXEC (@Script)
SELECT * FROM #T
END
DROP TABLE #T
回答by Marian
My code to search for data from more than one database would be:
我从多个数据库中搜索数据的代码是：
use [master]
go
if object_id('tempdb.dbo.#database') is not null
drop TABLE #database
go
create TABLE #database(id INT identity primary key, name sysname)
go
set nocount on
insert into #database(name)
select name
from sys.databases
where name like '%tgsdb%' --CHANGE HERE THE FILTERING RULE FOR YOUR DATABASES!
and source_database_id is null
order by name
Select *
from #database
declare @id INT, @cnt INT, @sql NVARCHAR(max), @currentDb sysname;
select @id = 1, @cnt = max(id)
from #database
while @id <= @cnt
BEGIN
select @currentDb = name
from #database
where id = @id
set @sql = 'select Column1, Column2 from ' + @currentDb + '.dbo.Table1'
print @sql
exec (@sql);
print '--------------------------------------------------------------------------'
set @id = @id + 1;
END
go
回答by Shai Alon
DECLARE @Loop int
DECLARE @MaxLoop int
DECLARE @DBName varchar(300)
DECLARE @SQL varchar(max)
SET @Loop = 1
SET @DBName = ''
set nocount on
SET @MaxLoop = (select count([name]) FROM sys.databases where [name] like 'Z%')
WHILE @Loop <= @MaxLoop
BEGIN
SET @DBName = (select TableWithRowsNumbers.name from (select ROW_NUMBER() OVER (ORDER by [name]) as Row,[name] FROM sys.databases where [name] like 'Z%' ) TableWithRowsNumbers where Row = @Loop)
SET @SQL = 'USE [' + @DBName + ']'
exec (@SQL)
...
...
set @Loop = @Loop + 1
END
***Note: I didn't add the check if exists here.
***注意：我没有在这里添加检查是否存在。
回答by Jeremy F.
This doesn't use a loop. Hope this helps!
这不使用循环。希望这可以帮助！
Note that "TABLE_OWNER" is that same as "SCHEMA Owner" and "TABLE_TYPE" will identify if the item is a table OR view.
请注意，“TABLE_OWNER”与“SCHEMA Owner”相同，“TABLE_TYPE”将标识该项目是表还是视图。
--This will return all tables, table owners and table types for all database(s) that are NOT 'Offline'
--Offline database information will not appear
Declare @temp_table table(
DB_NAME varchar(max),
TABLE_OWNER varchar(max),
TABLE_NAME varchar(max),
TABLE_TYPE varchar(max),
REMARKS varchar(max)
)
INSERT INTO @temp_table (DB_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE,REMARKS)
EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_tables'
SELECT DB_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE
FROM @temp_table
--Uncomment below if you are seaching for 1 database
--WHERE DB_NAME = '<Enter specific DB Name>'
--For all databases other than 'System Databases'
WHERE DB_NAME not in ('master','model','msdn','tempdb')
order by 1,2,3
回答by tsells
I ended up writing one last week on the fly for some stuff I was doing.
上周我为我正在做的一些事情匆匆忙忙地写了一篇。
Blog post here:
博客文章在这里：
http://tsells.wordpress.com/2012/02/14/sql-server-database-iterator/
http://tsells.wordpress.com/2012/02/14/sql-server-database-iterator/
Here is the code.
这是代码。
SET NOCOUNT ON
GO
use master
go
Declare
@dbname nvarchar(500),
@variable1 int,
@variable2 int,
@variable3 int,
@totaldb int = 0,
@totaldbonserver int = 0,
@totaldbwithmatches int = 0
-- Get non system databases
Declare mycursor CURSOR for select name, database_id from SYS.databases where database_id > 4 order by name desc
open mycursor
fetch next from mycursor into @dbname, @variable1
while (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @ParmDefinition NVARCHAR(500)
Declare @mysql nvarchar(500) = 'select @variable2OUT = COUNT(*) from [' + @dbname + '].INFORMATION_SCHEMA.TABLES where Upper(TABLE_NAME) like ''MyTable''';
SET @ParmDefinition = N'@variable2OUT int OUTPUT'
set @totaldbonserver = @totaldbonserver + 1
Execute sp_executesql @mysql, @ParmDefinition, @variable2 OUTPUT
if @variable2 = 1
BEGIN
DECLARE @ParmDefinition2 NVARCHAR(500)
Declare @mysql2 nvarchar(500) = 'select @variable2OUT = COUNT(*) from [' + @dbname + '].dbo.MyTable';
SET @ParmDefinition2 = N'@variable2OUT int OUTPUT'
Execute sp_executesql @mysql2, @ParmDefinition2, @variable3 OUTPUT
set @totaldb = @totaldb + 1
if @variable3 > 1
BEGIN
Print @dbname + ' matched the criteria'
set @totaldbwithmatches = @totaldbwithmatches + 1
END
ELSE
Select 1
END
fetch next from mycursor into @dbname, @variable1
END
PRINT 'Total databases on server: '
Print @totaldbonserver
PRINT 'Total databases tested () : '
Print @totaldb
PRINT 'Total databases with matches: '
Print @totaldbwithmatches
CLOSE mycursor
DEALLOCATE mycursor
回答by jlnorsworthy
You don't have to use a "USE DATABASE" statement. You can select from the particular database table by using a 3 part identifier as in:
您不必使用“USE DATABASE”语句。您可以使用 3 部分标识符从特定的数据库表中进行选择，如下所示：
select * from MyDatabase.dbo.MyTable
从 MyDatabase.dbo.MyTable 中选择 *
