String Search

Question

String Search

Sean Smith

SSCertifiable

Points: 6016
More actions
April 7, 2012 at 11:48 am

#258898

Comments posted to this topic are about the item String Search

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

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

Eric Hu²º¹² Mr or Mrs. 500 Points: 506 More actions · Answer 1

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: 6016 More actions · Answer 2

Sean Smith

SSCertifiable

Points: 6016

April 9, 2012 at 7:25 pm

#1471027

Thank you. I hope you find it useful.

Eric Hu²º¹² Mr or Mrs. 500 Points: 506 More actions · Answer 3

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 More actions · Answer 4

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

Sean Smith SSCertifiable Points: 6016 More actions · Answer 5

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 More actions · Answer 6

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: 6016 More actions · Answer 7

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: 1212 More actions · Answer 8

Leon . Orlov .

Ten Centuries

Points: 1212

July 2, 2012 at 1:17 pm

#1507905

nice little proc. thanks for sharing.

Sean Smith SSCertifiable Points: 6016 More actions · Answer 9

Sean Smith

SSCertifiable

Points: 6016

July 2, 2012 at 4:52 pm

#1507973

My pleasure. Hope you find it handy.

itisme_fred SSC Enthusiast Points: 185 More actions · Answer 10

itisme_fred

SSC Enthusiast

Points: 185

July 16, 2013 at 5:26 pm

#1633046

The procedure does not search primary key content.

Sean Smith SSCertifiable Points: 6016 More actions · Answer 11

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 More actions · Answer 12

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: 6016 More actions · Answer 13

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 More actions · Answer 14

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.

String Search

Cookies on SQLServerCentral