SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Query Help Needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jamaral141989
Starting Member

6 Posts

Posted - 09/18/2013 :  12:06:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 09/18/2013 :  12:50:09  Show Profile  Reply with Quote
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 - 09/18/2013 :  13:21:16  Show Profile  Reply with Quote
That doesn't seem to be working, any other ideas?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 09/18/2013 :  13:56:55  Show Profile  Reply with Quote
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.

Edited by - James K on 09/18/2013 13:57:20
Go to Top of Page

Jamaral141989
Starting Member

6 Posts

Posted - 09/18/2013 :  15:20:09  Show Profile  Reply with Quote
I'm getting syntax errors at step 9.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30117 Posts

Posted - 09/18/2013 :  15:55:30  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 09/18/2013 16:05:56
Go to Top of Page

Jamaral141989
Starting Member

6 Posts

Posted - 09/18/2013 :  18:16:55  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000