有没有办法在 SQL Server CE 中一次搜索所有表的字段? [英] Is there a way to search the fields of all tables at once, in SQL Server CE?
问题描述
我希望在 WebMatrix (C#.net) 环境(使用 SQL Server Compact)中有一种方法可以搜索所有表和字段的值.我有一堆(比如 100 个)表,通过 WebMatrix 连接,我正在尝试寻找一个包含我需要的一些信息的表.
I was hoping that within the WebMatrix (C#.net) environment (using SQL Server Compact) that there was a way to search all tables and fields for a value. I have a bunch (like 100) of tables, connected to via WebMatrix and I am trying to look for a table that holds some information I need.
这里,在 stackoverflow 上:搜索所有表,特定值 SQL Server 的所有列
And here, on stackoverflow: search all tables, all columns for a specific value SQL Server
以及这里:如何在 SQL Server 数据库中的任何位置查找值?
不幸的是,我没有看到如何在我当前的环境中实现这些方法,但我意识到可能没有办法真正做到我所要求的.
Unfortunately I am not seeing how to implement these methods in my current environment, but I realize there may not really be a way to do what I am asking.
无论有没有办法做我所要求的,我至少想知道,所以我可以寻找另一种方法.
Whether there is or there isn't a way to do what I'm asking, I would at least like to know, so I can look for another method.
谢谢!
---------------------在 SQL CE 中运行的 SQL 子查询-----------------------------
-----------------------------SQL SubQuery That Works In SQL CE-----------------------------
SELECT * FROM UserProfile JOIN pages_UsersInRolesON UserProfile.UserID = pages_UsersInRoles.UserIdWHERE (RoleId <> 6) 和电子邮件不在(SELECT Email FROM UserProfile JOIN pages_UsersInRolesON UserProfile.UserID = pages_UsersInRoles.UserIdWHERE RoleId = 6) 通过电子邮件订购
SELECT * FROM UserProfile JOIN webpages_UsersInRoles ON UserProfile.UserID = webpages_UsersInRoles.UserId WHERE (RoleId <> 6) AND Email NOT IN (SELECT Email FROM UserProfile JOIN webpages_UsersInRoles ON UserProfile.UserID = webpages_UsersInRoles.UserId WHERE RoleId = 6) ORDER BY Email
推荐答案
我已经改编了 Syn123 的答案中的 SQL,这是我目前所拥有的:
I've adapted the SQL from Syn123's answer and here is what I have so far:
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN INFORMATION_SCHEMA.Tables AS t ON t.TABLE_NAME = c.TABLE_NAME
WHERE (c.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')) AND (t.TABLE_TYPE = 'TABLE')
我遇到的问题是我无法使用 SQL CE 进行子查询,因此无法从结果集中进行选择.如果您有权访问您的 .MDF 数据库文件,您可以编写一个小型控制台应用程序来使用上述 SQL 返回的集合搜索特定关键字.您需要一种创建动态 SQL 的方法,而 SQL CE 不支持 EXEC,因此这很困难,而且很可能本身不可能.
The problem I'm having is that I can't do subqueries with SQL CE and thus can't select from a result set. If you have access to your .MDF database file, you can write a small console app to search for a particular keyword using the set returned by the above SQL. You need a way to create dynamic SQL and SQL CE doesn't support EXEC so this is difficult and most likely not possible by itself.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SQLCESearch
{
class Program
{
static void Main(string[] args)
{
SearchText("Nancy");
Console.ReadKey();
}
private static void SearchText(string searchText)
{
string connStr = "Data Source=Northwind40.sdf;Persist Security Info=False;";
DataTable dt = new DataTable();
try
{
string sql = "SELECT c.TABLE_NAME, c.COLUMN_NAME ";
sql += "FROM INFORMATION_SCHEMA.COLUMNS AS c ";
sql += "INNER JOIN INFORMATION_SCHEMA.Tables AS t ON t.TABLE_NAME = c.TABLE_NAME ";
sql += "WHERE (c.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')) AND (t.TABLE_TYPE = 'TABLE') ";
SqlCeDataAdapter da = new SqlCeDataAdapter(sql, connStr);
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
string dynSQL = "SELECT [" + dr["COLUMN_NAME"] + "]";
dynSQL += " FROM [" + dr["TABLE_NAME"] + "]";
dynSQL += " WHERE [" + dr["COLUMN_NAME"] + "] LIKE '%" + searchText + "%'";
DataTable result = new DataTable();
da = new SqlCeDataAdapter(dynSQL, connStr);
da.Fill(result);
foreach (DataRow r in result.Rows)
{
Console.WriteLine("Table Name: " + dr["TABLE_NAME"]);
Console.WriteLine("Column Name: " + dr["COLUMN_NAME"]);
Console.WriteLine("Value: " + r[0]);
}
}
}
catch (Exception e)
{
Console.Write(e.Message);
}
}
}
}
有一个非常快速和肮脏的控制台应用程序,它将打印出包含文本值的任何表/列.您可以对其进行调整以满足您的需求.我希望这会有所帮助.
There's a really quick and dirty console application that will print out any table/column that contains a value of text. You can adapt it to fit your needs. I hope this helps.
这篇关于有没有办法在 SQL Server CE 中一次搜索所有表的字段?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!