HOW TO FIND A VALUE IN ALL COLUMN OF ALL TABLE IN A db.

  • Ivan,

    please check out BOL on SUBSTRING and you'll find out that this command will only work with STRINGS, but not with numeric values.

    Just remove the SUBSTRING section from the last EXEC section and you're good to go.

    - Michael

  • Steve Jones - SSC Editor (1/9/2012)


    Use a script like this: http://www.sqlservercentral.com/scripts/Miscellaneous/65769/

    Change the where from a "column like @value" to "datalength(column) = 9"

    If you want 9 digit values.

    You think you're asking for something simple, but either we have a language barrier or you don't understand what you're asking, because what you're posting doesn't make sense in English. It doesn't provide a description that can be translated to code.

    I went through Your script .If u can past the script and guide me where to make the change as because the script through lot of error when i parse it

  • Ivan Mohapatra (1/12/2012)


    Steve Jones - SSC Editor (1/9/2012)


    Use a script like this: http://www.sqlservercentral.com/scripts/Miscellaneous/65769/

    Change the where from a "column like @value" to "datalength(column) = 9"

    If you want 9 digit values.

    You think you're asking for something simple, but either we have a language barrier or you don't understand what you're asking, because what you're posting doesn't make sense in English. It doesn't provide a description that can be translated to code.

    I went through Your script .If u can past the script and guide me where to make the change as because the script through lot of error when i parse it

    Ivan,

    as I've already mentioned--just remove the SUBSTRING portion from the last EXEC() statement and you won't receive the error any more. No need for more scripts--you've already received all possible variants.

    Here's the part that needs to be changed (as it should look to work):

    'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName

    + ' FROM ' + @TableName + ' (NOLOCK) ' +

    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr

    Cheers.

  • michael.kaufmann (1/12/2012)


    Ivan Mohapatra (1/12/2012)


    Steve Jones - SSC Editor (1/9/2012)


    Use a script like this: http://www.sqlservercentral.com/scripts/Miscellaneous/65769/

    Change the where from a "column like @value" to "datalength(column) = 9"

    If you want 9 digit values.

    You think you're asking for something simple, but either we have a language barrier or you don't understand what you're asking, because what you're posting doesn't make sense in English. It doesn't provide a description that can be translated to code.

    I went through Your script .If u can past the script and guide me where to make the change as because the script through lot of error when i parse it

    Ivan,

    as I've already mentioned--just remove the SUBSTRING portion from the last EXEC() statement and you won't receive the error any more. No need for more scripts--you've already received all possible variants.

    Here's the part that needs to be changed (as it should look to work):

    'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName

    + ' FROM ' + @TableName + ' (NOLOCK) ' +

    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr

    Cheers.

    GO

    /****** Object: StoredProcedure [dbo].[SearchAllTablesAllColumns] Script Date: 01/12/2012 17:29:39 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROC [dbo].[SearchAllTablesAllColumns]

    (

    @SearchStr nvarchar(100)

    )

    AS

    BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT

    SELECT @TableName = '', @ColumnName = ''

    SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE (@ColumnName IS NOT NULL)

    BEGIN

    SET @ColumnName = (

    SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)

    + '|' + QUOTENAME(C.Column_name))

    FROM INFORMATION_SCHEMA.COLUMNS c

    INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name

    WHERE T.TABLE_TYPE = 'BASE TABLE'

    AND C.DATA_TYPE IN ('varchar','int')

    AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName

    )

    SET @Parse = PATINDEX ('%|%', @ColumnName)

    SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)

    SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))

    IF @ColumnName IS NOT NULL

    BEGIN

    INSERT INTO #Results

    EXEC

    (

    'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName

    + ' FROM ' + @TableName + ' (NOLOCK) ' +

    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr

    )

    END

    END

    SELECT ColumnName, ColumnValue FROM #Results

    ORDER BY ColumnName

    END

    This is how it look after changing but can i confined the search to 9 digit like the

    SearchAllTablesAllColumns '5________' as when i tried it IT shows alphabet also as 'ABC' i only need 9 digits value.like 999999999,000000999 etc.

    kindly guide me

  • Ivan Mohapatra (1/12/2012)


    This is how it look after changing but can i confined the search to 9 digit like the

    SearchAllTablesAllColumns '5________' as when i tried it IT shows alphabet also as 'ABC' i only need 9 digits value.like 999999999,000000999 etc.

    kindly guide me

    Check out BOL on function ISNUMERIC().

    Modify your WHERE clause in the statement you just amended--sorry but I cannot do your work for you.

    Cheers,

    Michael

  • THANKS

    (michael.kaufmann & jnuqui) After modifying the final script which i have tested in a small DB it work fine and still yet to be tested in big DB. and Isnumeric function doesn't work in Varchar datatype.

    HOPE for the Best and positive hope for testing it in Live DB.

    /****** Object: StoredProcedure [dbo].[SearchAllTablesAllColumns] Script Date: 01/12/2012 18:00:34 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROC [dbo].[SearchAllTablesAllColumns]

    (

    @SearchStr nvarchar(100)

    )

    AS

    BEGIN

    CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

    SET NOCOUNT ON

    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(255), @Parse AS INT

    SELECT @TableName = '', @ColumnName = ''

    SET @SearchStr = QUOTENAME('%' + @SearchStr + '%','''')

    WHILE (@ColumnName IS NOT NULL)

    BEGIN

    SET @ColumnName = (

    SELECT MIN(QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name)

    + '|' + QUOTENAME(C.Column_name))

    FROM INFORMATION_SCHEMA.COLUMNS c

    INNER JOIN INFORMATION_SCHEMA.TABLES t on c.table_schema = t.table_schema and c.Table_name = t.table_name

    WHERE T.TABLE_TYPE = 'BASE TABLE'

    AND C.DATA_TYPE IN ('varchar','int')

    AND QUOTENAME(C.Table_Schema) + '.' + QUOTENAME(C.Table_name) + '.' + QUOTENAME(COLUMN_NAME) > @TableName + '.' + @ColumnName

    )

    SET @Parse = PATINDEX ('%|%', @ColumnName)

    SET @TableName = SUBSTRING(@ColumnName, 1, @Parse - 1)

    SET @ColumnName = SUBSTRING(@ColumnName, @Parse +1, LEN(@ColumnName))

    IF @ColumnName IS NOT NULL

    BEGIN

    INSERT INTO #Results

    EXEC

    (

    'SELECT ''' + @TableName + '.' + @ColumnName + ''',' + @ColumnName

    + ' FROM ' + @TableName + ' (NOLOCK) ' +

    ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr

    )

    END

    END

    SELECT distinct(ColumnValue), ColumnName FROM #Results

    ORDER BY ColumnName

    END

  • This script (meaning the first one in this thread to search all tables) works perfectly for us. I cannot thank enough the person who wrote it.

    Eric

  • Hi... from search i found an sql statment mostly same as yours...

    the problem is which when searching uniqueidentifier.... It just dont work at my side...

    Anyway, SQL Statement is stated below..

    USE ServiceManager;

    DECLARE @GUID VARCHAR(MAX);

    DECLARE @columnValue VARCHAR(MAX)

    DECLARE @searchTableName VARCHAR(MAX);

    DECLARE @managementPackId VARCHAR(MAX);

    SET @columnValue = '%67C8B21E-47D5-3835-01B1-28BD72258E62%';

    SET @searchTableName = '%';

    DECLARE @managementPackTable TABLE ( TABLE_NAME VARCHAR(MAX), COLUMN_NAME VARCHAR(MAX) );

    DECLARE @generatedSQLTable TABLE ( SQLStatement VARCHAR(MAX) );

    DECLARE @tableName VARCHAR(MAX), @columnName VARCHAR(MAX);

    DECLARE @ctr INT;

    DECLARE managementPackTable_Cursor CURSOR FOR

    SELECT INFORMATION_SCHEMA.COLUMNS.TABLE_NAME, COLUMN_NAME

    FROM INFORMATION_SCHEMA.COLUMNS

    INNER JOIN INFORMATION_SCHEMA.TABLES ON

    INFORMATION_SCHEMA.TABLES.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME AND

    INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = 'dbo'

    WHERE

    INFORMATION_SCHEMA.COLUMNS.TABLE_NAME LIKE @searchTableName AND

    INFORMATION_SCHEMA.TABLES.TABLE_TYPE <> 'view' AND

    DATA_TYPE IN ('nvarchar', 'varchar')

    SET @ctr = null;

    OPEN managementPackTable_Cursor;

    FETCH managementPackTable_Cursor INTO @tableName, @columnName;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    DECLARE @sqlString NVARCHAR(MAX);

    SET @sqlString = N'SELECT @counter = COUNT(' + @tableName + '.' + @columnName + ') FROM ' + @tableName + ' WHERE ' + @columnName + ' LIKE ''' + @columnValue + '''';

    INSERT INTO @generatedSQLTable (SQLStatement) VALUES (@sqlString);

    execute sp_executesql @sqlstring, N'@counter INT OUTPUT', @counter=@ctr OUTPUT;

    IF @ctr > 0

    BEGIN

    INSERT INTO @managementPackTable ( TABLE_NAME, COLUMN_NAME ) VALUES ( @tableName, @columnName );

    END

    FETCH managementPackTable_Cursor INTO @tableName, @columnName;

    END

    DEALLOCATE managementPackTable_Cursor;

    SELECT * FROM @managementPackTable;

    SELECT * FROM @generatedSQLTable;

    Thanks in advance man...

  • my mistake.. sorry.... was just careless bout this...

    from

    DATA_TYPE IN ('nvarchar', 'varchar')

    to

    DATA_TYPE IN ('nvarchar', 'varchar','uniqueidentifier')

    :rolleyes:

  • SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    ALTERPROCEDURE[dbo].[usp_SEARCH_DATABASE_FOR_STRING]

    (

    @SearchStringVarchar(1000),

    @SearchTypeVarchar(6)= 'EQUALS',

    @StringTypeVarchar(8)= 'Text'

    )

    AS

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    --

    --PURPOSE:THIS PROCEDURE WILL SEARCH THE ENTIRE DATABASE AND IDENTITY THE TABLE AND COLUMN THAT CONTAINS A SPECIFIC SEARCH STRING AND THE COUNT OF THE SEARCH STRING IN EACH TABLE\COLUMN

    --COMBINATION. THE RESULTS ARE DISPLAY IN A QUERY RESULT SET.

    --

    --It can be extended to search for Date and Money and float data types, just copy the code for Integer Number and change the data type to Datetime, Datetime2 or money, etc., and

    --add another values for the @StringType parameter such as 'DATE' or 'MONEY' or 'FLOAT'

    --

    --AUTHOR:STEVE KIRCHNER

    --

    --CREATED:04/06/2013

    --

    --SAMPLE EXEC:

    --

    /*

    EXECusp_SEARCH_DATABASE_FOR_STRING

    @SearchString='ammo',

    @SearchType='EQUALS',

    @StringType='TEXT'

    EXECusp_SEARCH_DATABASE_FOR_STRING

    @SearchString='WEAPON',

    @SearchType='LIKE',

    @StringType='TEXT'

    EXECusp_SEARCH_DATABASE_FOR_STRING

    @SearchString='1',

    @SearchType='EQUALS',

    @StringType='NUMBER'

    EXECusp_SEARCH_DATABASE_FOR_STRING

    @SearchString='1',

    @SearchType='LIKE',

    @StringType='NUMBER'

    */

    --

    --

    --

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    DECLARE@IINT

    DECLARE@sqlVarchar(MAX)

    DECLARE@SQLqueriesTable

    (

    IDINTIDENTITY(1,1),

    SQLstatement nvarchar(max)

    )

    CREATETable##Results

    (

    IDINTIDENTITY(1,1),

    TableNamenvarchar(750),

    ColumnNamenvarchar(750),

    SearchValue nvarchar(max),

    Found_Count int

    )

    IF@StringType= 'TEXT'

    IF@SearchType = 'EQUALS'

    INSERT@SQLqueries

    SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE [' + column_name + '] = ''' + @SearchString + '''' + ' GROUP BY [' + column_name + ']'

    FROM INFORMATION_SCHEMA.COLUMNS

    INNER

    JOINsys.sysobjects

    ONOBJECT_ID(Table_Name) = ID

    WHEREDATA_TYPE in ( 'varchar', 'nvarchar', 'Text', 'nText')

    ANDxtype = 'U'

    ORDER

    BYTABLE_NAME, COLUMN_NAME

    ELSE

    INSERT@SQLqueries

    SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE [' + column_name + '] LIKE ''%' + @SearchString + '%''' + ' GROUP BY [' + column_name + ']'

    FROM INFORMATION_SCHEMA.COLUMNS

    INNER

    JOINsys.sysobjects

    ONOBJECT_ID(Table_Name) = ID

    WHEREDATA_TYPE in ( 'varchar', 'nvarchar', 'Text', 'nText')

    ANDxtype = 'U'

    ORDER

    BYTABLE_NAME, COLUMN_NAME

    ELSE-- FIND INTEGER NUMBERs

    IF@SearchType = 'EQUALS'

    INSERT@SQLqueries

    SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE [' + column_name + '] = ' + @SearchString + '' + ' GROUP BY [' + column_name + ']'

    FROM INFORMATION_SCHEMA.COLUMNS

    INNER

    JOINsys.sysobjects

    ONOBJECT_ID(Table_Name) = ID

    WHEREDATA_TYPE in ( 'INT', 'BIT' )

    ANDxtype = 'U'

    ORDER

    BYTABLE_NAME, COLUMN_NAME

    ELSE

    INSERT@SQLqueries

    SELECT 'INSERT ##Results (Tablename, ColumnName, SearchValue, Found_Count) SELECT ''' + TABLE_NAME +''' as Tname, ''' + column_name + ''' as Cname, [' + column_name + '], COUNT(*) FROM [' + TABLE_NAME + '] WHERE CONVERT(VARCHAR(100), [' + column_name + ']) LIKE ''%' + @SearchString + '%''' + ' GROUP BY [' + column_name + ']'

    FROM INFORMATION_SCHEMA.COLUMNS

    INNER

    JOINsys.sysobjects

    ONOBJECT_ID(Table_Name) = ID

    WHEREDATA_TYPE in ( 'INT', 'BIT' )

    ANDxtype = 'U'

    ORDER

    BYTABLE_NAME, COLUMN_NAME

    Select @I = MAX(ID) From @SQLqueries

    While@I > 1

    BEGIN

    Select @sql = SQLstatement From @SQLqueries Where ID = @I

    Exec(@SQL)

    SET@I = @I - 1

    END

    SELECT* From ##Results

    --SELECT * From @SQLqueries

    DROP TABLE ##Results

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    --PROCEDURE END

    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Viewing 10 posts - 31 through 39 (of 39 total)

You must be logged in to reply to this topic. Login to reply