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

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    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.

  • mrdenny

    SSCrazy

    Points: 2898

    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.

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    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

  • jnuqui

    Default port

    Points: 1445

    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..

  • jnuqui

    Default port

    Points: 1445

    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'

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

    AND OBJECTPROPERTY(

    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)

    AND TABLE_NAME = PARSENAME(@TableName, 1)

    AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')

    AND QUOTENAME(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..

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    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

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

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

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    mrdenny can u send me the script

  • Sean Lange

    SSC Guru

    Points: 286536

    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/

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

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

  • Sean Lange

    SSC Guru

    Points: 286536

    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/

  • Cadavre

    SSC-Forever

    Points: 41690

    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/

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

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

  • Ivan Mohapatra

    SSCarpal Tunnel

    Points: 4452

    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

  • Devendra (Dev) Shirbad

    SSC-Forever

    Points: 42493

    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 40 total)

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