Find tables which are most commonly used in Stored Procedures

When you start working on a new project, one of the important tasks as a database developer is to get used to the table names. Here’s a script which will help you identify the most commonly used tables (assuming that the most commonly used are the most important).

SELECT TOP 50
t.name, COUNT(1) tcount
FROM INFORMATION_SCHEMA.routines r
INNER JOIN sys.tables t ON r.ROUTINE_NAME LIKE ('%' + t.name + '%')
GROUP BY t.name 
ORDER BY tcount desc
Advertisements