|
|
|
SSC Rookie
 
Group: General Forum Members
Last Login: Thursday, January 14, 2010 6:15 AM
Points: 28,
Visits: 66
|
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, June 08, 2009 5:49 PM
Points: 1,
Visits: 1
|
|
| You helped me out a bunch with this! Thank you much!
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, November 03, 2009 11:09 PM
Points: 192,
Visits: 151
|
|
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!
|
|
|
|
|
Forum Newbie
Group: General Forum Members
Last Login: Monday, October 26, 2009 9:56 PM
Points: 1,
Visits: 100
|
|
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.
|
|
|
|
