Examples Using the sys Schema in SQL Server

The Object Catalog and Dynamic Management Views are some of the features that are stored in the sys schema. They contains many objects that are built-in to every SQL Server database. Information about the tables, stored procedures and views contained within your database can be retrieved through these objects. They also give you information, like how many connections are active on your database.

The purpose of this article is to run through some of the SQL statements you can run to get this information. With that aside, that’s begin.

Getting a List of Tables

The tables sys.tables and sys.schemas store information about your the tables and schemas that exist in your database. If you perform an INNER JOIN to these tables, you can get a list of all the tables and the schemas they belong to. Here is a query you can use to get this information.

SELECT s.[name] as SchemaName, t.[name] AS TableName from sys.tables t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
ORDER BY SchemaName, TableName

Getting a List of Views and Stored Procedures

Very similar way of getting views and stored procedures. There is a sys.views and a sys.procedures table which you can join up with the sys.schemas table. As a result, these statements will get you a list of each one.

SELECT s.[name] as SchemaName, v.[Name] AS ViewName from sys.views v
INNER JOIN sys.schemas s ON v.schema_id=s.schema_id
ORDER BY SchemaName, ViewName

SELECT s.[name] as SchemaName, p.name AS ProcedureName from sys.procedures p
INNER JOIN sys.schemas s ON p.schema_id=s.schema_id
ORDER BY SchemaName, ProcedureName

Listing the Columns

It’s not just the objects that you can get a list of. You can also retrieve information about columns from a table or view. This SQL Statement shows you how to get all the columns from all the tables that exist in your database.

SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName, c.column_id AS ColumnId, c.max_length AS ColumnMaxLength from sys.columns c
INNER JOIN sys.tables t ON c.object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
ORDER BY SchemaName, TableName, ColumnId

A couple of things to note on the above example. The column_id field is the order that your column appears in the table. Subsequently, the lower the number, the higher it ranks in the table.

We also have a max_length column which specifies the maximum length that any value for that column can contain.

So what if you want to know this information for a view? Well, you can run a similar query, substituting the sys.tables object for sys.views.

SELECT s.name AS SchemaName, v.name AS ViewName, c.name AS ColumnName, c.column_id AS ColumnId, c.max_length AS ColumnMaxLength from sys.columns c
INNER JOIN sys.views v ON c.object_id=v.object_id
INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
ORDER BY SchemaName, ViewName, ColumnId

Who’s running a Query on the Database?

If your database starts having performance issues, you will want to know what is taking up the resources. Is that do-able by running a SQL Statement? Yes it is!

All requests are stored in the sys.requests objects and you can join sys.databases to get the database that the request is coming from. You can also find out the SQL statement that request is trying to execute. All you need to do is to pass the sql_handle column into the dm_exec_sql_text function to get the result.

Other information you can retrieve includes the connection id, the total elapsed time and the number of reads. Here is a query to help you get this information.

SELECT d.name as DatabaseName, r.connection_id AS ConnectionId, s.text AS SqlStatement,
r.session_id AS SessionId,
r.status AS Status,
r.command AS Command,
r.cpu_time AS CpuTimeMs,
r.total_elapsed_time AS TotalElapsedTimeMs,
r.reads AS Reads,
r.writes AS Writes
FROM sys.dm_exec_requests r
INNER JOIN sys.databases d ON r.database_id=d.database_id

Getting Stats about Performance

The above example is great for what’s happening now, but what if you want statistics on the performance? Well you can use the sys.dm_exec_query_stats object. This can get you information such as the SQL statement ran, the last executed time, the number of reads and writes, and even a query plan. Run the query to see what I mean:

SELECT top 50 sql.text AS sql_query, stats.execution_count,
stats.last_execution_time, stats.last_elapsed_time/1000 AS last_elapsed_time_ms, stats.last_logical_reads, stats.last_logical_writes, stats.total_elapsed_time/1000 AS total_elapsed_time_ms,
stats.total_logical_reads, stats.total_logical_writes, [plan].query_plan AS query_plan
FROM sys.dm_exec_query_stats stats
CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS sql
CROSS APPLY sys.dm_exec_query_plan(stats.plan_handle) AS [plan]
ORDER BY stats.last_execution_time desc

Only Touching the Surface

The queries above only touch the surface of what is included in the sys schema. There is a list of objects available on the Microsoft website.

These will come in handy if you want statistics of how big your database is, or how your database is performing. It can also help you identify areas that have performance issues, and help you to resolve them.