I'm trying to query all the tables and all their columns in a db for a particular search string. I got the example below from someone elses post, but I get an "invalid object name 'sys.tables' along with other errors related to anything referencing sys. in the query . Here's the example I was trying to use in query analyzer:
USE AdventureWorks
DECLARE @find_this VARCHAR(50)
SET @find_this = 'Adventure'
SELECT
'SELECT [' + C.[name] + '] FROM [' + S.[name] + '].[' + tbl.[name] + '] WHERE [' + C.[name] + '] LIKE ' + char(39) + '%' + @find_this + '%' + char(39)
FROM sys.tables tbl
INNER JOIN sys.schemas S ON tbl.schema_id = S.schema_id
INNER JOIN sys.columns C ON C.object_id = tbl.Object_id
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE T.[name] LIKE '%varchar%'
order by tbl.[name], C.column_id
Start Free Trial
