Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help Needed

Author  Topic 

Jamaral141989
Starting Member

6 Posts

Posted - 2013-09-18 : 12:06:29
Hello,

I'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 12:50:09
See if this will help you: http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspx
Go to Top of Page

Jamaral141989
Starting Member

6 Posts

Posted - 2013-09-18 : 13:21:16
That doesn't seem to be working, any other ideas?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 13:56:55
What is it doing? Syntax errors, or giving your the wrong results, or no results at all?

Redgate and others have search products - some of them free. You might want to look up those. Redgate product is free. http://www.red-gate.com/products/sql-development/sql-search/ But not sure if it is data search or object search though.
Go to Top of Page

Jamaral141989
Starting Member

6 Posts

Posted - 2013-09-18 : 15:20:09
I'm getting syntax errors at step 9.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 15:55:30
Try this
-- Prepare user supplied parameter value
DECLARE @SearchWord NVARCHAR(100) = 'A Phrase To Search For';

-- Stage columns
DECLARE @Columns TABLE
(
ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
TableSchema SYSNAME NOT NULL,
TableName SYSNAME NOT NULL,
ColumnList NVARCHAR(MAX) NOT NULL
);

INSERT @Columns
(
TableSchema,
TableName,
ColumnList
)
SELECT t.TABLE_SCHEMA AS TableSchema,
t.TABLE_NAME AS TableName,
STUFF(c.ColumnList, 1, 1, '') AS ColumnList
FROM (
SELECT TABLE_SCHEMA,
TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
) AS t
CROSS APPLY (
SELECT ',' + QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS AS x
WHERE x.TABLE_SCHEMA = t.TABLE_SCHEMA
AND x.TABLE_NAME = t.TABLE_NAME
AND x.DATA_TYPE IN ('TEXT', 'NTEXT', 'VARCHAR', 'CHAR', 'NVARCHAR', 'NCHAR')
FOR XML PATH('')
) AS c(ColumnList)
WHERE c.ColumnList > ''
ORDER BY t.TABLE_SCHEMA,
t.TABLE_NAME;

DECLARE @CurrID INT = 1,
@StopID INT = (SELECT MAX(ID) FROM @Columns),
@SQL NVARCHAR(MAX);

WHILE @CurrID <= @StopID
BEGIN
SELECT @SQL = N'SELECT N' + QUOTENAME(TableSchema + N'.' + Tablename, N'''') + N' AS TableName, *'
+ N' FROM ' + QUOTENAME(TableSchema) + N'.' + QUOTENAME(TableName)
+ N' WHERE N' + QUOTENAME(@SearchWord, N'''') + N' IN (' + ColumnList + N');'
FROM @Columns
WHERE ID = @CurrID;

PRINT @SQL;

EXEC (@SQL);

SET @CurrID += 1;
END;


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Jamaral141989
Starting Member

6 Posts

Posted - 2013-09-18 : 18:16:55
When I try the first line I got a
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Go to Top of Page
   

- Advertisement -