locked
How to search for a table using field name. RRS feed

  • Question

  •  

    hi,

    Is there any way to get the table name in a database by searching using field value.

     

    For eg

    ASTND is  field the value stored in a table in  a database.I m not sure about the table name and column name.

    I need to search for table which contains the value.

     

     

    Pls help me.

    Thursday, December 11, 2008 6:00 PM

Answers

  • Here is another version.  It does the same thing but in a different way.

     

    Code Snippet

    CREATE PROCEDURE usp_SearchAllTableColumns

     

    @SearchStr VARCHAR(500)

     

    AS

    BEGIN

     

    DECLARE @Table VARCHAR(100),

                @Column VARCHAR(100),

                @sql NVARCHAR(max)

     

    CREATE TABLE #Results(

    ColumnName varchar(100),

    ColumnValue varchar(max)

    )

     

    DECLARE cursorDB_Action CURSOR FOR

    SELECT c.[Table_Name], c.[Column_name]

    FROM INFORMATION_SCHEMA.[COLUMNS] c

    WHERE c.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

     

    OPEN cursorDB_Action

    FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

     

    WHILE @@FETCH_STATUS = 0

        BEGIN

                SET @sql = N'

                      INSERT INTO #Results

                      SELECT '

                            + QUOTENAME(QUOTENAME(@Table) + '.' + QUOTENAME(@Column),'''') + ',' +

                            + QUOTENAME(@Table) + '.' + QUOTENAME(@Column) + '

                      FROM ' + QUOTENAME(@Table) + ' (NOLOCK)

                      WHERE ' + QUOTENAME(@Column) + ' LIKE ' + QUOTENAME(@SearchStr + '%','''')

     

                PRINT @sql

                EXEC SP_EXECUTESQL @sql

                FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

        END

     

    CLOSE cursorDB_Action

    DEALLOCATE cursorDB_Action

     

    SELECT *

    FROM #Results

     

    END

     

     

    Edit: I see you are saying you cannot execute a stored procedure.  In that case strip the code and use it as an ad hoc query, as shown below.

     

    Code Snippet

    DECLARE @SearchStr VARCHAR(100),

                @Table VARCHAR(100),

                @Column VARCHAR(100),

                @sql NVARCHAR(500)

     

    SET @SearchStr = 'Test'

     

    CREATE TABLE #Results(

    ColumnName varchar(100),

    ColumnValue varchar(500)

    )

     

    DECLARE cursorDB_Action CURSOR FOR

    SELECT c.[Table_Name], c.[Column_name]

    FROM INFORMATION_SCHEMA.[COLUMNS] c

    WHERE c.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

     

    OPEN cursorDB_Action

    FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

     

    WHILE @@FETCH_STATUS = 0

        BEGIN

                SET @sql = N'

                      INSERT INTO #Results

                      SELECT '

                            + QUOTENAME(QUOTENAME(@Table) + '.' + QUOTENAME(@Column),'''') + ',' +

                            + QUOTENAME(@Table) + '.' + QUOTENAME(@Column) + '

                      FROM ' + QUOTENAME(@Table) + ' (NOLOCK)

                      WHERE ' + QUOTENAME(@Column) + ' LIKE ' + QUOTENAME(@SearchStr + '%','''')

     

                PRINT @sql

                EXEC SP_EXECUTESQL @sql

                FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

        END

     

    CLOSE cursorDB_Action

    DEALLOCATE cursorDB_Action

     

    SELECT *

    FROM #Results

     

    DROP TABLE #Results

     

     

    • Marked as answer by Cool Tech Friday, December 12, 2008 9:16 AM
    Thursday, December 11, 2008 6:45 PM

All replies

  • Thanks.But in my database i dont have permission to execute a stored procedure.

     

    I need a simple query which does the function like searching column name using

     

    SELECT *
      FROM INFORMATION_SCHEMA.columns
      WHERE Column_Name like '%tax%'

     

    Thursday, December 11, 2008 6:32 PM
  • Here is another version.  It does the same thing but in a different way.

     

    Code Snippet

    CREATE PROCEDURE usp_SearchAllTableColumns

     

    @SearchStr VARCHAR(500)

     

    AS

    BEGIN

     

    DECLARE @Table VARCHAR(100),

                @Column VARCHAR(100),

                @sql NVARCHAR(max)

     

    CREATE TABLE #Results(

    ColumnName varchar(100),

    ColumnValue varchar(max)

    )

     

    DECLARE cursorDB_Action CURSOR FOR

    SELECT c.[Table_Name], c.[Column_name]

    FROM INFORMATION_SCHEMA.[COLUMNS] c

    WHERE c.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

     

    OPEN cursorDB_Action

    FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

     

    WHILE @@FETCH_STATUS = 0

        BEGIN

                SET @sql = N'

                      INSERT INTO #Results

                      SELECT '

                            + QUOTENAME(QUOTENAME(@Table) + '.' + QUOTENAME(@Column),'''') + ',' +

                            + QUOTENAME(@Table) + '.' + QUOTENAME(@Column) + '

                      FROM ' + QUOTENAME(@Table) + ' (NOLOCK)

                      WHERE ' + QUOTENAME(@Column) + ' LIKE ' + QUOTENAME(@SearchStr + '%','''')

     

                PRINT @sql

                EXEC SP_EXECUTESQL @sql

                FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

        END

     

    CLOSE cursorDB_Action

    DEALLOCATE cursorDB_Action

     

    SELECT *

    FROM #Results

     

    END

     

     

    Edit: I see you are saying you cannot execute a stored procedure.  In that case strip the code and use it as an ad hoc query, as shown below.

     

    Code Snippet

    DECLARE @SearchStr VARCHAR(100),

                @Table VARCHAR(100),

                @Column VARCHAR(100),

                @sql NVARCHAR(500)

     

    SET @SearchStr = 'Test'

     

    CREATE TABLE #Results(

    ColumnName varchar(100),

    ColumnValue varchar(500)

    )

     

    DECLARE cursorDB_Action CURSOR FOR

    SELECT c.[Table_Name], c.[Column_name]

    FROM INFORMATION_SCHEMA.[COLUMNS] c

    WHERE c.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

     

    OPEN cursorDB_Action

    FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

     

    WHILE @@FETCH_STATUS = 0

        BEGIN

                SET @sql = N'

                      INSERT INTO #Results

                      SELECT '

                            + QUOTENAME(QUOTENAME(@Table) + '.' + QUOTENAME(@Column),'''') + ',' +

                            + QUOTENAME(@Table) + '.' + QUOTENAME(@Column) + '

                      FROM ' + QUOTENAME(@Table) + ' (NOLOCK)

                      WHERE ' + QUOTENAME(@Column) + ' LIKE ' + QUOTENAME(@SearchStr + '%','''')

     

                PRINT @sql

                EXEC SP_EXECUTESQL @sql

                FETCH NEXT FROM cursorDB_Action INTO @Table, @Column

        END

     

    CLOSE cursorDB_Action

    DEALLOCATE cursorDB_Action

     

    SELECT *

    FROM #Results

     

    DROP TABLE #Results

     

     

    • Marked as answer by Cool Tech Friday, December 12, 2008 9:16 AM
    Thursday, December 11, 2008 6:45 PM