Re: How to Interrogate SQL Server Tables for Specific Values - Here's how to do it in MS Access

Erland Sommarskog (esquel@xxxxxxxxxxxxx) writes:
robboll (robboll@xxxxxxxxxxx) writes:
When I run it using SQL Server 2005 with a SQL Server 2000 database.
...
3: Execute.
Result:

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'MAX'.
Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near 'APPLY'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'FOR'.
Msg 137, Level 15, State 2, Line 15
Must declare the variable '@sql'.

Change the compatibility level to 90 with sp_dbcmptlevel. Change it back
to 80 when you are done.


Or run the query from another database, just adding the database name:

DECLARE @sql nvarchar(MAX)

SELECT @sql = (SELECT ' SELECT ' + quotename(o.name, '''') + ', *' +
' FROM yourdb.dbo.' + quotename(o.name) +
' WHERE @mystring IN (' + cl.collist + 'NULL);'
AS [text()]
FROM yourdb.sys.objects o
CROSS APPLY (SELECT c.name + ', ' AS [text()]
FROM yourdb.sys.columns c
WHERE c.object_id = o.object_id
AND type_name(c.system_type_id)
LIKE '%char%'
FOR XML PATH('')) AS cl(collist)
WHERE o.type = 'U'
AND cl.collist IS NOT NULL
FOR XML PATH(''))
EXEC sp_executesql @sql, N'@mystring nvarchar(200)', @mystring = N'ALFKI'




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages