How to find table name if I know only data which stored in it

Question

I have DB which contains more than 100 tables. Most of the tables have names like SY0001234 I know data which stored in that table but do not know the name of it so cant run normal select against it. Do you have any ideas how to find the table name?

@user814064 yes i know value which is in that table in one of the rows — Andrey, Sep 17 '13 at 23:11
can you bulk copy the whole DB to a file? then you could free text search. i'm sure there are some tools like RedGate that do this with a nice GUI for you too. — Andyz Smith, Sep 17 '13 at 23:15
I think your question is answered here: http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-sqll-server-db — dcaswell, Sep 17 '13 at 23:18
that link is good. i especially like the idea of using the profiler if you have a running application you can exercise. — Andyz Smith, Sep 17 '13 at 23:20
Try one of the following threads [How do I find a value anywhere in a SQL Server Database?][1] [Search for a string in an all the tables, rows and columns of a SQLL Server DB][2] [How to search for one value in any column of any table inside one MS-SQL database?][3] [1]: http://stackoverflow.com/questions/436351/how-do-i-find-a-value-anywhere-in-a-sql-server-database [2]: http://stackoverflow.com/questions/591853/search-for-a-string-in-an-all-the-tables-rows-and-columns-of-a-sqll-server-db [3]: http://stackoverflow.com/questions/709120/how-to-search-for-one-value-in-any-colu — Chaz Gardyner, Sep 18 '13 at 11:39

score 2 · Accepted Answer · answered Sep 17 '13 at 23:33

You do perform something like this

--EXEC SearchAllTables 'test'

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

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

score 1 · Answer 2 · answered Sep 18 '13 at 06:18

1

You may do better to read this article - http://www.sqlservercentral.com/articles/T-SQL/67624/

I realize I am late to the party - but this may help you.

answered Sep 18 '13 at 06:18

Leptonator

3,379
2
38
51

How to find table name if I know only data which stored in it

2 Answers2