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.