{"id":2154,"date":"2016-01-27T00:00:00","date_gmt":"2016-01-27T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/sql-server-system-views-the-basics\/"},"modified":"2021-08-16T15:01:55","modified_gmt":"2021-08-16T15:01:55","slug":"sql-server-system-views-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/learn\/sql-server-system-views-the-basics\/","title":{"rendered":"SQL Server System Views: The Basics"},"content":{"rendered":"
\n

SQL Server provides an assortment of system views for accessing metadata about the server environment and its database objects. There are catalog views and information schema views and dynamic management views and several other types of views. DBAs and developers alike can benefit significantly from the rich assortment of information they can derive through these views, and it is worth the effort to get to know them. <\/p>\n

System views are divided into categories that each serve a specific purpose. The most extensive category is the one that contains catalog views. Catalog views let you retrieve information about a wide range of system and database components-from table columns and data types to server-wide configurations.<\/p>\n

Information schema views are similar to some of the catalog views in that they provide access to metadata that describes database objects such as tables, columns, domains, and check constraints. However, information schema views conform to the ANSI standard, whereas catalog views are specific to SQL Server.<\/p>\n

In contrast to either of these types of views, dynamic management views return server state data that can be used to monitor and fine-tune a SQL Server instance and its databases. Like catalog views, dynamic management views are specific to SQL Server.<\/p>\n

In this article, we’ll focus on these three types of views, looking at examples in each category. We won’t be covering the other types of system views because they tend not to be as commonly used, with perhaps a couple exceptions. For the most part, catalog, information schema, and dynamic management views are the ones you’ll likely be using the most often. But just so you know, the other types are related to replication and data-tier application (DAC) instances as well as provide compatibility with earlier SQL Server releases. Although they have their places, for now we’ll stick with the big three.<\/p>\n

Catalog views<\/h1>\n

Of the various types of system views available in SQL Server, catalog views represent the largest collection and most diverse. You can use catalog views to gather information about such components as AlwaysOn Availability Groups, Change Data Capture, change tracking, database mirroring, full-text search, Resource Governor, security, Service Broker, and an assortment of other features-all in addition to being able to view information about the database objects themselves.<\/p>\n

In fact, SQL Server provides so many catalog views that it would be nearly impossible-or at least highly impractical-to try look at all of them in one article, but know that there is a vast storehouse of views waiting for you, and they all work pretty much the same way. <\/p>\n

Microsoft recommends that you use catalog views as your primary method for accessing SQL Server metadata because they provide the most efficient mechanism for retrieving this type of information. Through the catalog views you can access all user-available metadata. For example, the following SELECT<\/code><\/b> statement returns information about databases whose name starts with adventureworks<\/i>:<\/p>\n

SELECT name, database_id, compatibility_level\nFROM sys.databases\nWHERE name LIKE 'adventureworks%';\n<\/pre>\n
The columns  specified in the  SELECT<\/code><\/b> clause-name<\/code><\/b>,  database_id<\/code><\/b>, and  compatibility_level<\/code><\/b>-represent  only a fraction of the many columns supported by this view. The view will  actually return nearly 75 columns worth of information about each database  installed on the SQL Server instance. I’ve kept it short for the sake of  brevity, as shown in the following results: <\/p>\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tdatabase_id<\/b><\/p>\n<\/td>\n
\n
 \t\tcompatibility_level<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
AdventureWorks2014<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n
\n
120<\/p>\n<\/td>\n<\/tr>\n
\n
AdventureWorksDW2014<\/p>\n<\/td>\n
\n
10<\/p>\n<\/td>\n
\n
120<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
There is  nothing remarkable here, except for the ease with which I was able to collect  the metadata. The results include the database names, their auto-generated IDs,  and their compatibility levels, which in both cases is 120<\/code><\/b>. The  120<\/code><\/b> refers to SQL Server 2014. (I created the examples in  this article on a local instance of SQL Server 2014 running in a test virtual  machine.)<\/p>\n
The  sys.databases<\/code><\/b> view can also return information about  database settings, such as whether the database is read-only or whether the  auto-shrink feature is enabled. Many of the configuration-related columns take  the  bit<\/b> data type to indicate whether a feature is  on (1<\/b>) or off (0<\/code><\/b>).  <\/p>\n
As the  preceding example illustrates, you access catalog views through the  sys<\/code><\/b> schema. Whichever view you use, it’s always a good idea  to check the SQL Server documentation if you have any questions about its  application to your particular circumstances. For example, the  sys.databases<\/code><\/b> view includes the state<\/code><\/b>  column, which provides status information such as whether a database is online,  offline, or being restored. Each option is represented by one of nine predefined  tinyint<\/b> values. Some values in this column  pertain only to certain environments. For instance, the value 7<\/code><\/b>  (copying) applies only to Azure SQL Database.<\/p>\n
Now let’s  look at the  sys.objects<\/b> catalog view, which returns a row  for each user-defined, schema-scoped object in a database. The following  SELECT<\/code><\/b> statement retrieves the name and ID of all  table-valued functions defined in the  dbo<\/code><\/b> schema within  the  AdventureWorks2014<\/code><\/b> sample database:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT name, object_id\nFROM sys.objects\nWHERE SCHEMA_NAME(schema_id) = 'dbo'\n  AND type_desc = 'sql_table_valued_function';\n \n<\/pre>\n
Notice that  I use the  SCHEMA_NAME<\/b> built-in function to match the  schema  ID<\/b> to  dbo<\/code><\/b> in the  WHERE<\/code><\/b> clause. Functions such as SCHEMA_NAME<\/code><\/b>,  OBJECT_ID<\/b>,  OBJECT_NAME<\/code><\/b>, and  so on can be extremely useful when working with catalog views. <\/p>\n
Also in the  WHERE<\/code><\/b> clause, I match the type_desc<\/code><\/b>  column to  sql_table_valued_function<\/b>, giving me the  following results:<\/p>\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tobject_id<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tufnGetContactInformation<\/p>\n<\/td>\n
\n
103671417<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The  sys.objects<\/code><\/b> view is a handy tool to have because it provides  quick and easy access to all user-defined objects in your database, including  tables, views, triggers, functions, and constraints. However, SQL Server also  provides catalog views that are distinct to a specific object type. For example,  the following  SELECT<\/code><\/b> statement  retrieves data through the  sys.tables<\/code><\/b> view:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT name, max_column_id_used\nFROM sys.tables\nWHERE SCHEMA_NAME(schema_id) = 'HumanResources'\n<\/pre>\n
The  statement returns a list of all tables in the  HumanResources<\/code><\/b>  schema, along with the maximum column ID used for each table, as shown in the  following results:<\/p>\n\n\n\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tmax_column_id_used<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
Shift<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n<\/tr>\n
\n
Department<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n<\/tr>\n
\n
Employee<\/p>\n<\/td>\n
\n
16<\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tEmployeeDepartmentHistory<\/p>\n<\/td>\n
\n
6<\/p>\n<\/td>\n<\/tr>\n
\n
EmployeePayHistory<\/p>\n<\/td>\n
\n
5<\/p>\n<\/td>\n<\/tr>\n
\n
JobCandidate<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The  interesting thing about the  sys.tables<\/code><\/b> view is  that it inherits all the columns from the  sys.objects<\/code><\/b> view  and then adds additional columns with table-specific information. For example,  in the preceding example, the  name<\/code><\/b> column is  inherited from  sys.objects<\/code><\/b> but the max_column_id_used<\/b> column is specific to sys.tables<\/code><\/b>. <\/b>(For information about which views inherit columns from other views, refer  to the SQL Server documentation.)<\/b><\/p>\n
You can also  join catalog views to retrieve specific types of information. For example, the  following  SELECT<\/b> statement joins the sys.columns<\/code><\/b>  view to the  sys.types<\/b> view to retrieve information about the  Person<\/b> table:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT c.name AS ColumnName,  \n  t.name AS DataType, \n  CASE t.is_user_defined\n    WHEN 1 THEN 'user-defined type'\n    ELSE 'system type' END AS UserOrSystem\nFROM sys.columns c JOIN sys.types t\n  ON c.user_type_id = t.user_type_id\nWHERE c.object_id = OBJECT_ID('Person.Person');\n<\/pre>\n
Not  surprisingly, the  sys.columns<\/code><\/b> view  returns a list of columns in the table, and the sys.types<\/code><\/b>  view returns the name of the column data types, along with whether they are  system types or user-defined:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tColumnName<\/b><\/p>\n<\/td>\n
\n
 \t\tDataType<\/b><\/p>\n<\/td>\n
\n
 \t\tUserOrSystem<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
BusinessEntityID<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
PersonType<\/p>\n<\/td>\n
\n
nchar<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
NameStyle<\/p>\n<\/td>\n
\n
NameStyle<\/p>\n<\/td>\n
\n
user-defined type<\/p>\n<\/td>\n<\/tr>\n
\n
Title<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
FirstName<\/p>\n<\/td>\n
\n
Name<\/p>\n<\/td>\n
\n
user-defined type<\/p>\n<\/td>\n<\/tr>\n
\n
MiddleName<\/p>\n<\/td>\n
\n
Name<\/p>\n<\/td>\n
\n
user-defined type<\/p>\n<\/td>\n<\/tr>\n
\n
LastName<\/p>\n<\/td>\n
\n
Name<\/p>\n<\/td>\n
\n
user-defined type<\/p>\n<\/td>\n<\/tr>\n
\n
Suffix<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
EmailPromotion<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
AdditionalContactInfo<\/p>\n<\/td>\n
\n
xml<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
Demographics<\/p>\n<\/td>\n
\n
xml<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
rowguid<\/p>\n<\/td>\n
\n
uniqueidentifier<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n
\n
ModifiedDate<\/p>\n<\/td>\n
\n
datetime<\/p>\n<\/td>\n
\n
system type<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Up to this  point, the catalog views we’ve looked at have focused on the databases and their  objects. However, we can use catalog views to retrieve all sorts of information,  such as details about database files:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT file_id, name, state_desc, type_desc\nFROM sys.database_files\nWHERE name LIKE 'adventureworks%';\n<\/pre>\n
In this  case, we’re using the  sys.database_files<\/code><\/b> view to retrieve the  file ID, file name, file state, and file type.<\/p>\n\n\n\n\n\n
\n
 \t\tfile_id<\/b><\/p>\n<\/td>\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tstate_desc<\/b><\/p>\n<\/td>\n
\n
 \t\ttype_desc<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
 \t\tAdventureWorks2014_Data<\/p>\n<\/td>\n
\n
ONLINE<\/p>\n<\/td>\n
\n
ROWS<\/p>\n<\/td>\n<\/tr>\n
\n
2<\/p>\n<\/td>\n
\n
 \t\tAdventureWorks2014_Log<\/p>\n<\/td>\n
\n
ONLINE<\/p>\n<\/td>\n
\n
LOG<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
We might  instead use the  sys.assembly_types<\/b> view to return information  about any assemblies added to the database:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT name, user_type_id, assembly_class\nFROM sys.assembly_types;\n<\/pre>\n
As the  following results show, the  AdventureWorks2014<\/code><\/b>  database includes three assemblies, all of which are SQL Server’s advanced data  types:<\/p>\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tuser_type_id<\/b><\/p>\n<\/td>\n
\n
 \t\tassembly_class<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
hierarchyid<\/p>\n<\/td>\n
\n
128<\/p>\n<\/td>\n
\n
 \t\tMicrosoft.SqlServer.Types.SqlHierarchyId<\/p>\n<\/td>\n<\/tr>\n
\n
geometry<\/p>\n<\/td>\n
\n
129<\/p>\n<\/td>\n
\n
 \t\tMicrosoft.SqlServer.Types.SqlGeometry<\/p>\n<\/td>\n<\/tr>\n
\n
geography<\/p>\n<\/td>\n
\n
130<\/p>\n<\/td>\n
\n
 \t\tMicrosoft.SqlServer.Types.SqlGeography<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You can even  retrieve data about security-related metadata within your database. For example,  the following  SELECT<\/code><\/b> statement  uses the  sys.database_principals<\/b> view to return the names  and IDs of all security principals in the AdcentureWorks2014<\/code><\/b>  database:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT name, principal_id\nFROM sys.database_principals\nWHERE type_desc = 'DATABASE_ROLE';\n<\/pre>\n
Notice that  we’ve used a  WHERE<\/code><\/b> clause to qualify our query so the  SELECT<\/b> statement returns only the  DATABASE_ROLE<\/code><\/b> principal type:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tprincipal_id<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
public<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n<\/tr>\n
\n
db_owner<\/p>\n<\/td>\n
\n
16384<\/p>\n<\/td>\n<\/tr>\n
\n
db_accessadmin<\/p>\n<\/td>\n
\n
16385<\/p>\n<\/td>\n<\/tr>\n
\n
db_securityadmin<\/p>\n<\/td>\n
\n
16386<\/p>\n<\/td>\n<\/tr>\n
\n
db_ddladmin<\/p>\n<\/td>\n
\n
16387<\/p>\n<\/td>\n<\/tr>\n
\n
db_backupoperator<\/p>\n<\/td>\n
\n
16389<\/p>\n<\/td>\n<\/tr>\n
\n
db_datareader<\/p>\n<\/td>\n
\n
16390<\/p>\n<\/td>\n<\/tr>\n
\n
db_datawriter<\/p>\n<\/td>\n
\n
16391<\/p>\n<\/td>\n<\/tr>\n
\n
db_denydatareader<\/p>\n<\/td>\n
\n
16392<\/p>\n<\/td>\n<\/tr>\n
\n
db_denydatawriter<\/p>\n<\/td>\n
\n
16393<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Of course,  SQL Server security occurs at the database level and at the server level. To  address the server level, SQL Server also includes catalog views specific to the  current instance. For example, the following SELECT<\/code><\/b>  statement joins the  sys.server_principals<\/code><\/b>  view and the  sys.server_permissions<\/code><\/b> view to retrieve  information about the server principals and their permissions:<\/b><\/p>\n
SELECT pr.name, pr. principal_id, \n  pm.permission_name, pm.state_desc\nFROM sys.server_principals pr \n  JOIN sys.server_permissions AS pm\n  ON pr.principal_id = pm.grantee_principal_id \nWHERE pr.type_desc = 'SERVER_ROLE';\n<\/pre>\n
In this  case, we’re concerned only with the  SERVER_ROLE<\/code><\/b>  principal type, so we’ve added the  WHERE<\/code><\/b> clause,  giving us the following results:<\/p>\n\n\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tprincipal_id<\/b><\/p>\n<\/td>\n
\n
 \t\toermission_name<\/b><\/p>\n<\/td>\n
\n
 \t\tstate_desc<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
public<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
VIEW ANY DATABASE<\/p>\n<\/td>\n
\n
GRANT<\/p>\n<\/td>\n<\/tr>\n
\n
public<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
CONNECT<\/p>\n<\/td>\n
\n
GRANT<\/p>\n<\/td>\n<\/tr>\n
\n
public<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
CONNECT<\/p>\n<\/td>\n
\n
GRANT<\/p>\n<\/td>\n<\/tr>\n
\n
public<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
CONNECT<\/p>\n<\/td>\n
\n
GRANT<\/p>\n<\/td>\n<\/tr>\n
\n
public<\/p>\n<\/td>\n
\n
2<\/p>\n<\/td>\n
\n
CONNECT<\/p>\n<\/td>\n
\n
GRANT<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
You can also  use catalog views to retrieve server configuration information. For instance,  the following  SELECT<\/code><\/b> statement uses the the  sys.configurations<\/code><\/b> view to retrieve configuration  information about the current server:<\/p>\n
SELECT name, description\nFROM sys.configurations\nWHERE is_advanced = 1 AND is_dynamic = 0;\n<\/pre>\n
In this  case, we’ve limited our query to non-dynamic advanced settings, as shown in the  following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tname<\/b><\/p>\n<\/td>\n
\n
 \t\tdescription<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
user connections<\/p>\n<\/td>\n
\n
Number of user  \t\tconnections allowed<\/p>\n<\/td>\n<\/tr>\n
\n
locks<\/p>\n<\/td>\n
\n
Number of locks for  \t\tall users<\/p>\n<\/td>\n<\/tr>\n
\n
open objects<\/p>\n<\/td>\n
\n
Number of open  \t\tdatabase objects<\/p>\n<\/td>\n<\/tr>\n
\n
fill factor (%)<\/p>\n<\/td>\n
\n
Default fill factor  \t\tpercentage<\/p>\n<\/td>\n<\/tr>\n
\n
c2 audit mode<\/p>\n<\/td>\n
\n
c2 audit mode<\/p>\n<\/td>\n<\/tr>\n
\n
priority boost<\/p>\n<\/td>\n
\n
Priority boost<\/p>\n<\/td>\n<\/tr>\n
\n
set working set size<\/p>\n<\/td>\n
\n
set working set size<\/p>\n<\/td>\n<\/tr>\n
\n
lightweight pooling<\/p>\n<\/td>\n
\n
User mode scheduler  \t\tuses lightweight pooling<\/p>\n<\/td>\n<\/tr>\n
\n
scan for startup  \t\tprocs<\/p>\n<\/td>\n
\n
scan for startup  \t\tstored procedures<\/p>\n<\/td>\n<\/tr>\n
\n
affinity I\/O mask<\/p>\n<\/td>\n
\n
affinity I\/O mask<\/p>\n<\/td>\n<\/tr>\n
\n
affinity64 I\/O mask<\/p>\n<\/td>\n
\n
affinity64 I\/O mask<\/p>\n<\/td>\n<\/tr>\n
\n
common criteria  \t\tcompliance enabled<\/p>\n<\/td>\n
\n
Common Criteria  \t\tcompliance mode enabled<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
There are,  of course, many more examples of catalog views I can show you, but you get the  point. There’s a great deal of information to be had, and I’ve barely scratched  the surface. For a complete listing of the available catalog views, check out  the MSDN topic  Catalog Views (Transact-SQL)<\/a>.<\/p>\n
Information schema views<\/h1>\n
Information  schema views provide a standardized method for querying metadata about objects  within a database. The views are part of the schema INFORMATION_SCHEMA<\/code><\/b>,  rather than the  sys<\/code><\/b> schema, and are much more limited in  scope than catalog views. At last count, SQL Server was providing only 21  information schema views, compared to over 200 catalog views.<\/p>\n
The  advantage of using information schema views is that, because they are  ANSI-compliant, you can theoretically migrate your code to different database  systems without having to update your view references. If portability is  important to your solution, you should consider information schema views, just  know that they don’t do nearly as much as catalog views. And, of course, using  one type of view doesn’t preclude you from using another type of view.<\/p>\n
With  information schema views, you can retrieve metadata about database objects such  as tables, constraints, columns, privileges, views, and domains. (In the world  of information schema views, a domain<\/i>  is a user-defined data type, and a catalog<\/i>  is the database itself.)<\/p>\n
Let’s look  at a few examples. The first one uses the  TABLES<\/code><\/b> view to  retrieve the name and type of all the tables and views in the Purchasing<\/code><\/b>  schema:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT TABLE_NAME, TABLE_TYPE\nFROM INFORMATION_SCHEMA.TABLES\nWHERE TABLE_SCHEMA = 'purchasing'\nORDER BY TABLE_NAME;\n<\/pre>\n
No magic  here. Just a simple query that returns basic information, as shown in the  following results:<\/p>\n\n\n\n\n\n\n\n\n\n\n
\n
 \t\tTABLE_NAME<\/b><\/p>\n<\/td>\n
\n
 \t\tTABLE_TYPE<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
ProductVendor<\/p>\n<\/td>\n
\n
BASE TABLE<\/p>\n<\/td>\n<\/tr>\n
\n
PurchaseOrderDetail<\/p>\n<\/td>\n
\n
BASE TABLE<\/p>\n<\/td>\n<\/tr>\n
\n
PurchaseOrderHeader<\/p>\n<\/td>\n
\n
BASE TABLE<\/p>\n<\/td>\n<\/tr>\n
\n
ShipMethod<\/p>\n<\/td>\n
\n
BASE TABLE<\/p>\n<\/td>\n<\/tr>\n
\n
Vendor<\/p>\n<\/td>\n
\n
BASE TABLE<\/p>\n<\/td>\n<\/tr>\n
\n
vVendorWithAddresses<\/p>\n<\/td>\n
\n
VIEW<\/p>\n<\/td>\n<\/tr>\n
\n
vVendorWithContacts<\/p>\n<\/td>\n
\n
VIEW<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
We could  have also retrieved the  TABLE_CATALOG<\/code><\/b> and  TABLE_SCHEMA<\/code><\/b> columns, which are included in the view to  provide fully qualified, four-part names for each object, but we didn’t need  that information in this case, and the table includes no other columns, falling  far short of what you get with  sys.tables<\/code><\/b>.<\/p>\n
Now let’s  pull data through the  COLUMNS<\/code><\/b> view, which provides a few more  details than we get with  TABLES<\/code><\/b>:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT COLUMN_NAME, DATA_TYPE, DOMAIN_NAME\nFROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_SCHEMA = 'person' \n  AND TABLE_NAME = 'contacttype';\n<\/pre>\n
In this  case, our query retrieves the column name, system data type, and user data type,  if any, within the  Person.ContactType<\/code><\/b> table. In this case,  the table includes only one user-defined data type (Name<\/code><\/b>):<\/p>\n\n\n\n\n\n\n
\n
 \t\tCOLUMN_NAME<\/b><\/p>\n<\/td>\n
\n
 \t\tDATA_TYPE<\/b><\/p>\n<\/td>\n
\n
 \t\tDOMAIN_NAME<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
ContactTypeID<\/p>\n<\/td>\n
\n
int<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n
\n
Name<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n
\n
Name<\/p>\n<\/td>\n<\/tr>\n
\n
ModifiedDate<\/p>\n<\/td>\n
\n
datetime<\/p>\n<\/td>\n
\n
NULL<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Now suppose  we want to retrieve a list of user-defined data types in the  AdventureWorks2014<\/code><\/b> database, along with the base type for  each one:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT DOMAIN_NAME, DATA_TYPE\nFROM INFORMATION_SCHEMA.DOMAINS\nORDER BY DOMAIN_NAME;\n<\/pre>\n
This time,  we use the  DOMAINS<\/b> view, which gives us the following  results (at least on my system):<\/p>\n\n\n\n\n\n\n\n\n\n
\n
 \t\tDOMAIN_NAME<\/b><\/p>\n<\/td>\n
\n
 \t\tDATA_TYPE<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
AccountNumber<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n<\/tr>\n
\n
Flag<\/p>\n<\/td>\n
\n
bit<\/p>\n<\/td>\n<\/tr>\n
\n
Name<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n<\/tr>\n
\n
NameStyle<\/p>\n<\/td>\n
\n
bit<\/p>\n<\/td>\n<\/tr>\n
\n
OrderNumber<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n<\/tr>\n
\n
Phone<\/p>\n<\/td>\n
\n
nvarchar<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Let’s look  at one more example, this one of the  CHECK_CONSTRAINTS<\/code><\/b>  view, which retrieves information about the check constraints in the  Person<\/code><\/b> schema:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT CONSTRAINT_NAME, CHECK_CLAUSE\nFROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS\nWHERE CONSTRAINT_SCHEMA = 'person';\n<\/pre>\n
In this  case, we get the name of the check constraints, along with the constraint  definitions:<\/p>\n\n\n\n\n\n
\n
 \t\tCONSTRAINT_NAME<\/b><\/p>\n<\/td>\n
\n
 \t\tCHECK_CLAUSE<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
 \t\tCK_Person_EmailPromotion<\/p>\n<\/td>\n
\n
 \t\t([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))<\/p>\n<\/td>\n<\/tr>\n
\n
CK_Person_PersonType<\/p>\n<\/td>\n
\n
([PersonType] IS NULL  \t\tOR (upper([PersonType])=’GC’ OR upper([PersonType])=’SP’ OR  \t\tupper([PersonType])=’EM’ OR upper([PersonType])=’IN’ OR  \t\tupper([PersonType])=’VC’ OR upper([PersonType])=’SC’))<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
That’s all  there is to information schema views. There are relatively few of them and the  ones that are there contain relatively little information, when compared to  their catalog counterparts. You can find more details about information schema  views by referring to the MSDN topic Information  Schema Views (Transact-SQL)<\/a>.<\/p>\n
Dynamic management views<\/h1>\n
With dynamic  management views, we move into new territory. The views return server state  information about your databases and servers, which can be useful for monitoring  your systems, tuning performance, and diagnosing any issues that might arise.<\/p>\n
Like catalog  views, dynamic management views provide a wide range of information. For  example, SQL Server includes a set of dynamic management that are specific to  memory-optimized tables. One of these,   dm_xtp_system_memory_consumers<\/code><\/b>, returns information about  database-level memory consumers:<\/p>\n
SELECT memory_consumer_desc, allocated_bytes, used_bytes\nFROM sys.dm_xtp_system_memory_consumers\nWHERE memory_consumer_type_desc = 'pgpool';\n<\/pre>\n
The  statement retrieves the consumer description, the amount of allocated bytes, and  the amount of used bytes for the  pgpool<\/code><\/b> consumer  type, giving us the following results.<\/p>\n\n\n\n\n\n\n
\n
 \t\tmemory_consumer_desc<\/b><\/p>\n<\/td>\n
\n
 \t\tallocated_bytes<\/b><\/p>\n<\/td>\n
\n
 \t\tused_bytes<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
System 256K page pool<\/p>\n<\/td>\n
\n
262144<\/p>\n<\/td>\n
\n
262144<\/p>\n<\/td>\n<\/tr>\n
\n
System 64K page pool<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n<\/tr>\n
\n
System 4K page pool<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n
\n
0<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Like catalog  views, dynamic management views are part of the sys<\/code><\/b>  schema. In addition, their names always begin with the dm_<\/code><\/b>  prefix. Unfortunately, Microsoft uses the same naming convention for SQL  Server’s dynamic management functions. But you’ll quickly discover which ones  are which when you try to run them and you’re prompted to provide input  parameters. (I’ll save a discussion about the functions for a different  article.)<\/p>\n
Another  category of dynamic management views focuses on the SQL Server Operating System  (SQLOS), which manages the operating system resources specific to SQL Server.  For example, you can use the  dm_os_threads<\/code><\/b> view  to retrieve a list of SQLOS threads running under the current SQL Server  process:<\/p>\n
SELECT os_thread_id, kernel_time, usermode_time\nFROM sys.dm_os_threads\nWHERE usermode_time > 300;\n<\/pre>\n
The  statement returns the thread ID, kernel time, and user time, for those threads  greater than 300 milliseconds, giving us the following results (on my test  system):<\/p>\n\n\n\n\n\n\n\n
\n
 \t\tos_thread_id<\/b><\/p>\n<\/td>\n
\n
 \t\tkernel_time<\/b><\/p>\n<\/td>\n
\n
 \t\tusermode_time<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
2872<\/p>\n<\/td>\n
\n
140<\/p>\n<\/td>\n
\n
327<\/p>\n<\/td>\n<\/tr>\n
\n
2928<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n
\n
1014<\/p>\n<\/td>\n<\/tr>\n
\n
2944<\/p>\n<\/td>\n
\n
46<\/p>\n<\/td>\n
\n
327<\/p>\n<\/td>\n<\/tr>\n
\n
5500<\/p>\n<\/td>\n
\n
78<\/p>\n<\/td>\n
\n
1216<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The SQLOS  views even include one that returns miscellaneous information about the computer  and its resources:<\/p>\n
SELECT cpu_count, physical_memory_kb, virtual_memory_kb\nFROM sys.dm_os_sys_info;\n<\/pre>\n
Although the  dm_os_sys_info<\/code><\/b> view can return a variety of information  about the environment, in this case, we’ve limited that information to the CPU  count, physical memory, and virtual memory:<\/p>\n\n\n\n\n
\n
 \t\tcpu_count<\/b><\/p>\n<\/td>\n
\n
 \t\tphysical_memory_kb<\/b><\/p>\n<\/td>\n
\n
 \t\tvirtual_memory_kb<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
4<\/p>\n<\/td>\n
\n
4193840<\/p>\n<\/td>\n
\n
8589934464<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
SQL Server  also includes dynamic management views for retrieving information about the  indexes. For example, you can use the  db_index_usage_stats<\/code><\/b>  view to return details about different types of index operations:<\/p>\n
SELECT index_id, user_seeks, user_scans\nFROM sys.dm_db_index_usage_stats\nWHERE object_id = OBJECT_ID('AdventureWorks2014.HumanResources.Employee');\n<\/pre>\n
The  statement returns the data shown in the following table:<\/p>\n\n\n\n\n
\n
 \t\tindex_id<\/b><\/p>\n<\/td>\n
\n
 \t\tuser_seeks<\/b><\/p>\n<\/td>\n
\n
 \t\tuser_scans<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
1<\/p>\n<\/td>\n
\n
4<\/p>\n<\/td>\n
\n
9<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Being able  to query statistics about an index in this way can be useful when testing an  application’s individual operations. This can help you pinpoint whether your  queries are using the indexes effectively or whether you might need to build  different indexes. Note, however, that index statistics can reflect all  activity, whether generated by an application or generated internally by SQL  Server.<\/p>\n
Dynamic  management views are either server-scoped or database-scoped. The ones we’ve  look at so far have been server-scoped, even the  dm_db_index_usage_stats<\/code><\/b> index shown in the last example. In that  case, however, we were concerned with only the  AdventureWorks2014<\/b> database, so we specified the  database in our  WHERE<\/code><\/b> clause.<\/p>\n
If you want  to run a database-scoped dynamic management view, you must do so within the  context of the target database. In the following SELECT<\/code><\/b>  statement, I use the  dm_db_file_space_usage<\/code><\/b>  view to return space usage data about the data file used by the  AdventureWorks2014<\/code><\/b> database: <\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT total_page_count, allocated_extent_page_count, unallocated_extent_page_count\nFROM sys.dm_db_file_space_usage\nWHERE file_id = 1;\n<\/pre>\n
All I’m  doing here is retrieving the total page count, allocated extent page count, and  unallocated extent page count:<\/p>\n\n\n\n\n
\n
 \t\ttotal_page_count<\/b><\/p>\n<\/td>\n
\n
 \t\tallocated_extent_page_count<\/b><\/p>\n<\/td>\n
\n
 \t\tunallocated_extent_page_count<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
30368<\/p>\n<\/td>\n
\n
28368<\/p>\n<\/td>\n
\n
2000<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Note that  page counts are always at the extent level, which means they will be multiples  of eight.<\/p>\n
We can  instead use the  dm_db_fts_index_physical_stats<\/code><\/b> view to  retrieve data about the full-text and sematic indexes in each table:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT OBJECT_NAME(object_id) ObjectName, \n  object_id ObjectID, fulltext_index_page_count IndexPages\nFROM sys.dm_db_fts_index_physical_stats;\n<\/pre>\n
This time we  get the object name and ID of the table that contains the index, as well as the  page count for each index:<\/p>\n\n\n\n\n\n\n
\n
 \t\tObjectName<\/b><\/p>\n<\/td>\n
\n
 \t\tObjectID<\/b><\/p>\n<\/td>\n
\n
 \t\tIndexPages<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
ProductReview<\/p>\n<\/td>\n
\n
610101214<\/p>\n<\/td>\n
\n
8<\/p>\n<\/td>\n<\/tr>\n
\n
Document<\/p>\n<\/td>\n
\n
1077578877<\/p>\n<\/td>\n
\n
13<\/p>\n<\/td>\n<\/tr>\n
\n
JobCandidate<\/p>\n<\/td>\n
\n
1589580701<\/p>\n<\/td>\n
\n
15<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Let’s look  at one more dynamic management view that is database-scoped. The  dm_db_persisted_sku_features<\/code><\/b> view returns a list of  edition-specific features that are enabled in the current database, but are not  supported on all SQL Server versions. The view applies to SQL Server 2008  through the current version. The following  SELECT<\/code><\/b> statement  uses the view to retrieve the feature name and ID:<\/p>\n
USE AdventureWorks2014;\ngo\n \nSELECT feature_name, feature_id\nFROM sys.dm_db_persisted_sku_features;\n<\/pre>\n
In this  case, the  SELECT<\/b> statement returns only one row:<\/p>\n\n\n\n\n
\n
 \t\tfeature_name<\/b><\/p>\n<\/td>\n
\n
 \t\tfeature_id<\/b><\/p>\n<\/td>\n<\/tr>\n
\n
InMemoryOLTP<\/p>\n<\/td>\n
\n
800<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 The  dm_db_persisted_sku_features<\/code><\/b> view includes the  feature_id<\/code><\/b> column only for informational purposes. The  column is not supported and may not be part of the view in the future.  <\/p>\n
Although  these are but a few of the dynamic management views that SQL Server supports,  the examples should give you a good sense of the variety of data they can  provide. For a complete list of dynamic management views and to learn more about  each one, refer to the MSDN topic Dynamic  Management Views and Functions (Transact-SQL)<\/a>.<\/p>\n
Plenty more where that came  from<\/h1>\n
As mentioned  earlier, SQL Server also provides system views to support backward  compatibility, replication, and DAC instances. The compatibility views might  come in handy if you’re still running SQL Server 2000. You might also find the  replication-related views useful if you’ve implemented replication, although  Microsoft recommends that you instead use the stored procedures available for  accessing replication metadata. As for the DAC views, SQL Server provides only  two of them, and they reside only in the  msdb<\/code><\/b> database.<\/p>\n
For many  DBAs and database developers, the catalog views and dynamic management views  will likely be their first line of defense when retrieving SQL Server metadata,  whether it’s specific to particular database objects or the server environment  as a whole. That’s not to diminish the importance of the other views, but rather  to point out that Microsoft has put most of its effort into building an  extensive set of catalog views and dynamic management views. And given all the  work that’s gone into them, there’s certainly no reason not to take advantage of  what’s available.<\/p>\n<\/p><\/div>\n","protected":false},"excerpt":{"rendered":"
When maintaining or refactoring an unfamiliar database, you’ll need a fast way to uncover all sorts of facts about the database, its tables, columns keys and indexes. SQL Server’s plethora of system catalog views, INFORMATION_SCHEMA views, and dynamic management views contain all the metadata you need, but it isn’t always obvious which views are best to use for which sort of information. Many of us could do with a simple explanation, and who better to provide one than Rob Sheldon?…<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143525],"tags":[4242,4168,4149,4500,4150,4151],"coauthors":[],"class_list":["post-2154","post","type-post","status-publish","format-standard","hentry","category-learn","tag-basics","tag-database","tag-learn-sql-server","tag-refactoring","tag-sql","tag-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2154","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2154"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2154\/revisions"}],"predecessor-version":[{"id":41270,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2154\/revisions\/41270"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2154"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2154"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2154"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2154"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}