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

  • HI ALL,

    As i Have a task to find 9 digit value in all column of all table in a DB or All DB in SQL server 2005.

    SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?

    All Help is appreciated.

  • Ivan Mohapatra (1/9/2012)


    HI ALL,

    As i Have a task to find 9 digit value in all column of all table in a DB or All DB in SQL server 2005.

    SO DO ANY ONE HAVE THE T-SQL STATEMENT TO FIND IT?

    All Help is appreciated.

    You'll need to use dynamic SQL to query for the character columns, then in a cursor loop through those columns looking for the correct values.

  • hi u mean by using DMV query can u just Send me the query so that i can go through it and find the query

  • hi you may use the Len variable.

    select employeeID, EmployeeName, EmployeeTask from Employees

    where Len(employeedID) = 9

    this will post only all employeeID whos lenght is 9 eg.(000333444)

    hope this helps πŸ™‚

    cheers! πŸ˜€

    ===============================================================

    "lets do amazing" our company motto..

  • in addition you may use this script.. πŸ™‚

    you may want to run this per database lol.. haha just change the value to what you want to search lol.. you get the idea? just tweek it some more and you might be able to modify it to the one you need. this will return the table where it was located.

    EXEC SearchAllTables 'Computer'

    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'

    ANDQUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName

    ANDOBJECTPROPERTY(

    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)

    ANDTABLE_NAME= PARSENAME(@TableName, 1)

    ANDDATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

    ANDQUOTENAME(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

    ===============================================================

    "lets do amazing" our company motto..

  • i don'nt know the table & column

    i just want to find table name and column name where data len = 9

    can any one ping the script PLZ

  • i don't know the table name and column name So this query will not match my requirement.

  • mrdenny can u send me the script

  • You need to find table and column names for any table that contains a numeric value that is exactly 9 digits?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • yes exactly i want all the column name and table name where value is 9 character

  • What have you tried? It looks like the script posted above would be a pretty good starting point. That could be modified pretty easily to accomplish what you are after.

    I have to ask, this is a rather strange request. What is the reason behind finding such information?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Untested, but something like this should work

    BEGIN TRAN

    DECLARE @SQL AS VARCHAR(MAX)

    SELECT @SQL = COALESCE(@SQL,'') + 'SELECT ' + c.name + ', '''+c.name+''' AS columnName FROM ' + o.name + ' WHERE LEN('+c.name+')=9;' + CHAR(10)

    FROM sys.objects o

    INNER JOIN sys.columns c ON o.object_id = c.object_id

    INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

    WHERE o.type = 'U' AND

    t.name IN ('tinyint','smallint','int','real','money','float','decimal','numeric','smallmoney','bigint')

    EXEC (@SQL)

    ROLLBACK


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • because in our db there are table with wrong datatype.So i wan to find it by this means to complete a task

  • Cadavre (1/9/2012)


    Untested, but something like this should work

    BEGIN TRAN

    DECLARE @SQL AS VARCHAR(MAX)

    SELECT @SQL = COALESCE(@SQL,'') + 'SELECT ' + c.name + ', '''+c.name+''' AS columnName FROM ' + o.name + ' WHERE LEN('+c.name+')=9;' + CHAR(10)

    FROM sys.objects o

    INNER JOIN sys.columns c ON o.object_id = c.object_id

    INNER JOIN sys.types t ON c.system_type_id = t.system_type_id

    WHERE o.type = 'U' AND

    t.name IN ('tinyint','smallint','int','real','money','float','decimal','numeric','smallmoney','bigint')

    EXEC (@SQL)

    ROLLBACK

    Let me try this but not sure it will work

  • Agree with Sean. It’s not like regular requirements that we get from management. For instance, if a table has 2 rows and one of the columns have 123456789 and 12345678 values. Would you consider this table and column as desired output?

    What do you mean by wrong data type? Oversized columns? Don’t have data model of your database to figure it out? It would be a nice option, if you have.

Viewing 15 posts - 1 through 15 (of 39 total)

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