T-SQL Scripts

1. In SQL Server, how do you find un-used stored procedures and un-used views?

A There is no in-built mechanism to identify the un-used stored procedures. When an stored procedure is called, an entry will be created in "sys.dmexecquery_stats". Based on this information, we can find un-used stored procedures by using below query. However, this information will be deleted once the server re-starts.

SELECT * from sys.procedures P

LEFT OUTER JOIN  ( SELECT x.objectid FROM sys.dm_exec_query_stats AS s1

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS x

WHERE OBJECTPROPERTYEX(x.objectid,''IsProcedure'') = 1 T on P.object_id = T.objectid

WHERE T.objectid is null

For views, it will store the execution plan in DMVs. Below query can be used to get un-used views. When the server is re-started, this information will be cleared. Also, when the server has more run out of procedure cache, it will dump the old execution plans. so, this query result might not be approximate.

SELECT v.name [ViewName] FROM sys.views v

WHERE v.is_ms_shipped = 0 EXCEPT SELECT o.Name [ViewName]

FROM master.sys.dm_exec_cached_plans p CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t

INNER JOIN sys.objects o ON t.objectid = o.object_id [here][1]

2 Suppose you have Employee_mst table now how to find store procedure uses this Employee_Mst table?

SELECT sys.objects.name, sys.objects.type, sys.objects.type_desc,

sys.objects.schema_id, sys.syscomments.text

FROM sys.objects

INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id

where sys.syscomments.text like '%Employee_mst%' And type ='P'

3 Difference between Stored Procedure and Trigger

1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.

2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table. 

3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

5) Stored procedures can return values but a trigger cannot return a value.

6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.

7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.

8) We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.