February 6, 2009 at 1:55 pm
Comments posted to this topic are about the item Find Text in all columns of all tables in a Database
June 8, 2009 at 4:56 pm
You helped me out a bunch with this! Thank you much!
August 5, 2009 at 7:51 am
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!
August 28, 2009 at 7:33 am
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 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.
May 17, 2016 at 6:45 am
Thanks for the script.
May 17, 2016 at 6:46 am
...I'm hoping this will be very useful. Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply