syscomments query

anushahanna
anushahanna used Ask the Experts™
on
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'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Try this
select object_name(t1.id), count(DISTINCT t2.Name)
from syscomments t1
inner join sysobjects t2 on 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' 
GROUP BY object_name(t1.id)

Open in new window

Author

Commented:
That was a smart idea. thanks.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial