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

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:

SELECT DB_NAME() AS DatabaseName;

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:

SELECT DB_NAME() AS DatabaseName;

USE AdventureWorks2014;

SELECT DB_NAME() AS CurrentDBName

, ORIGINAL_DB_NAME() AS OriginalDBName;

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:

SELECT ORIGINAL_DB_NAME() AS DatabaseName

, DB_ID(ORIGINAL_DB_NAME()) AS DatabaseID;

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

DB_ID(DB_NAME([database id])) = DB_ID([database name])

and

DB_NAME(DB_ID([database name]) = DB_NAME([database id])

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:

SELECT u.NAME AS UserName

, l.dbname AS DefaultDatabase

FROM sys.sysusers u

JOIN sys.syslogins l ON u.sid = l.sid

WHERE u.NAME = USER_NAME(;

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:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS 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:

SELECT compatibility_level AS CompatabilityLevel

FROM sys.databases

WHERE name = 'AdventureWorks2014';

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:

SELECT sid FROM sys.database_principals WHERE name = 'dbo';

I get: 0x0105000000000005150000003704E0A8012294A2BD738156E9030000

If I try to look up that sid:

SELECT * from sys.syslogins

WHERE sid = '0x0105000000000005150000003704E0A8012294A2BD738156E9030000';

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:

SELECT SUSER_SNAME(owner_sid) FROM sys.databases

WHERE name = 'AdventureWorksDW2014';

I get:

Which is… me!

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

ALTER AUTHORIZATION ON DATABASE::AdventureWorksDW2014 TO sa;

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:

SELECT d.name as DBName, u.name

FROM sys.databases d

JOIN sys.sysusers u

ON d.owner_sid = u.sid

WHERE d.name = 'AdventureWorks2014DW';

Are there other users here?

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

SELECT SUSER_NAME(p.sid) AS Name, type_desc as [Type]

FROM sys.database_principals p

JOIN sys.syslogins l

ON p.sid = l.sid

WHERE type_desc in ('SQL_USER', 'WINDOWS_USER')

AND SUSER_NAME(p.sid) IS NOT NULL;

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:

All users belong to AD groups or local groups on the server (limit these!).
Only the groups have permission to login to the server or connect to a database.
In the database, database roles (to be covered later) control permissions.
Permissions are granted to roles.
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:

SELECT OBJECT_NAME(object_id) AS ObjectName, * FROM sys.partitions

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

WHERE OBJECT_NAME(object_id) NOT LIKE 'sys%'

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:

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.
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:

1
2
3

SELECT COUNT(*) FROM myschema.mytable WITH (TABLOCK)

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.

SELECT * FROM sys.database_files

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.