Collect information from system metadata

May include but is not limited to: Dynamic Management Views (DMVs), catalog views

SQL Server always provided ways to access metadata describing your database objects. The SQL Server 2008 ways to do this is to query Dynamic Management Views and catalog views.

Before we move further, let’s describe metadata. Metadata is meta that describes data.

Catalog views provide a way to query database objects, and server configuration options in a very similar way you are doing it with actual tables. The obsolete method doing this was querying INFORMATION_SCHEMA views. Catalog views are far more superior, because they let you query results and use them in joins, offers server-specific information, and provides much more information.

We’ll query the following: table, column and index metadata. To query table metadata, use the sys.tables, sys.columns, sys.schemas and sys.types views. The syntax is the standard SQL:

SELECT * FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id

The code above retrieves all tables with their columns residing in the current database.

Information about indexes can be retrieved by using the sys.indexes, sys.index_columns catalog views.

Dynamic Management Views give you access to internal server state information. There are more than eighty of them, and you can easily identify them, because they are members of the sys schema, and their names start with the prefix dm_.

You can retrieve a great deal of information by using them, regarding session, connection, executing SQL, server resources, and unused indexes, just to name a few.

I don’t think it would do any good if I’d provide a list of these views, because MSDN did this job in a more effective way already. Just play around with these views, and you’ll gain enough experience on them for the exam, too.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s