Is there a way to search the fields of all tables at once, in SQL Server CE?

Question

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.

I have been here: http://blogs.thesitedoctor.co.uk/tim/2007/11/02/How+To+Search+Every+Table+And+Field+In+A+SQL+Server+Database.aspx

And here, on stackoverflow: search all tables, all columns for a specific value SQL Server

As well as here: How do I find a value anywhere in a SQL Server Database?

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.

Thanks!

-----------------------------SQL SubQuery That Works In SQL CE-----------------------------

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

Answer 1

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')

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.

EDIT:

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.

Answer 2

I'm not sure about CE, but I use this method:

http://www.dbforums.com/microsoft-sql-server/972792-find-text-string-database.html

namely the third one down. Read and understand the syntax as you use it, it's highly modifiable and can filter by data type.