They are very rich source for getting meta data information for the server.
Lets look for more detail on DISCOVER METHODS
Provides information for commands
SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_COMMANDS
)
Provides resource usage information for current running or last executed command.From command_text column we can get query for the session.
You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934103.aspx
Provides information for connections.
SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_CONNECTIONS
)
Provides information related to connection.
Useful column will be connection_host_name which will provide ip of the connection, connection host application.
You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934105.aspx
Provides information for datasources
SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_DATASOURCES
)
But this one will not give results using select command.
You can get the results with using DISCOVER method.
<Envelope xmlns=”http://schemas.xmlsoap.org/soap/envelope/”>
<Header />
<Body>
<Discover xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList />
</Restrictions>
<Properties>
<PropertyList />
</Properties>
</Discover>
</Body>
</Envelope>
You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126129.aspx
To get information for current instance
SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_INSTANCES
,INSTANCE_NAME=’sseight’
)
You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126129.aspx
Provides information for the active jobs running on the server
SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_JOBS
)
You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934102.aspx
Provides information for all keywords reserved by provider
SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_KEYWORDS
)
You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126063.aspx
Provides information for locks
SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_LOCKS
)
SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_LOCKS,
LOCK_STATUS =’1′
)
— 0 Granted locks
— 1 Waiting locks
Another useful column in lock_type
You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934104.aspx
Provides information for memory usage by objects
SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
)
ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC
You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934098.aspx
Provides information about the standard and provider-specific properties
SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_PROPERTIES
)
You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126045.aspx
Lets SQL 