Re: How to Interrogate SQL Server Tables for Specific Values - Here"s
am 30.03.2008 23:52:45 von robbollOn Mar 30, 12:51=A0pm, robboll
> Thanks! =A0I'll check it out. =A0To answer your question about why one
> would need such a routine. =A0I have a situation where I was getting an
> error message "The path cannot be found" when trying to open SQL
> Server 2000. =A0Researing the error I found that that the error message
> was usually associated with ISS. =A0I don't have ISS loaded. =A0Anyway, in=
> the interest of time I uninstalled/reinstalled SQL Server. =A0After
> bringing it back in session the user said that some of his data is
> missing. =A0He provided me with two accounts to search for. =A0One that he=
> can find through his application and one that he cannot -- that he
> knows was entered.
>
> This is a very poor designed database which creates tables daily and
> does not archive anything. =A0As a result it has grown in excess of 65+
> Gig in size. =A0It literally has thousands of tables and the user could
> not tell me the approximate date when the missing values were added.
> That's when I thought of the MS Access routine that I have used in the
> past that works great with MS Access (small DBs). =A0I tried linking to
> the SQL database and running it, but the workstation didn't have
> enough horsepower to do anything with it.
>
> I will try your routine. =A0Will it work with SQL Server 2000? =A0This
> looks great! =A0Thanks for your help.
>
> RBolling
When I run it using SQL Server 2005 with a SQL Server 2000 database.
Steps:
1: Open SQL Server 2005 and connect to the database.
2: Right-Click the database, select New Query and past the code:
DECLARE @sql nvarchar(MAX)
SELECT @sql =3D (SELECT ' SELECT ' + quotename(o.name, '''') + ', *' + '
FROM ' + quotename(o.name) + ' WHERE @mystring IN (' + cl.collist +
'NULL);'
AS [text()]
FROM sys.objects o
CROSS APPLY (SELECT c.name + ', ' AS [text()]
FROM sys.columns c
WHERE c.object_id =3D o.object_id
AND type_name(c.system_type_id)
LIKE '%char%'
FOR XML PATH('')) AS cl(collist)
WHERE o.type =3D 'U'
AND cl.collist IS NOT NULL
FOR XML PATH(''))
EXEC sp_executesql @sql, N'@mystring nvarchar(200)', @mystring =3D
N'abc123'
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'.
RBollinger