从数据库中以字符串格式文本检索表名

Question

我有一个这样的查询，

@"SELECT 
                                               TABLE_NAME
                                              AS
                                               TABLES
                                            FROM 
                                               INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                                           WHERE 
                                               CONSTRAINT_TYPE = 'PRIMARY KEY'
                                             AND
                                               TABLE_NAME <> 'dtProperties'
                                        ORDER BY
                                               TABLE_NAME";

当从此查询中检索表列表时，有什么方法可以将表格形式转换为字符串格式形式。

Answer 1

通过 .NET，两者都不是 - 这只是 SSMS 显示。根据前面的问题，听起来您只是很难将本专栏读成字符串；

也许只是这样的东西：（

    List<string> list = new List<string>();
    using(var conn = new SqlConnection(connectionString))
    using (var cmd = conn.CreateCommand()) {
        cmd.CommandText = @"
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
            AND TABLE_NAME <> 'dtProperties'
            ORDER BY TABLE_NAME";
        conn.Open();

        using (var reader = cmd.ExecuteReader()) {
            while (reader.Read()) {
                list.Add(reader.GetString(0));
            }
        }
    }

在本地测试；工作正常）

如果您想要分隔的东西，那么也许：

string s = string.Join("|", list);

或者涉及 StringBuilder 的东西（本例中的换行符）：

    StringBuilder sb = new StringBuilder();
    using(var conn = new SqlConnection(connectionString))
    using (var cmd = conn.CreateCommand()) {
        cmd.CommandText = @"
            SELECT TABLE_NAME
            FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
            WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
            AND TABLE_NAME <> 'dtProperties'
            ORDER BY TABLE_NAME";
        conn.Open();

        using (var reader = cmd.ExecuteReader()) {
            while (reader.Read()) {
                sb.AppendLine(reader.GetString(0));
            }
        }
    }
    string s = sb.ToString();

Answer 2

这样的事情有帮助吗？

string[] myObjArray = new string[DataTable1.Rows.Count];

DataTable1.Rows.CopyTo(myObjArray, 0);

Answer 3

我不知道你所说的格式化表格是什么意思。
您可以使用以下 sql 语句检索包含所有表名称的字符串：

declare @out varchar(max)
set @out = ''
select @out = @out + table_name + char(10) + char(13) from information_schema.table_constraints
where constraint_type = 'PRIMARY KEY'
and table_name <> 'dtProperties'
order by table_name

select @out