Querying SQL Server Metadata

To get administrative and informational activities, some functions are available to view mentioned tasks, like stored functions and dynamic management objects. Latter one provides server state information, that exist as 200 DMO functions. Each one's duty is separately shared, for databases,executions,transactions and so on. Most used are:

• dm_db_
• dm_os_
• dm_exec_
• dm_tran_
• dm_io_

DMO stored under sys schema, and executed by xp_ prefix via SELECT statement command.

General formula: select ... from sys.dm_<DMO>_<function>

To view whole schemes exist in server: Database_name\Programmability\Security\Schemas

System stored procedures give administrative info about server,its instances. DBAs frequently use same way to be informed, logically is the same of other stored procedures, however it is created by system and stored in Master database under sys schema,adding sp_ prefix. Executed by exec or execute commands by mentioning parameters within.

Formula of system stored procedures: exec sys.sp_<function>

To display all system stored procedures:

Extended stored procedures has been already integrated to CLR(Common Language Run) after SQL Server 2005.This type of functions are created by C and C++ languages mainly, to provide general information as system sp or tasks.Extended SP is used by xp_ prefix and stored under sys schema as same as System SP. 

Formula is: exec sys.xp_<function>

You can watch my video throughout YouTube, explanation is in Turkish only: