String Search

I like the idea of this but the only issue I have is this, If I want to run this against our production warehouse I'm not going to have rights to create a stored procedure.

Maybe later I'll look into modifying it to not be a stored procedure for what I will need.

Thanks again for the base code at least.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

peter.mauss SSC Journeyman Points: 82 More actions · Answer 5

Yes, it displays the

object_name, the column_name for each hit. It would be cool if it would also Display

the Name of the Primary key column for the object and its value so a further query could be formed later on.

Something like "select [pkey_name],[column_name] from [object_name] where [pkey_name]=[pkey_value]"

Sean Smith SSCertifiable Points: 5982 More actions · Answer 6

below86 (10/22/2014)
I like the idea of this but the only issue I have is this, If I want to run this against our production warehouse I'm not going to have rights to create a stored procedure.
Maybe later I'll look into modifying it to not be a stored procedure for what I will need.
Thanks again for the base code at least.

Good point. I'm used to having "god" access but I see what you're saying. If you do mod it feel free to post it back here for others if you want. 🙂

Sean Smith SSCertifiable Points: 5982 More actions · Answer 7

peter.mauss (10/22/2014)
Yes, it displays the
object_name, the column_name for each hit. It would be cool if it would also Display
the Name of the Primary key column for the object and its value so a further query could be formed later on.
Something like "select [pkey_name],[column_name] from [object_name] where [pkey_name]=[pkey_value]"

Gotcha. The problem is that if it's a view or a table which is a heap then there's no PK. I usually just query the table in question against the column were the result was found using the value returned in the "column_data" column.

peter.mauss SSC Journeyman Points: 82 More actions · Answer 8

Sean Smith-776614 (10/22/2014)
peter.mauss (10/22/2014)
Yes, it displays the
object_name, the column_name for each hit. It would be cool if it would also Display
the Name of the Primary key column for the object and its value so a further query could be formed later on.
Something like "select [pkey_name],[column_name] from [object_name] where [pkey_name]=[pkey_value]"
Gotcha. The problem is that if it's a view or a table which is a heap then there's no PK. I usually just query the table in question against the column were the result was found using the value returned in the "column_data" column.

OK, i see. Tanks anyway for thist script..

below86 SSChampion Points: 11534 More actions · Answer 9

Sean Smith-776614 (10/22/2014)
below86 (10/22/2014)
I like the idea of this but the only issue I have is this, If I want to run this against our production warehouse I'm not going to have rights to create a stored procedure.
Maybe later I'll look into modifying it to not be a stored procedure for what I will need.
Thanks again for the base code at least.
Good point. I'm used to having "god" access but I see what you're saying. If you do mod it feel free to post it back here for others if you want. 🙂

Yes, we all can't be "god's". 🙂 But if needed I know how I can get the "god" like power.

If I can carve out some time later I'll play with it, and post what I get to work.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Sean Smith SSCertifiable Points: 5982 More actions · Answer 10

below86 (10/22/2014)
Sean Smith-776614 (10/22/2014)
below86 (10/22/2014)
I like the idea of this but the only issue I have is this, If I want to run this against our production warehouse I'm not going to have rights to create a stored procedure.
Maybe later I'll look into modifying it to not be a stored procedure for what I will need.
Thanks again for the base code at least.
Good point. I'm used to having "god" access but I see what you're saying. If you do mod it feel free to post it back here for others if you want. 🙂
Yes, we all can't be "god's". 🙂 But if needed I know how I can get the "god" like power.
If I can carve out some time later I'll play with it, and post what I get to work.

Awesome and thanks! 🙂

Sean Smith SSCertifiable Points: 5982 More actions · Answer 11

peter.mauss (10/22/2014)
Sean Smith-776614 (10/22/2014)
peter.mauss (10/22/2014)
Yes, it displays the
object_name, the column_name for each hit. It would be cool if it would also Display
the Name of the Primary key column for the object and its value so a further query could be formed later on.
Something like "select [pkey_name],[column_name] from [object_name] where [pkey_name]=[pkey_value]"
Gotcha. The problem is that if it's a view or a table which is a heap then there's no PK. I usually just query the table in question against the column were the result was found using the value returned in the "column_data" column.
OK, i see. Tanks anyway for thist script..

No problem. Hope you find it handy.

stevemc Right there with Babe Points: 797 More actions · Answer 12

WRT compiling this procedure on production servers, would it be possible to run this as a temp sproc on that system?

IF OBJECT_ID (N'tempdb..#usp_String_Search', N'P') IS NULL

BEGIN

EXECUTE ('CREATE PROCEDURE #usp_String_Search AS SELECT 1 AS shell')

END

...

ALTER PROCEDURE #usp_String_Search

...

exec #usp_String_Search

@Search_String = 'spoke'--AS NVARCHAR (500)

,@Database_Name = 'AdventureWorks2012'--AS NVARCHAR (300)

,@Object_Types = 'U'--AS NVARCHAR (10) = NULL

Not sure what kind of 'god' powers this would need, but this works on my test system (on which I am lord and ruler).

Sean Smith SSCertifiable Points: 5982 More actions · Answer 13

stevemc (10/22/2014)
WRT compiling this procedure on production servers, would it be possible to run this as a temp sproc on that system?
IF OBJECT_ID (N'tempdb..#usp_String_Search', N'P') IS NULL
BEGIN
EXECUTE ('CREATE PROCEDURE #usp_String_Search AS SELECT 1 AS shell')
END
...
ALTER PROCEDURE #usp_String_Search
...
exec #usp_String_Search
@Search_String = 'spoke'--AS NVARCHAR (500)
,@Database_Name = 'AdventureWorks2012'--AS NVARCHAR (300)
,@Object_Types = 'U'--AS NVARCHAR (10) = NULL
Not sure what kind of 'god' powers this would need, but this works on my test system (on which I am lord and ruler).

Actually, I believe that should work. 🙂

below86 SSChampion Points: 11534 More actions · Answer 14

OK this isn't the same SQL, this is something I got back in March, probably from this site, didn't keep who wrote it, sorry about that, I'm trying to do better. 🙂

This SQL you need to be connected to the database you want it to run against.

DECLARE @Search_String nvarchar(100);

SET @Search_String = 'CHIROPRACTOR';

CREATE TABLE #Results

(

Column_Name nvarchar(370),

Column_Value nvarchar(3630)

)

;

SET NOCOUNT ON;

DECLARE @Table_Name nvarchar(256);

DECLARE @Column_Name nvarchar(128);

DECLARE @Search_String2 nvarchar(110);

SET @Table_Name = '';

SET @Search_String2 = QUOTENAME('%' + @Search_String + '%','''');

WHILE @Table_Name IS NOT NULL

BEGIN

SET @Column_Name = ''

SET @Table_Name = (SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

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

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)

WHILE (@Table_Name IS NOT NULL) AND (@Column_Name IS NOT NULL)

BEGIN

SET @Column_Name = (SELECT MIN(QUOTENAME(COLUMN_NAME))

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = PARSENAME(@Table_Name, 2)

AND TABLE_NAME = PARSENAME(@Table_Name, 1)

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

AND QUOTENAME(COLUMN_NAME) > @Column_Name )

IF @Column_Name IS NOT NULL

BEGIN

INSERT INTO #Results

EXEC ('SELECT ''' + @Table_Name + '.' + @Column_Name + ''', LEFT(' + @Column_Name + ', 3630) FROM ' + @Table_Name + ' (NOLOCK) ' +

' WHERE ' + @Column_Name + ' LIKE ' + @Search_String2)

END

SELECT Column_Name, Column_Value

FROM #Results

GROUP BY Column_Name, Column_Value

ORDER BY Column_Name, Column_Value

--DROP TABLE #Results

I'll post the original SQL later. I'm having a hard time getting either version to run very fast.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Sean Smith SSCertifiable Points: 5982 More actions · Answer 15

Yeah, text searches can run REALLY slow. Did you try using some of the input variables from my proc? It tends to help if you know column length, number of rows in the table(s) you want to search, etc.