query does oposite of what i want

I am trying to find all the user tables that are not mentioned in a view or
stored procedure. Instead, this lists each table once for each stored
procedure it is not appearing.
select so2.name,so.name
from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
(so.id=sc.id)
inner join AdminDB.dbo.sysobjects so2 on
(patindex('%so2.name%',sc.text)=0)
where so.xtype in ('P','V')
and so2.xtype ='U'
order by so2.nameGOT IT!
SELECT a.name
FROM AdminDB.dbo.sysobjects a LEFT JOIN (
SELECT so2.name,so.name AS 'usedin'
FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
ON (so.id=sc.id)
INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
'%',sc.text)>0)
WHERE so.xtype IN ('P','V','FN')
AND so2.xtype ='U'
) x ON a.name = x.name
WHERE x.name IS NULL
AND a.xtype IN ('U')
"DBA72" wrote:

> I am trying to find all the user tables that are not mentioned in a view o
r
> stored procedure. Instead, this lists each table once for each stored
> procedure it is not appearing.
> select so2.name,so.name
> from AdminDB.dbo.sysobjects so inner join AdminDB.dbo.syscomments sc on
> (so.id=sc.id)
> inner join AdminDB.dbo.sysobjects so2 on
> (patindex('%so2.name%',sc.text)=0)
> where so.xtype in ('P','V')
> and so2.xtype ='U'
> order by so2.name|||I would use the ANSI schema views, as opposed to directly accessing system
tables. Does this yield the same result as your query?
SELECT t.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
LEFT OUTER JOIN
(
SELECT
oname = ROUTINE_NAME,
odef = ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
UNION ALL
SELECT
oname = TABLE_NAME,
odef = VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
) o
ON o.odef LIKE '%'+t.TABLE_NAME+'%'
WHERE o.oname IS NULL
Note, of course, that pattern matching isn't perfect, for example there are
these (and probably many other) limitations:
(a) a table name could be mentioned in a comment (false positive)
(b) a table name could be spread across multiple rows for a proc/view>8000
characters (missing)
(c) the view/proc name could be the same as or contain the table name, but
not actually depend on it
http://www.aspfaq.com/
(Reverse address to reply.)
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:BB5D2A28-C9A7-4606-82A6-3E7960E72065@.microsoft.com...[vbcol=seagreen]
> GOT IT!
> SELECT a.name
> FROM AdminDB.dbo.sysobjects a LEFT JOIN (
> SELECT so2.name,so.name AS 'usedin'
> FROM AdminDB.dbo.sysobjects so INNER JOIN AdminDB.dbo.syscomments sc
> ON (so.id=sc.id)
> INNER JOIN AdminDB.dbo.sysobjects so2 ON (patindex('%' + so2.name+
> '%',sc.text)>0)
> WHERE so.xtype IN ('P','V','FN')
> AND so2.xtype ='U'
> ) x ON a.name = x.name
> WHERE x.name IS NULL
> AND a.xtype IN ('U')
> "DBA72" wrote:
>
or[vbcol=seagreen]