SQL query to find tables with a specific value in any column and in any table

An excellent way of scanning a database [Every Table, Every Column] for any Value.  As I’m sure you can imagine, this is incredibly useful for a developer, especially those who have to support existing applications…

This has been  wrapped into a stored procedure, This can be used to identify a particular value existing in the whole database. Interesting isnt it ?

This can be of great power if directly given to the user–and possibly of great destruction. Remember, not everyone should be priveleged to all information.

Now, for those ambitious persons out there, this information can be augmented with additional useful information–Perhaps the value of the Primary Key, or a SQL Statement to query that table/column/value, etc. Good luck

(

@Value VARCHAR(64)

)

AS

BEGIN

DECLARE @sql VARCHAR(MAX)

DECLARE @TableName VARCHAR(64)

DECLARE @ColumnName VARCHAR(64)

CREATE TABLE #Results

(

TableName VARCHAR(64)

,    ColumnName VARCHAR(64)

)

DECLARE TABLES CURSOR

FOR

SELECT

o.name

,    c.name

FROM syscolumns c

INNER JOIN sysobjects o

ON c.id = o.id

WHERE o.type = ‘U’

AND c.xtype IN (167, 175, 231, 239)

ORDER BY

o.name

,    c.name

OPEN TABLES

FETCH NEXT FROM TABLES

INTO @TableName

,    @ColumnName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = ‘IF EXISTS(SELECT NULL FROM [‘ + @TableName + ‘] ‘

SET @sql = @sql + ‘WHERE RTRIM(LTRIM([‘ + @ColumnName + ‘])) LIKE ”%’ + @value + ‘%”) ‘

SET @sql = @sql + ‘INSERT INTO #Results ( TableName, ColumnName ) VALUES (”’+ @TableName + ”’, ”’

SET @sql = @sql + @ColumnName + ”’)’

EXEC(@sql)

FETCH NEXT FROM TABLES

INTO @TableName

,    @ColumnName

END

CLOSE TABLES

DEALLOCATE TABLES

SELECT *

FROM #Results

DROP TABLE #Results

END

Tagged on:

4 thoughts on “SQL query to find tables with a specific value in any column and in any table

  1. jai

    for ease here is the sp for you ,execute and just pass the parameters

    /****** Object: StoredProcedure [dbo].[FindMatchingValueInAnyTable] Script Date: 07/16/2009 15:14:44 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    Create PROCEDURE [dbo].[FindMatchingValueInAnyTable]
    (
    @Value VARCHAR(64)
    )
    AS
    BEGIN

    DECLARE @sql VARCHAR(MAX)
    DECLARE @TableName VARCHAR(64)
    DECLARE @ColumnName VARCHAR(64)

    CREATE TABLE #Results
    (
    TableName VARCHAR(64)
    , ColumnName VARCHAR(64)
    )

    DECLARE TABLES CURSOR
    FOR

    SELECT
    o.name
    , c.name
    FROM syscolumns c
    INNER JOIN sysobjects o
    ON c.id = o.id
    WHERE o.type = ‘U’
    AND c.xtype IN (167, 175, 231, 239)
    ORDER BY
    o.name
    , c.name

    OPEN TABLES

    FETCH NEXT FROM TABLES
    INTO @TableName
    , @ColumnName

    WHILE @@FETCH_STATUS = 0
    BEGIN

    SET @sql = ‘IF EXISTS(SELECT NULL FROM [‘ + @TableName + ‘] ‘
    SET @sql = @sql + ‘WHERE RTRIM(LTRIM([‘ + @ColumnName + ‘])) LIKE ”%’ + @value + ‘%”) ‘
    SET @sql = @sql + ‘INSERT INTO #Results ( TableName, ColumnName ) VALUES (”’ + @TableName + ”’, ”’
    SET @sql = @sql + @ColumnName + ”’)’

    EXEC(@sql)

    FETCH NEXT FROM TABLES
    INTO @TableName
    , @ColumnName
    END

    CLOSE TABLES
    DEALLOCATE TABLES

    SELECT *
    FROM #Results

    DROP TABLE #Results

    END

  2. Sanjoy Banerjee

    Dear All, Please help for the below mentioned problem.

    I have a table, in that table there is some rows contains sql queries, but when i fetched the sql and try execute through asp, then query is not executed.

  3. Ranganath

    Hi People,

    I wanna know how to create this procedure, execute and the parameters that are to be passed while executing the procedure.

    Im quite new to this and I have a requirement of getting the tables with columns using the value as the input.

    Thanks!!

Leave a Reply

Your email address will not be published. Required fields are marked *

Bitnami