Advertisements
Home > SQL > How to find out the tables and columns having particular data in SQL?

How to find out the tables and columns having particular data in SQL?


How to find out the tables and columns that hold the particular data

 In some of the scenario a developer have to check is there any particular value is stored in the any of the table in the entire db. It is very hard to find out the column and table that holds the particular data, by calling select query for each and every table in the database.

Following store procedure will search the particular data that we are given as the paramaeter to stored procedure, in the all tables and coloumns in the entire db and return the results. If we want to perfect match of the search key given the second parameter as 1 else 0.

 StoredProcedure for search a keyword in the entire database.

CREATE PROCEDURE FindMyData_String
    @DataToFind NVARCHAR(4000),
    @ExactMatch BIT = 0
AS
SET NOCOUNT ON

DECLARE @Temp TABLE(RowId INT IDENTITY(1,1),
SchemaName sysname,
TableName sysname,
ColumnName SysName,
DataType VARCHAR(100), DataFound BIT)

INSERT  INTO @Temp(TableName,SchemaName, ColumnName, DataType)
SELECT  C.Table_Name,C.TABLE_SCHEMA, C.Column_Name, C.Data_Type
FROM    Information_Schema.Columns AS C
        INNER Join Information_Schema.Tables AS T
            ON C.Table_Name = T.Table_Name
    AND C.TABLE_SCHEMA = T.TABLE_SCHEMA
WHERE   Table_Type = 'Base Table'
        And Data_Type In
        ('ntext','text','nvarchar','nchar','varchar','char')

DECLARE @i INT
DECLARE @MAX INT
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SchemaName sysname
DECLARE @SQL NVARCHAR(4000)
DECLARE @PARAMETERS NVARCHAR(4000)
DECLARE @DataExists BIT
DECLARE @SQLTemplate NVARCHAR(4000)

SELECT  @SQLTemplate = CASE WHEN @ExactMatch = 1
    THEN 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               = ''' + @DataToFind + '''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    ELSE 'If Exists(Select *
                  From   ReplaceTableName
                  Where  Convert(nVarChar(4000), [ReplaceColumnName])
                               Like ''%' + @DataToFind + '%''
                  )
             Set @DataExists = 1
         Else
             Set @DataExists = 0'
    END,
    @PARAMETERS = '@DataExists Bit OUTPUT',
    @i = 1

SELECT @i = 1, @MAX = MAX(RowId)
FROM   @Temp

WHILE @i <= @MAX
BEGIN
    SELECT  @SQL = REPLACE(REPLACE(@SQLTemplate, 'ReplaceTableName',
    QUOTENAME(SchemaName) + '.' + QUOTENAME(TableName)),
    'ReplaceColumnName', ColumnName)
    FROM    @Temp
    WHERE   RowId = @i

    PRINT @SQL
    EXEC SP_EXECUTESQL @SQL, @PARAMETERS, @DataExists = @DataExists OUTPUT

    IF @DataExists =1
        UPDATE @Temp SET DataFound = 1 WHERE RowId = @i

    SET @i = @i + 1
END

SELECT  SchemaName,TableName, ColumnName
FROM    @Temp
WHERE   DataFound = 1
GO
Advertisements
Categories: SQL Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: