现在让我描述一下我面临的情况,因为可能有不同的方法来解决这个问题.我有一个有几十个数据库的实例.这些数据库都具有相同的模式,所有相同的表,索引等.它们是作为第三方软件安装的一部分创建的.我需要有一种方法来处理它们,以便我可以以临时方式聚合它们的数据. dba.se的好人已经在这里帮助了我How to create a trigger in a different database?
目前,我需要找到一种方法,从所有数据库中的表中进行选择.我已将所有数据库名称记录到名为Databasees的表中,并编写了以下脚本来对所有数据库执行select语句：
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL
DROP TABLE #tmp
select * into #tmp from Database1.dbo.Table1 where 1=0
DECLARE @statement nvarchar(max) =
N'insert into #tmp select * from Table1 where Column1=0 and Cloumn2 =1'
DECLARE @LastDatabaseID INT
SET @LastDatabaseID = 0
DECLARE @DatabaseNameToHandle varchar(60)
DECLARE @DatabaseIDToHandle int
SELECT TOP 1 @DatabaseNameToHandle = Name,@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No
WHILE @DatabaseIDToHandle IS NOT NULL
BEGIN
DECLARE @sql NVARCHAR(MAX) = QUOTENAME(@DatabaseNameToHandle) + '.dbo.sp_executesql'
EXEC @sql @statement
SET @LastDatabaseID = @DatabaseIDToHandle
SET @DatabaseIDToHandle = NULL
SELECT TOP 1 @DatabaseNameToHandle = Name,@DatabaseIDToHandle = Database_Ref_No
FROM Databasees
WHERE Database_Ref_No > @LastDatabaseID
ORDER BY Database_Ref_No
END
select * from #tmp
DROP TABLE #tmp
但是,上面的脚本失败并显示以下消息：
An explicit value for the identity column in table ‘#tmp’ can only be
specified when a column list is used and IDENTITY_INSERT is ON.
添加这个：
SET IDENTITY_INSERT #tmp ON
没有帮助,因为,我无法指定列列表并保持通用.
在sql中,无法关闭给定表上的标识.您只能删除一列并添加一列,这显然会更改列顺序.如果列顺序发生变化,您又需要指定列列表,根据您查询的表格,列表列表会有所不同.
所以我在考虑是否可以在我的T-sql代码中获取create table scrip,我可以使用字符串操作表达式来操作它以删除标识列,并将数据库名称的列添加到结果集中.
任何人都可以想到一个相对简单的方法来实现我想要的吗？
解决方法
CREATE TABLE script via T-SQL rather than using the UI or SMO. I was summarily rejected.
但是,sql Server 2012使这很容易.让我们假装我们在多个数据库中有一个具有相同模式的表,例如dbo.whatcha：
CREATE TABLE dbo.whatcha ( id INT IDENTITY(1,1),x VARCHAR(MAX),b DECIMAL(10,2),y SYSNAME );
以下脚本使用新的sys.dm_exec_describe_first_results_set dynamic management function为每个列检索正确的数据类型(并忽略IDENTITY属性).它构建您需要的#tmp表,从列表中的每个数据库插入,然后从#tmp中选择,所有这些都在一个动态sql批处理中,而不使用WHILE循环(这样做不会更好,只是更简单)查看并允许您完全忽略Database_Ref_No :-)).
SET NOCOUNT ON; DECLARE @sql NVARCHAR(MAX),@cols NVARCHAR(MAX) = N''; SELECT @cols += N',' + name + ' ' + system_type_name FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.whatcha',NULL,1); SET @cols = STUFF(@cols,1,N''); SET @sql = N'CREATE TABLE #tmp(' + @cols + ');' DECLARE @dbs TABLE(db SYSNAME); INSERT @dbs VALUES(N'db1'),(N'db2'); -- SELECT whatever FROM dbo.databases SELECT @sql += N' INSERT #tmp SELECT ' + @cols + ' FROM ' + QUOTENAME(db) + '.dbo.tablename;' FROM @dbs; SET @sql += N' SELECT ' + @cols + ' FROM #tmp;'; PRINT @sql; -- EXEC sp_executesql @sql;
CREATE TABLE #tmp(id int,x varchar(max),b decimal(10,y nvarchar(128)); INSERT #tmp SELECT id,x,b,y FROM [db1].dbo.tablename; INSERT #tmp SELECT id,y FROM [db2].dbo.tablename; SELECT id,y FROM #tmp;
当您确信它正在按照您的期望行事时,只需取消对EXEC的注释即可.
(这相信您的架构是相同的;它不会验证一个或多个表已被更改,因此可能会失败.)
