从SELECT语句生成CREATE TABLE脚本

Question

问题在于： 在SQL Server中，我有一个SELECT语句，可能采用任何形式，例如：

SELECT ID,Name
FROM Table1;

或者

SELECT ID,Name
FROM Table1
WHERE ID IN(
SELECT ID
FROM Table2
WHERE City = 'C1'
)

或者

SELECT *
FROM
(
  SELECT ID,Name
  FROM Tablex
  INTERSECT
  SELECT ID,Name
  FROM Tablex
) AS T

如果有一个非常复杂的SQL语句，或者其他IT技术相关内容，我需要生成一个CREATE TABLE语句，以便创建一个结构可以容纳SELECT语句返回的结果集数据的表。

代码可以是C#或T-SQL。

编辑：

SELECT语句是传递给我的应用程序的参数。SELECT语句通常返回数百万条记录，这些记录将从一个服务器传输到另一个服务器，因此我需要在目标服务器上执行CREATE TABLE脚本，以便可以传输数据。

- Mostafa Elmoghazi

---

从 C# 中，您可以执行 SET FMTONLY ON; YourQuery，但不确定如何使用结果。我认为它只会返回一个空结果集。 - Martin Smith

你可以使用SqlDataReader的GetSchemaTable()加上SqlBulkCopy来完成这个任务。虽然有点繁琐但是应该能够工作。如果你可以连接服务器，也可以使用SELECT INTO。 - Vinko Vrsalovic

Answer 1

在我的问题评论的启发下，我尝试了一下，非常感谢大家的帮助：

class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection con = new SqlConnection("server=myserver;database=mydb;user id=sa;password=mypassword;"))
            {

                Console.WriteLine(GetCreateTableFromSqlCode(@"
SELECT ID,Eid,Keyword AS Keywords,KeywordType AS Sources,Year
FROM Eid_Keywords 
WHERE Eid IN(SELECT Eid FROM ReviewersPublications)","Keywords",con));                

            }
        }

        public static string GetCreateTableFromSqlCode(string sqlSelect,string tableName, SqlConnection con)
        {            
            SqlCommand cmd = new SqlCommand(string.Format("SET FMTONLY ON;\r\n{0}\r\nSET FMTONLY OFF;",sqlSelect), con);
            try
            {
                con.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                DataTable dt = reader.GetSchemaTable();
                reader.Close();
                return GetCreateTableScript(dt, tableName);

            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }

        }

        private static string GetCreateTableScript(DataTable dt,string tableName)
        {
            string snip = string.Empty;
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("CREATE TABLE {0}\r\n(\r\n",tableName);
            for (int i = 0; i < dt.Rows.Count;i++)
            {
                DataRow dr = dt.Rows[i];
                snip = GetColumnSql(dr);
                sql.AppendFormat((i < dt.Rows.Count - 1) ? snip : snip.TrimEnd(',','\r','\n'));
            }
            sql.AppendFormat("\r\n)");
            return sql.ToString();
        }


        private static string GetColumnSql(DataRow dr)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("\t[{0}] {1}{2} {3} {4},\r\n",
                dr["ColumnName"].ToString(),
                dr["DataTypeName"].ToString(),
                (HasSize(dr["DataTypeName"].ToString())) ? "(" + dr["ColumnSize"].ToString() + ")" : (HasPrecisionAndScale(dr["DataTypeName"].ToString())) ? "(" + dr["NumericPrecision"].ToString() + "," + dr["NumericScale"].ToString() + ")" : "",
                (dr["IsIdentity"].ToString() == "true") ? "IDENTITY" : "",
                (dr["AllowDBNull"].ToString() == "true") ? "NULL" : "NOT NULL");
            return sql.ToString();
        }

        private static bool HasSize(string dataType)
        {            
            Dictionary<string, bool> dataTypes = new Dictionary<string, bool>();
            dataTypes.Add("bigint", false);
            dataTypes.Add("binary", true);
            dataTypes.Add("bit", false);
            dataTypes.Add("char", true);
            dataTypes.Add("date", false);
            dataTypes.Add("datetime", false);
            dataTypes.Add("datetime2", false);
            dataTypes.Add("datetimeoffset", false);
            dataTypes.Add("decimal", false);
            dataTypes.Add("float", false);
            dataTypes.Add("geography", false);
            dataTypes.Add("geometry", false);
            dataTypes.Add("hierarchyid", false);
            dataTypes.Add("image", true);
            dataTypes.Add("int", false);
            dataTypes.Add("money", false);
            dataTypes.Add("nchar", true);
            dataTypes.Add("ntext", true);
            dataTypes.Add("numeric", false);
            dataTypes.Add("nvarchar", true);
            dataTypes.Add("real", false);
            dataTypes.Add("smalldatetime", false);
            dataTypes.Add("smallint", false);
            dataTypes.Add("smallmoney", false);
            dataTypes.Add("sql_variant", false);
            dataTypes.Add("sysname", false);
            dataTypes.Add("text", true);
            dataTypes.Add("time", false);
            dataTypes.Add("timestamp", false);
            dataTypes.Add("tinyint", false);
            dataTypes.Add("uniqueidentifier", false);
            dataTypes.Add("varbinary", true);
            dataTypes.Add("varchar", true);
            dataTypes.Add("xml", false);
            if (dataTypes.ContainsKey(dataType))
                return dataTypes[dataType];
            return false;
        }

        private static bool HasPrecisionAndScale(string dataType)
        {
            Dictionary<string, bool> dataTypes = new Dictionary<string, bool>();
            dataTypes.Add("bigint", false);
            dataTypes.Add("binary", false);
            dataTypes.Add("bit", false);
            dataTypes.Add("char", false);
            dataTypes.Add("date", false);
            dataTypes.Add("datetime", false);
            dataTypes.Add("datetime2", false);
            dataTypes.Add("datetimeoffset", false);
            dataTypes.Add("decimal", true);
            dataTypes.Add("float", true);
            dataTypes.Add("geography", false);
            dataTypes.Add("geometry", false);
            dataTypes.Add("hierarchyid", false);
            dataTypes.Add("image", false);
            dataTypes.Add("int", false);
            dataTypes.Add("money", false);
            dataTypes.Add("nchar", false);
            dataTypes.Add("ntext", false);
            dataTypes.Add("numeric", false);
            dataTypes.Add("nvarchar", false);
            dataTypes.Add("real", true);
            dataTypes.Add("smalldatetime", false);
            dataTypes.Add("smallint", false);
            dataTypes.Add("smallmoney", false);
            dataTypes.Add("sql_variant", false);
            dataTypes.Add("sysname", false);
            dataTypes.Add("text", false);
            dataTypes.Add("time", false);
            dataTypes.Add("timestamp", false);
            dataTypes.Add("tinyint", false);
            dataTypes.Add("uniqueidentifier", false);
            dataTypes.Add("varbinary", false);
            dataTypes.Add("varchar", false);
            dataTypes.Add("xml", false);
            if (dataTypes.ContainsKey(dataType))
                return dataTypes[dataType];
            return false;
        }



    }

