Discovering database specific information using built in functions and dynamic management views DMVs
Discovering database specific information using built-in functions and dynamic management views DMVs
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 Latest posts by Gerald Britton (see all) Snapshot Isolation in SQL Server - August 5, 2019 Shrinking your database using DBCC SHRINKFILE - August 16, 2018 Partial stored procedures in SQL Server - June 8, 2018
SQLShack
SQL Server training EspañolDiscovering database specific information using built-in functions and dynamic management views DMVs
May 15, 2017 by Gerald BrittonIntroduction
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: 123 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: 12345678 SELECT DB_NAME() AS DatabaseName;GOUSE AdventureWorks2014;GOSELECT 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: 1234 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: 123 DB_ID(DB_NAME([database id])) = DB_ID([database name]) and 123 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: 1234567 SELECT u.NAME AS UserName , l.dbname AS DefaultDatabaseFROM sys.sysusers uJOIN sys.syslogins l ON u.sid = l.sidWHERE 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: 123 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: 12345 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: 123 SELECT sid FROM sys.database_principals WHERE name = 'dbo'; I get: 0x0105000000000005150000003704E0A8012294A2BD738156E9030000 If I try to look up that sid: 1234 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: 1234 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: 123 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: 1234567 SELECT d.name as DBName, u.nameFROM sys.databases dJOIN sys.sysusers uON d.owner_sid = u.sidWHERE 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: 12345678 SELECT SUSER_NAME(p.sid) AS Name, type_desc as [Type]FROM sys.database_principals pJOIN sys.syslogins lON p.sid = l.sidWHERE 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: 123 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 123 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: 123 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. 123 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. Other articles in this series: Discovering SQL server instance information using system views Discovering more SQL Server information using the built-in dynamic management views (DMVs) How to track SQL Server database space usage with built-in functions and DMVs Author Recent Posts Gerald BrittonGerald 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 Latest posts by Gerald Britton (see all) Snapshot Isolation in SQL Server - August 5, 2019 Shrinking your database using DBCC SHRINKFILE - August 16, 2018 Partial stored procedures in SQL Server - June 8, 2018