Find a Specific Column Entry in an Unknown Table in a Database

Find a specific column in an unknown table in a database?

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE column_name LIKE '%watcher%'
[AND table_schema = 'database']

How to find fields in a unknown table in a database?

If I understood you correctly, you need to find the exact names of the tables that were named like your project plus they have some additional characters in their names (that look like dates and times).

Well, you can list all the tables that start with the name of your project, using a query like this:

SELECT *
FROM sys.tables
WHERE name LIKE 'yourprojectname%'

sys.tables is a system view where all your tables are listed.

'yourprojectname%' is a mask used for filtering through the list of tables. The % character is neccessary. It means 'any character or characters, any number of them (or none of them)'. (Without % the output would show you only one table whose name is exactly like your project's name. If such a table exists, that is.)

Search for one value in any column of any table inside a database

How to search all columns of all
tables in a database for a keyword?

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

EDIT: Here's the actual T-SQL, in case of link rot:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO #Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM #Results
 END

Describe each table's columns

You could use information_schema.COLUMNS to get all the fields returned by describe command.

Try:

select TABLE_SCHEMA as 'database',
       TABLE_NAME as 'table',
       COLUMN_NAME as 'Field',
       DATA_TYPE as 'Type',
       IS_NULLABLE as 'Null',
       COLUMN_KEY as 'Key',
       COLUMN_DEFAULT as 'Default',
       EXTRA as 'Extra' 
from information_schema.COLUMNS;

Note. Add specific condition if you want to limit the search for specific schema or table like:

WHERE TABLE_SCHEMA='your_schema' AND TABLE_NAME='your_table_name'

EDIT
Tested on my server

mysql> select TABLE_SCHEMA as 'database',
       TABLE_NAME as 'table',
           COLUMN_NAME as 'Field',
           DATA_TYPE as 'Type',
           IS_NULLABLE as 'Null',
           COLUMN_KEY as 'Key',
           COLUMN_DEFAULT as 'Default',
           EXTRA as 'Extra'
from information_schema.COLUMNS
WHERE TABLE_SCHEMA='gesti' AND TABLE_NAME='test_table';
+----------+------------+-------+----------+------+-----+---------+----------------+
| database | table      | Field | Type     | Null | Key | Default | Extra          |
+----------+------------+-------+----------+------+-----+---------+----------------+
| gesti    | test_table | id    | int      | NO   | PRI | NULL    | auto_increment |
| gesti    | test_table | title | longtext | NO   | MUL | NULL    |                |
+----------+------------+-------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> desc test_table;
+-------+----------+------+-----+---------+----------------+
| Field | Type     | Null | Key | Default | Extra          |
+-------+----------+------+-----+---------+----------------+
| id    | int      | NO   | PRI | NULL    | auto_increment |
| title | longtext | NO   | MUL | NULL    |                |
+-------+----------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Search for an entry - in a Database ( without specifying a Column name/Table name)

As for as I know there is nothing built-into MySQL that accomplishes this, however there is an alternative. This is not a very efficient approach, but if you must do this there is a way!

http://code.google.com/p/anywhereindb/

---

Related Topics