Gerald Britton

Discovering database specific information using built-in functions and dynamic management views (DMVs)

May 15, 2017 by

Introduction

In the last two articles on dynamic management views in SQL Server, Discovering SQL server instance information using system views and Discovering more SQL Server information using the built-in dynamic management views (DMVs), we used DMVs to discover a fair bit of information about the SQL Server instance we’re connected to. In this article, we’ll begin diving in to database specifics. There is a lot of territory to cover! We’ll also use several of the built-in functions that come with SQL Server.

Where am I?

A good place to start is to figure out what database you are connected to, if any. That part is easy:

The DB_NAME function returns the name of the database to which you are currently connected, if you do not specify any parameters. Before you run this, though, open a new connection to SQL Server in SSMS, right-click the instance node in object explorer (just above Databases) and select “New Query”. In the query window that opens, type the command above. When I do that I get:

Since I’m not connected to any database, that tells me that the default database for my user id is “master”. If, however, I do the same operation after right-clicking on a specific database – e.g. AdventureWorks2014 – the results now show:

Unsurprising, to say the least! There’s another function, ORIGINAL_DB_NAME, which can display different results. It shows the database to which you were connected when the connection was first made. To show how they can be different, first open a new connection and explicitly specify a database. I chose “master”. Then run a script like this one:

You should see something like this:

Notice that SSMS reports both the original and current database names.

Every database also has an id value, which is an integer. In fact, all objects in SQL Server have an id. Some (like database objects) are used so frequently that they have a dedicated function to retrieve it. So there is a DB_ID function to return the id of a database given the name, or the current database id, if the name is omitted. The ORIGINAL_DB_NAME function does not have a corresponding ORIGINAL_DB_ID function, but we can get the id easily enough like this:

An interesting side-note is that DB_ID and DB_NAME are complementary. That is:

and

For any given database, even if you omit the parameters (in which case the functions apply to the current database.

By the way, if you’re not sure what the default database is for your SQL Server login id, this little query will expose it:

How is this database configured?

SQL Server has lots of properties for configuring the behavior of a database. You can, of course view them at any time in SSMS but this article is all about using the built-in functions to gather information. The function DATABASEPROPERTYEX is the one you want to use here. There is a long and growing list of database properties so we won’t look at all of them. To whet your appetite, here are some I find interesting:

Collation Default database collation
Comparison Style Ignore or respect case, accents Kana and width
Recovery Recover mode
Status Offline, recovering, restoring etc.
Updateability Read only or read write

For example, using AdventureWorks as a database, this will show me the collation:

Result:

These properties are also exposed by the system catalog view sys.databases. There is at least one property that you can see using the latter that you is not shown by the former: Compatibility Level

So, for AdventureWorks, I can run:

Whether you should use DATABASEPROPERTYEX or sys.databases depends on your use case. For simple, single-property enquiries, I find the function easier to use. Get familiar with both!

To see:

Who owns this database?

In SQL Server, every database has an owner. By default, if you create a new database, the owner will be the login id you are currently using. If you are restoring or attaching a database from another server, the owner might not be so obvious. The first thing to understand is that SQL Server uses a level of indirection to record the owner of a database. To put it simply, a database is owned by one of the database principles and that principle is associated with a particular login. The database principle that is the owner is always dbo.

For example, I have the AdventureWorks2014 database I restored from CodePlex. If I look for dbo in the database principles:

I get: 0x0105000000000005150000003704E0A8012294A2BD738156E9030000

If I try to look up that sid:

I get no results! Why? Because I restored the database, which was created on another server. The sid associated with the database principle dbo does not exist on my server. So, the sid in sys.database_principles might not always be accurate. However I can use this query instead:

I get:

Which is… me!

To reconcile this situation, I can change the ownership of the database easily:

Note: Making sa the owner of your databases is usually a good idea. The sa login is restricted and login is disabled by default. Now this query will work properly:

Are there other users here?

We can use sys.database_principals to see if there are other users with permissions in the database:

If this query returns rows, it may warrant a closer look. Does it make sense that actual SQL Server or individual Windows logins have specific permissions in your database? Often, it does not make sense at all! There is a good principle known as double-abstraction which works like this:

  1. All users belong to AD groups or local groups on the server (limit these!).
  2. Only the groups have permission to login to the server or connect to a database.
  3. In the database, database roles (to be covered later) control permissions.
  4. Permissions are granted to roles.
  5. Groups are added to roles.

If the query above shows an end-user, whether Windows or a SQL Server login, its time to ask serious questions. Each such user is a potential security hole and a maintenance headache. Managing users at the group level and permissions at the role level, then joining the two by adding groups to roles, makes auditors happy (well, not as unhappy as usual!) and user maintenance much easier.

In a future article, we’ll dig into what permissions are actually assigned to users, groups and roles. For now, let’s look at another area of interest, storage.

Where is everything?

Objects in databases live in partitions. For simple databases, you might not even notice, since everything lives in the PRIMARY partition. The view sys.partitions gives us insight into what is where:

The first time you run this, you’ll see a long list of objects beginning with “sys”. If you want to see non-system items, add

Now, you should be able to see some table and index names. On my test machine, in the AdventureWorks database, I see results beginning with:

There are a number of interesting items here, but let’s look at just two for now:

  1. Index_id has just three values, 0, 1, or 2 for heaps, clustered indexes, and non-clustered indexes respectively. So, I can tell that “Currency” is a clustered index (a table) and DatabaseLog is a non-clustered index.

  2. Rows is the approximate number of rows in the partition for the object in that row of the results. Sometimes the approximate number of rows is enough. On a busy, large table, the actual number of rows may be changing minute by minute or even second by second or even faster. To obtain the actual number of rows for a table at some point in time you’d need a query like this:

    However, that’s the sort of query you don’t want to run against a busy table! It’s nice to know that you can a get “good-enough” row count from sys.partitions. If you use this method, and you also have more than one partition, be sure to add up the row counts for the tables or indexes you’re interested in.

The last system view I’ll introduce in this article is sys.database_files. It shows, surprise! The files used by the database. This may be a list of just two files or a much longer list if you use multiple partitions.

Will get you started.

Next time, I’ll dig into user permissions, a somewhat complicated but extremely important topic, especially in today’s security-conscious world.

Other articles in this series:

References

Useful link


Gerald Britton

Gerald Britton

Gerald Britton is a Senior SQL Server Solution Designer, Author, Software Developer, Teacher and a Microsoft Data Platform MVP. He has many years of experience in the IT industry in various roles.

Gerald specializes in solving SQL Server query performance problems especially as they relate to Business Intelligence solutions. He is also a co-author of the eBook "Getting Started With Python" and an avid Python developer, Teacher, and Pluralsight author.

You can find him on LinkedIn, on Twitter at twitter.com/GeraldBritton or @GeraldBritton, and on Pluralsight

View all posts by Gerald Britton
Gerald Britton
SQL Commands

About Gerald Britton

Gerald Britton is a Senior SQL Server Solution Designer, Author, Software Developer, Teacher and a Microsoft Data Platform MVP. He has many years of experience in the IT industry in various roles. Gerald specializes in solving SQL Server query performance problems especially as they relate to Business Intelligence solutions. He is also a co-author of the eBook "Getting Started With Python" and an avid Python developer, Teacher, and Pluralsight author. You can find him on LinkedIn, on Twitter at twitter.com/GeraldBritton or @GeraldBritton, and on Pluralsight View all posts by Gerald Britton

133 Views