String Search

  • Comments posted to this topic are about the item String Search

  • very very good.

    --The confidence is a premise, the courage is a motive, the perseverance is assurance!
    http://www.cnblogs.com/huyong/[/url]

  • Thank you. I hope you find it useful. 🙂

  • no problem.

    --The confidence is a premise, the courage is a motive, the perseverance is assurance!
    http://www.cnblogs.com/huyong/[/url]

  • Hello any one can help me on. i want to search particular one table.

  • You can modify the code temporarily by adding at line 232:

    AND O.name = 'MY_TABLE_NAME'

    So something like this:

    FROM

    '+@v_Database_Name+N'.[sys].[schemas] S

    INNER JOIN '+@v_Database_Name+N'.[sys].[objects] O ON O.[schema_id] = S.[schema_id]

    AND O.[type] IN ('+(CASE

    WHEN @v_Object_Types IS NOT NULL THEN @v_Object_Types

    ELSE N'''U'', ''V'''

    END)+N')

    AND O.name = ''MY_TABLE_NAME''

    INNER JOIN '+@v_Database_Name+N'.[sys].[columns] C ON C.[object_id] = O.[object_id]

  • Thanks for help me..

    my table name contain schema name like 'abc.tablename'

    when i add table name in my procedure it execute successfully after that run that procedure it's give me error

    Invalid column name 'abc.tablename'.

    please help me on..

    can u send me Change procedure..

    -----------

  • If you need to include the schema, then something like this should do the trick:

    FROM

    '+@v_Database_Name+N'.[sys].[schemas] S

    INNER JOIN '+@v_Database_Name+N'.[sys].[objects] O ON O.[schema_id] = S.[schema_id]

    AND O.[type] IN ('+(CASE

    WHEN @v_Object_Types IS NOT NULL THEN @v_Object_Types

    ELSE N'''U'', ''V'''

    END)+N')

    AND O.name = ''MY_TABLE_NAME''

    AND S.name = ''MY_SCHEMA_NAME''

    INNER JOIN '+@v_Database_Name+N'.[sys].[columns] C ON C.[object_id] = O.[object_id]

  • nice little proc. thanks for sharing.

  • My pleasure. Hope you find it handy.

  • The procedure does not search primary key content.

  • Yes it does:

    USE tempdb

    CREATE TABLE test (somevarchar VARCHAR (100) PRIMARY KEY CLUSTERED)

    INSERT INTO test VALUES ('does it seach PK')

    EXEC "database the proc is in".dbo.usp_String_Search

    @v_Search_String = 'does it seach PK'

    ,@v_Database_Name = 'tempdb'

  • I re-created the stored procedure and it is working now, but I cannot get it working with INT.

    USE tempdb

    CREATE TABLE test (LoanNum VARCHAR (10) PRIMARY KEY CLUSTERED, OfficeID INT )

    INSERT INTO test VALUES ('100001', 1)

    INSERT INTO test VALUES ('100002', 2)

    INSERT INTO test VALUES ('100003', 3)

    EXEC DBAdmin.dbo.usp_String_Search

    @v_Search_String = '2'

    ,@v_Database_Name = 'tempdb'

    ,@v_Data_Types = N'INT'

    DROP TABLE TEST

  • As mentioned in the article it only checks against the following data types:

    CHAR, NCHAR, NTEXT, NVARCHAR, TEXT, VARCHAR, and XML

  • Thanks a million.

    I have one that search only one data type, but I could search for a list of values. This one will fill the gap for me.

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

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