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

Find Text in all columns of all tables in a Database Expand / Collapse
Author
Message
Posted Friday, February 6, 2009 1:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:57 AM
Points: 30, Visits: 111
Comments posted to this topic are about the item Find Text in all columns of all tables in a Database
Post #651976
Posted Monday, June 8, 2009 4:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 8, 2009 5:49 PM
Points: 1, Visits: 1
You helped me out a bunch with this! Thank you much!
Post #731026
Posted Wednesday, August 5, 2009 7:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:06 AM
Points: 217, Visits: 172
i was really hoping that i would find an article on this subject in SQLServerCentral and thank goodness i found your article, great!
you just saved me many hours of hunting!

excellent post!
Post #765457
Posted Friday, August 28, 2009 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 1, 2012 5:04 AM
Points: 1, Visits: 110
I made some minor changes to the code since i had two problems. First, if the schema where not dbo it did not work and if the text i searched for where to long or to long in an varchar field it did not find anything: The changed code looks like this:

set nocount on
DECLARE @TEXT VARCHAR(500)
SET @TEXT = 'test nisse, test malte, hejsan på dig du galde . vad gör dunu då'
DECLARE @TABLES TABLE([id] INT IDENTITY(1,1), TableName VARCHAR(500),
SchemaName varchar(255),ColumnName VARCHAR(500))
INSERT INTO @TABLES(TableName, SchemaName,ColumnName)
SELECT O.[NAME], ss.name,C.[NAME]
FROM SYSOBJECTS O
JOIN SYSCOLUMNS C
ON C.ID = O.ID
join sys.objects SO
on so.object_id=o.id
join sys.schemas SS
on ss.schema_id= so.schema_id
WHERE O.XTYPE = 'U'
AND C.XTYPE NOT IN
(
127 --bigint
, 173 --binary
, 104 --bit
, 61 --datetime
, 106 --decimal
, 62 --float
, 34 --image
, 56 --int
, 60 --money
, 108 --numeric
, 59 --real
, 58 --smalldatetime
, 52 --smallint
, 122 --smallmoney
, 189 --timestamp
, 48 --tinyint
, 36 --uniqueidentifier
, 165 --varbinary
)
ORDER BY O.[NAME], C.[NAME]
IF EXISTS (SELECT NAME FROM TEMPDB.DBO.SYSOBJECTS
WHERE NAME LIKE '#TMPREPORT%')
BEGIN
DROP TABLE #TMPREPORT
END
CREATE TABLE #TMPREPORT(COUNTER INT, TABLENAME VARCHAR(500),
COLUMNNAME VARCHAR(500))
DECLARE @CNTR bigINT, @POS bigINT, @TableName VARCHAR(500),
@SchemaName varchar(255),@ColumnName VARCHAR(500), @SQL VARCHAR(8000)
SELECT @POS = 1, @CNTR = MAX([ID]), @TableName = '', @ColumnName = ''
FROM @TABLES

WHILE @POS <= @CNTR
BEGIN
SELECT @TableName = TableName, @schemaname = schemaname,@ColumnName = ColumnName
FROM @TABLES
WHERE [ID] = @POS

SELECT @SQL = 'SELECT COUNT(*), ''' + @schemaname + '.' + @TABLENAME + ''' [TABLE],''' + @COLUMNNAME + '''[COLUMN] FROM ' + @schemaname + '.' + @TableName + ' WHERE CAST(' + @ColumnName + ' AS NVARCHAR(4000)) LIKE N''%' + @TEXT + '%'''
BEGIN TRY
INSERT INTO #TMPREPORT(COUNTER, TABLENAME, COLUMNNAME)
EXEC(@SQL)

END TRY
BEGIN CATCH
PRINT @@ERROR
PRINT @SQL
END CATCH
SELECT @POS = @POS + 1
END

SELECT * FROM #TMPREPORT
WHERE COUNTER > 0
DROP TABLE #TMPREPORT

Thanks for the code! Really nice and it did the trick fo rme when trying to figure out how VM ware really link their tables.



Post #779079
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse