sql-server – 有没有办法在TSQL中生成表创建脚本？

现在让我描述一下我面临的情况,因为可能有不同的方法来解决这个问题.我有一个有几十个数据库的实例.这些数据库都具有相同的模式,所有相同的表,索引等.它们是作为第三方软件安装的一部分创建的.我需要有一种方法来处理它们,以便我可以以临时方式聚合它们的数据. 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,我可以使用字符串操作表达式来操作它以删除标识列,并将数据库名称的列添加到结果集中.

任何人都可以想到一个相对简单的方法来实现我想要的吗？

解决方法

但是,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;

生成的PRINT输出：

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的注释即可.

(这相信您的架构是相同的;它不会验证一个或多个表已被更改,因此可能会失败.)

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!