Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Search anything anywhere Expand / Collapse
Author
Message
Posted Wednesday, June 5, 2013 7:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 24, 2014 2:02 AM
Points: 71, Visits: 17
I use this one (cant remember where I found it though):


CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO

SET NOCOUNT ON

DECLARE @SQL varchar(8000), @TABLE_NAME sysname, @COLUMN_NAME sysname, @Sargable varchar(80), @Count int

SELECT @Sargable = 'enter word or words here'

DECLARE insaneCursor CURSOR FOR
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext')
AND t.TABLE_TYPE = 'BASE TABLE'

OPEN insaneCursor

FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
+ '''' + @TABLE_NAME + '''' + ','
+ '''' + @COLUMN_NAME + '''' + ','
+ 'COUNT(*) FROM [' + @TABLE_NAME
+ '] WHERE [' + @COLUMN_NAME + '] Like '
+ ''''+ '%' + @Sargable + '%' + ''''
--SELECT @SQL
EXEC(@SQL)
IF @@ERROR <> 0
BEGIN
SELECT @SQL
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TABLE_NAME
GOTO Error
END
FETCH NEXT FROM insaneCursor INTO @TABLE_NAME, @COLUMN_NAME
END

SELECT * FROM myTable99 WHERE Occurs <> 0


Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

DROP TABLE myTable99
GO

SET NOCOUNT OFF
Post #1460238
Posted Wednesday, June 5, 2013 1:02 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:40 AM
Points: 903, Visits: 257
Thanks for this!
I've been using a similar script, and I was wondering if it would be possible to include data in the same row as the data found? In other words, I have a 'date_created' column on most tables, and I would like to return that as well as the search result, in order to sort by date created.

Is this possible?

Thanks
Post #1460392
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse