如果只知道存储在其中的数据，如何查找表名

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?

我的DB包含超过100个表。大多数表都有像SY0001234这样的名称,我知道存储在该表中的数据,但不知道它的名称,因此无法运行正常选择。您对如何找到表名有什么想法吗?

2 个解决方案

#1

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

#2

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

你可能会更好地阅读这篇文章 - http://www.sqlservercentral.com/articles/T-SQL/67624/

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

我意识到我迟到了 - 但这可能会对你有所帮助。

#1