- Mostafa Elmoghazi

---

谢谢 - 这帮助我快速高效地从远程服务器获取了一些 SQL 创建脚本（我想要一些用于本地调试和原型制作）。 - Cameron

我确实不得不更改我的代码，使比较使用“True”而不是“true”。 （dr["IsIdentity"].ToString() == "True") ? "IDENTITY" : "", (dr["AllowDBNull"].ToString() == "True") ? "NULL" : "NOT NULL"); - Cameron

IsIdentity和AllowDBNull都是bool类型，所以只需使用强制转换。 例如：(bool)dr["IsIdentity"] ? "IDENTITY" : "" “true”与“True”的区别可能是由于本地区域设置造成的。 - TOS

多么好的公共服务！感谢您的付出！ - HerrimanCoder

Answer 2

我认为您需要 公共表达式(CTE) 或者 表变量。

编辑：是的，看起来我对问题的理解有误。但也许 SELECT .. INTO 语句 可以帮助您呢？BOL说：

SELECT INTO 可以用于将多个表或视图中的数据组合到一个表中。它还可以用于创建一个包含从链接服务器中选择的数据的新表。新表的结构由选择列表中表达式的属性定义。

- Alex_L

---

-1 就我所知，这些结构都无法帮助实现所述目标。对于表变量，您需要首先了解列，并且定义CTE不会解决所述问题。 - Martin Smith

这个问题在于性能，正如我之前提到的，存在链接服务器以及在像上述复杂查询或类似SELECT ，(SELECT COUNT() FROM Table1 WHERE Year = 2000) AS Count2000 FROM Table1 WHERE ID IN( SELECT ID FROM Table2 WHERE City = 'C1' )这样的查询中添加'INTO tableName'短语的位置。 - Mostafa Elmoghazi