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

有没有办法从纯粹在T-SQL中的现有表生成创建脚本(不使用SMO,因为T-SQL无法访问SMO).假设一个存储过程接收一个表名并返回一个包含给定表的创建脚本的字符串？

现在让我描述一下我面临的情况,因为可能有不同的方法来解决这个问题.我有一个有几十个数据库的实例.这些数据库都具有相同的模式,所有相同的表,索引等.它们是作为第三方软件安装的一部分创建的.我需要有一种方法来处理它们,以便我可以以临时方式聚合它们的数据. 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的注释即可.

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

（编辑：安卓应用网）

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