syscomments query
the query will bring SPs that do not reference any tables. But it is returning more Proc names than there are in the database. what could be wrong?
select count(t2.Name)
from syscomments t1
inner join sysobjects t2 t1.id = t2.id
where not exists(SELECT 1
FROM syscomments sc, sysobjects so
WHERE sc.TEXT LIKE
'%[^@0-9#A-Z_$]' + so.Name + '[^@0-9#A-Z_$]%' /*trying to make sure only complete table names are included */
and so.xtype = 'u' and t1.id = sc.id
) and t2.xtype = 'p'
select count(t2.Name)
from syscomments t1
inner join sysobjects t2 t1.id = t2.id
where not exists(SELECT 1
FROM syscomments sc, sysobjects so
WHERE sc.TEXT LIKE
'%[^@0-9#A-Z_$]' + so.Name + '[^@0-9#A-Z_$]%' /*trying to make sure only complete table names are included */
and so.xtype = 'u' and t1.id = sc.id
) and t2.xtype = 'p'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Welcome..
ASKER