String Search

  • Sean Smith

    SSCertifiable

    Points: 5903

    Comments posted to this topic are about the item String Search

  • Eric Hu²º¹²

    Mr or Mrs. 500

    Points: 506

    very very good.

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

  • Sean Smith

    SSCertifiable

    Points: 5903

    Thank you. I hope you find it useful. 🙂

  • Eric Hu²º¹²

    Mr or Mrs. 500

    Points: 506

    no problem.

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

  • dost.kumar2

    SSC Enthusiast

    Points: 106

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

  • Sean Smith

    SSCertifiable

    Points: 5903

    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]

  • dost.kumar2

    SSC Enthusiast

    Points: 106

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

    -----------

  • Sean Smith

    SSCertifiable

    Points: 5903

    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]

  • Leon . Orlov .

    Ten Centuries

    Points: 1152

    nice little proc. thanks for sharing.

  • Sean Smith

    SSCertifiable

    Points: 5903

    My pleasure. Hope you find it handy.

  • itisme_fred

    SSC Enthusiast

    Points: 185

    The procedure does not search primary key content.

  • Sean Smith

    SSCertifiable

    Points: 5903

    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'

  • itisme_fred

    SSC Enthusiast

    Points: 185

    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

  • Sean Smith

    SSCertifiable

    Points: 5903

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

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

  • albertus.vandenberg

    SSC Journeyman

    Points: 89

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

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