SQL Get List of Tables and SPs and Views Used/NotUsed of Database

 -- used XYZ DB views into PQR DB SPs


SELECT DISTINCT




--SP_Name = O.name,


Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'V'



　

---- count of used sps in db



WITH T1




as


(


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

)

,



T2

as

(

SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

)

select *,COUNT (T1.SPECIFIC_NAME) OVER (PARTITION BY T1.SPECIFIC_NAME) from T1

LEFT JOIN T2 ON T1.SPECIFIC_NAME = T2.SPECIFIC_NAME





---- not used sp by execution time


select SPECIFIC_NAME

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'

And SPECIFIC_NAME not in




(


SELECT o.name as SPECIFIC_NAME

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'




)



----used sp by execution time

SELECT o.name,

ps.last_execution_time

FROM sys.dm_exec_procedure_stats ps

INNER JOIN

sys.objects o

ON ps.object_id = o.object_id

WHERE DB_NAME(ps.database_id) = 'XYZ' And O.type = 'P'

ORDER BY

ps.last_execution_time DESC




---- get name of sps


select *

from XYZ.information_schema.routines

where routine_type = 'PROCEDURE'



　

-- not used tables


SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG='PQR'

And TABLE_NAME not in

(SELECT DISTINCT

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P')




-- used tables


SELECT DISTINCT

SP_Name = O.name,

Table_Name = OO.name

FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON

O.id = D.id

INNER JOIN sys.sysobjects OO ON

OO.id = D.depid

WHERE O.xtype = 'P' And OO.xtype = 'U'