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

  • HI all,

    Well the point of discussion is to have a script by which i can get all the column name and table name in a db where the data length is 9 digit and the datatype should be varchar,int.AS because we have a ID column with datatype as varchar & Int in different table.

    So as this is a rarer request in any IT forum IT will be a nice one for all to learn it from the giants how it is done.

    So kindly help me out for which i will be very thankful to u all.

  • Dev (1/9/2012)


    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.

    hope it is you ( DEV PINAL) I Am a great Fan Of Your SKill.

    yes I would consider that as desired out put.Because we don't have such data So As ID column has 9 digit only in my entire DB.

  • Ivan Mohapatra (1/9/2012)


    Well the point of discussion is to have a script by which i can get all the column name and table name in a db where the data length is 9 digit

    data length or data type?

    and the datatype should be varchar,int.AS because we have a ID column with datatype as varchar & Int in different table.

    The sentence (or fragment) above doesn't make any sense. What is "varchar,int.as"? Yo need to slow down and write a description that makes sense.

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


    Ivan Mohapatra (1/9/2012)


    Well the point of discussion is to have a script by which i can get all the column name and table name in a db where the data length is 9 digit

    data length or data type? -Data length

    and the datatype should be varchar,int.AS because we have a ID column with datatype as varchar & Int in different table.

    The sentence (or fragment) above doesn't make any sense. What is "varchar,int.as"? Yo need to slow down and write a description that makes sense.

    Below is a Example what i mean to say and my kind request kindly read my request seriously and understand it before u all reply.

    Well there are 60 table in 1 Db there is a ID column in where there are 9 digit data.like 999999999,888888888

    well in this 60 table there is around 20 column where 9 digit data.like 999999999,888888888 exits AND when i found there is two kind of datatype is been used 1 is varchar and another is INT for this ID column in different tables. i mean in one table the datatype for ID column is varchar and in another table ID column is INT.

    So What i want is a script by which i can get all the table name and column name in this DB

    where len of Data is 9 digit data.like 999999999,888888888 .

  • OK we are now 20 posts into your request and the details of what you want is still changing with each post. We can't see over your shoulder, we are not familiar with your system or what you want to retrieve out of it. There have been a number of people trying to help. You have to help us help you. Give us a detailed explanation of what you want.

    So What i want is a script by which i can get all the table name and column name in this DB

    where len of Data is 9 digit data.like 999999999,888888888 .

    What does that mean? Do you want only varchar fields that a numeric value that has a length of 9? Does it count as a match if the column has other data?

    Taking the first script posted you could change the where clause find what you are looking for.

    pure pseudocode but...

    where datalength(column) = 9 and isnumeric(coliumn) = 1

    If you explain clearly (yes we know it is clear to you) in a way that somebody else can understand it, we will be happy to help you.

    _______________________________________________________________

    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/

  • Sean Lange (1/9/2012)


    OK we are now 20 posts into your request and the details of what you want is still changing with each post. We can't see over your shoulder, we are not familiar with your system or what you want to retrieve out of it. There have been a number of people trying to help. You have to help us help you. Give us a detailed explanation of what you want.

    So What i want is a script by which i can get all the table name and column name in this DB

    where len of Data is 9 digit data.like 999999999,888888888 .

    What does that mean? Do you want only varchar fields that a numeric value that has a length of 9? Does it count as a match if the column has other data?

    Taking the first script posted you could change the where clause find what you are looking for.

    pure pseudocode but...

    where datalength(column) = 9 and isnumeric(coliumn) = 1

    If you explain clearly (yes we know it is clear to you) in a way that somebody else can understand it, we will be happy to help you.

    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

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

    this is what i want and

  • A couple of things. Simply repeating a vague and non-descript explanation is not going to make it clear for anyone. It is obvious that you are getting frustrated. This is professional forum and as such we treat each other professionally. That means that you don't need to yell at anyone. Also, simply demanding that people provide the script is not going to happen. We are more than happy to share when it seems the person on the other end is trying to learn. Most people around here are reluctant to just hand over the goods to somebody who doesn't want to put in the effort for themselves. We are all volunteers around here and don't want to spend much time doing work for somebody else to reap the rewards. On the other hand, those of us who post around here do so because we truly enjoy passing on the knowledge we have gained through other people. Tutoring is very rewarding, being a consultant with no pay is not.

    Here is a script I wrote a long time ago to find certain values throughout the database. It is not exactly what you are looking for but should prove to work for your needs with some pretty simple modification.

    declare @table_name varchar(2000)

    declare @sSQL nvarchar(4000)

    declare @result varchar(20)

    declare @column_name varchar(2000)

    declare @SearchVal varchar(200)

    set @SearchVal = '%your search val here%'

    declare @ColName varchar (250)

    set @ColName = '%use this if you want to limit to a naming convention on the columns to search (i.e. email)%'

    declare SearchList cursor for

    select distinct so.name,sc.name from syscolumns sc

    inner join sysobjects so on sc.id = so.id

    where sc.name like @ColName

    and so.type = 'U'

    open SearchList

    fetch next from SearchList into @table_name, @column_name

    while(@@fetch_status = 0)

    begin

    select @sSQL = 'if exists (select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''') select ''' + @table_name + ''' as TableName,' + @column_name + ' from ' + @table_name + ' where ' + @column_name + ' like ''' + @SearchVal + ''''

    exec sp_executesql @sSQL

    --select @ssql

    fetch next from SearchList into @table_name, @column_name

    end

    close SearchList

    deallocate SearchList

    You will just need to tweak the where clauses and you should be able to get this to work. I would warn you about running this in production because the performance is totally horrible. Given that you have to search each and every column and row in the entire database it is not possible to make this fast. I would run this against a restored copy of production instead of crippling your production box.

    _______________________________________________________________

    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/

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

  • This SQL query will help to find table name and column name in database which column value length is 9.

    DECLARE @Tab_Name VARCHAR(500)

    DECLARE @Col_Name VARCHAR(500)

    DECLARE @sql NVARCHAR(MAX)

    CREATE TABLE #TMP_Details(TabName VARCHAR(500) NOT NULL,ColName VARCHAR(500) NOT NULL)

    DECLARE CUR_GET_INFO CURSOR FAST_FORWARD FOR

    SELECT DISTINCT SO.name,SC.NAME FROM SYS.OBJECTS SO

    INNER JOIN SYS.COLUMNS SC ON SO.object_id=SC.object_id

    WHERE SO.type='U'

    OPEN CUR_GET_INFO

    FETCH NEXT FROM CUR_GET_INFO INTO @Tab_Name,@Col_Name

    WHILE @@FETCH_STATUS =0

    BEGIN

    SET @sql='IF EXISTS (SELECT 1 FROM '+@Tab_Name+' WHERE LEN('+@Col_Name+')=9)'

    SET @sql=@SQL+'BEGIN INSERT INTO #TMP_Details VALUES('''+@Tab_Name+''','''+@Col_Name+''') END'

    EXEC (@SQL)

    FETCH NEXT FROM CUR_GET_INFO INTO @Tab_Name,@Col_Name

    END

    CLOSE CUR_GET_INFO

    DEALLOCATE CUR_GET_INFO

    SELECT * FROM #TMP_Details

    DROP TABLE #TMP_Details

  • You may also want to try the following code; modified it to what I believe is what you want--I originally created this one to detect all TEXT fields in a database:

    if exists (select * from tempdb..sysobjects where name like '#fieldinfo%')

    begin

    drop table #fieldinfo

    end;

    SelectTABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE

    , CASE WHEN data_type = 'uniqueidentifier' THEN 36

    ELSE isnull(character_maximum_length, isnull(numeric_precision, datetime_precision))

    END as charlength_or_precision

    , isnull(collation_name, isnull(cast(numeric_scale as varchar(10)), 'n.a.')) as collation_or_scale, is_nullable

    into #fieldinfo

    From INFORMATION_SCHEMA.COLUMNS

    WhereDATA_TYPE in ('varchar', 'int')

    and isnull(character_maximum_length, isnull(numeric_precision, datetime_precision)) = 9

    select *

    from #fieldinfo

    Order By TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION

    Let us know if this helped to resolve your issue.

    Regards,

    Michael

  • The script from jnuqui should give you what you need...

  • Ivan Mohapatra (1/9/2012)


    Dev (1/9/2012)


    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.

    hope it is you ( DEV PINAL) I Am a great Fan Of Your SKill.

    yes I would consider that as desired out put.Because we don't have such data So As ID column has 9 digit only in my entire DB.

    It’s a huge complement but I am not the one you are looking for. He is ‘Pinal Dave’ and you can find him on http://blog.sqlauthority.com/. IIRC he does post in his blog ONLY. 2000+ articles till date…

  • Querying the INFORMATION_SCHEMA views can return columns with definitions that matche search criteria, and then you can also use a similar query to dynamically build other SQL queries against those same table columns. I'm not sure what exactly you're requesting, but I think your solution can be derived from the examples below.

    select t.table_schema+'.'+t.table_name table_name, c.column_name,

    c.data_type+'('+cast(c.character_maximum_length as varchar(4))+')' data_type

    from information_schema.tables t

    join information_schema.columns c

    on c.table_name = t.table_name

    where t.table_type = 'base table'

    and c.data_type = 'varchar'

    and c.character_maximum_length >= 9;

    table_namecolumn_namedata_type

    dbo.sysmail_mailitemsbody_formatvarchar(20)

    dbo.sysmail_mailitemssensitivityvarchar(12)

    dbo.sysmail_mailitemsattachment_encodingvarchar(20)

    select 'select max('+c.column_name+') max_'

    +t.table_name+'_'+c.column_name+' from '

    +t.table_schema+'.'+t.table_name+';'

    from information_schema.tables t

    join information_schema.columns c

    on c.table_name = t.table_name

    where t.table_type = 'base table'

    and c.data_type = 'varchar'

    and c.character_maximum_length >= 9;

    select max(body_format) max_sysmail_mailitems_body_format from dbo.sysmail_mailitems;

    select max(sensitivity) max_sysmail_mailitems_sensitivity from dbo.sysmail_mailitems;

    select max(attachment_encoding) max_sysmail_mailitems_attachment_encoding from dbo.sysmail_mailitems;

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • 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')

    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 + ''', SUBSTRING('

    + @ColumnName + ',1, 3630)

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

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

    )

    END

    END

    SELECT ColumnName, ColumnValue FROM #Results where len(ColumnValue)= '9'

    ORDER BY ColumnName

    END

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

    Can any one suggest in this script -well i have modified this script which was provided by

    jnuqui. well i want a int value to be searched but when ever i am adding INT in C.DATA_TYPE IN ('varchar','INT')

    IT is troughs error in the when i pass a Int value to be searched. error as (Invalid argument for string).So can any one modify or suggest how i can fix this and make this work .

  • Dev (1/10/2012)


    Ivan Mohapatra (1/9/2012)


    Dev (1/9/2012)


    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.

    hope it is you ( DEV PINAL) I Am a great Fan Of Your SKill.

    yes I would consider that as desired out put.Because we don't have such data So As ID column has 9 digit only in my entire DB.

    It’s a huge complement but I am not the one you are looking for. He is ‘Pinal Dave’ and you can find him on http://blog.sqlauthority.com/. IIRC he does post in his blog ONLY. 2000+ articles till date…

    OK DEV NO ISSUE IF U CAN HELP ME OUT

    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')

    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 + ''', SUBSTRING('

    + @ColumnName + ',1, 3630)

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

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

    )

    END

    END

    SELECT ColumnName, ColumnValue FROM #Results where len(ColumnValue)= '9'

    ORDER BY ColumnName

    END

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

    Can any one suggest in this script -well i have modified this script which was provided by

    jnuqui. well i want a int value to be searched but when ever i am adding INT in C.DATA_TYPE IN ('varchar','INT')

    IT is troughs error in the when i pass a Int value to be searched. error as (Invalid argument for string).So can any one modify or suggest how i can fix this and make this work .

Viewing 15 posts - 16 through 30 (of 39 total)

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