Check by dependencies:
Figure out which objects have no internal dependencies:
select distinct so.id, so.name, so.Type
from sys.sysdepends sd
inner join sys.sysobjects so on so.id = sd.id
-- next line: filter by schema' if required:
inner join sys.objects o on o.object_id = so.id and SCHEMA_NAME(o.schema_id) <> 'dbo'
where
-- next line: filter by schema' if required:
inner join sys.objects o on o.object_id = so.id and SCHEMA_NAME(o.schema_id) <> 'dbo'
where
not exists ( select 1 from sysdepends sd2
where sd2.depid = so.id )
and so.Type in ('AF', 'FN', 'FS', 'FT', 'IF', 'TF', 'S', 'U', 'V') -- depend which types we want to check (and if we want)
I did another checks:
1. Insert the result of this quert into table
2. Search calls to those objects in field ROUTINE_DEFINITION in table INFORMATION_SCHEMA.ROUTINES. remove the objects that I found here.
3. Additional check in syscomments.
-- step 1
declare @UnUsedObjects TABLE (o_id int, o_name sysname, o_type char(2))
insert into @UnUsedObjects (o_id, o_name, o_type)
select distinct so.id, so.name, so.Type
from sys.sysdepends sd
inner join sys.sysobjects so on so.id = sd.id
where
not exists ( select 1 from sysdepends sd2
where sd2.depid = so.id )
and so.Type in ('AF', 'FN', 'FS', 'FT', 'IF', 'TF', 'S', 'U', 'V')
-- end of insert
select * from @UnUsedObjects --check after step 1
-- step 2
DECLARE @o_name nvarchar(50), @o_nameLike nvarchar(50)
DECLARE CursorWO CURSOR FORWARD_ONLY FOR
-------------- Start Select --------------------------
select cast(o_name as nvarchar(50)), cast('%' + o_name + '%' as nvarchar(50))
from @UnUsedObjects
-------------- End Select ----------------------------
OPEN CursorWO
FETCH NEXT FROM CursorWO
INTO @o_name, @o_nameLike
WHILE (@@FETCH_STATUS = 0)
BEGIN
if exists (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE @o_nameLike
and SPECIFIC_NAME <> @o_name
) delete from @UnUsedObjects where o_name = @o_name
--Get next record from cursor
FETCH NEXT FROM CursorWO
INTO @o_name, @o_nameLike
END
CLOSE CursorWO
DEALLOCATE CursorWO
select *, (CASE WHEN o_type IN ('S', 'U') THEN 10
WHEN o_type IN ('AF', 'FN', 'FS', 'FT', 'IF', 'TF') THEN 20
WHEN o_type IN ('V') THEN 30
ELSE 40
END) as ord
from @UnUsedObjects
order by ord, o_type
-- step 3
-- run this for each object:
select object_name(id) as object_name_, count(*) as count_
from syscomments where text like '%XXXXX%'
group by object_name(id) order by object_name(id)
More check:
select r.routine_name, r.routine_type, o.Type, o.name as objectName
from sys.objects o
left join INFORMATION_SCHEMA.ROUTINES r on r.ROUTINE_DEFINITION LIKE '%' + o.name + '%'
and SPECIFIC_SCHEMA <> 'dbo' -- schema check, if required
where o.Type in ('S', 'U')
where o.Type in ('S', 'U')
and r.routine_name is null
EXEC sp_depends @objname = N'TABLENAME'
Check by DB actual execution:
-- get last execution time:
SELECT object_name(m.object_id), MAX(qs.last_execution_time)
FROM sys.sql_modules m
LEFT JOIN (sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) st)
ON m.object_id = st.objectid
AND st.dbid = db_id()
GROUP BY object_name(m.object_id)
order by MAX(qs.last_execution_time) desc
-- get accesses count of tables:
SELECT t.name AS 'Table',
SUM(i.user_seeks + i.user_scans + i.user_lookups) AS 'Total accesses',
SUM(i.user_seeks) AS 'Seeks',
SUM(i.user_scans) AS 'Scans',
SUM(i.user_lookups) AS 'Lookups'
FROM sys.dm_db_index_usage_stats i
RIGHT OUTER JOIN sys.tables t ON (t.object_id = i.object_id)
GROUP BY i.object_id, t.name
ORDER BY [Total accesses] desc
Links:
Listing SQL Server Object Dependencies:
No comments:
Post a Comment