Name some of the frequently used system procedures on the day to day work with their uses.

System Procedure

Syntax

Work

sp_who

EXEC sp_who;

This system procedure will return all the running session, users and process on the database along with the status of them.

Sp_depends

EXEC sp_depends ‘schema_name.Object_name’

This procedure helps in finding out all the dependent items on the object.

I.e. if we execute sp_depends ‘procedure name’, it will show all the object (table, function, view, procedure etc.) which are used in the procedure

I.e. if we execute sp_depends ‘table name’, it will show all the objects/ places where the table is used (function, view, procedure, trigger etc.)

sp_helptext

EXEC sp_helptext ‘schema_name.Object_name’

It helps in extracting the definition of the objects( procedure, function, trigger) from the server

Sp_help

EXEC sp_help

Or EXEC sp_help ‘schema_name.Object_name’

This procedure extract all the system related information of the object.

I.e. if we execute sp_help( without arguments ) then it will show all the objects which are there on the server including the details of the object

I.e. if we execute sp_help ‘procedure name’ (or function name) then it will show details of the procedure/ function and all the objects parameter details.

I.e. if we execute sp_help ‘table name’ then it will show details of the column / column datatype / index details/ primary key details

Sp_tables

EXEC sp_tables

This will return list of all the table/ views which are can be used/ queried /have access to the user

Sp_executesql

Exec Sp_executesql

This procedure is used to execute any dynamic sql command.

I.e. exec Sp_executesql @SQL will execute the sql and give the result in return.