如何在数据库组上运行查询？ [英] How to run a query on group of databases ?

问题描述

Hii，



i必须在几个数据库组相同的查询中运行创建表查询...我该怎么做..



说例子我有7个数据库，其中有4个数据库，我想立即触发这个创建语句我怎么能这样做？

Hii ,

i have to run create table query on group of few databases same query ... How can i do that ..

say example i have 7 databases among those there are 4 databases on which i want to fire this create statement at once how can i do that ??

推荐答案

查看链接：

查询针对特定基团的数据库

如何在一个实例上运行同一个查询对所有数据库实施

run-query-in-all-database

希望它有所帮助：）

Check out the link:
query-against-specific-group-of-database
how-to-run-the-same-query-on-all-the-databases-on-an-instance
run-query-in-all-database
Hope it helps :)

尝试假设所有数据库都在同一个实例上。

Try this assuming all databases are on the same instance.

DECLARE @DATABASE TABLE
(
	ID INT IDENTITY(1, 1),
	NAME VARCHAR(100)
)

INSERT INTO @DATABASE (NAME)
SELECT 'Adventure' 
UNION ALL
SELECT 'TESTDB'

DECLARE @INDEX INT, @TOTAL INT, @DBNAME VARCHAR(100)

SELECT @INDEX = 1
SELECT @TOTAL = COUNT(*) FROM @DATABASE


WHILE @INDEX <= @TOTAL
BEGIN

	SELECT @DBNAME = NAME
	FROM	@DATABASE
	WHERE	ID = @INDEX
	
	SELECT 'USE ' + @DBNAME, @INDEX
	
	EXECUTE ('USE ' + @DBNAME)
	
	EXECUTE ('CREATE TABLE ' + @DBNAME + '..TEST1	(NAME VARCHAR(100)) ')
	 

	SET @INDEX = @INDEX + 1 

END

假设您可以连接到所有数据库，并将尽可能一次创建所有，我建议存储过程。



这样的程序可以让你几乎同时处理这个（如果你这样做）取决于你如何使用参数，如果你有这样的需要，可以使用多组表格。





如何写一个创建表的基本过程是：

这里^



< b>请注意，通常不会通过存储过程创建表。我建议这使它基本上成为一个批处理过程。

Assuming you can connect to all of the databases, and taking your 'create all at once' as literally as possible, I suggest a stored procedure.

Such a procedure would allow you to handle this almost concurrently (if you to do this with multiple sets of tables and if you have such a need) depending upon how/if you use parameters.


An example of how to write a basic procedure for creating the table is:
Here^

Note that creating a table via a stored procedure isn't normally done. I'm suggesting this to make it essentially a batch process.

这篇关于如何在数据库组上运行查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！