THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Changing How To Change Your Database Properties — ALTER DATABASE

(This article was originally published at )

Long ago (in SQL Server years) many metadata changes were implemented with special one-off stored procedures. For example, we had sp_addindex and sp_dropindex, as well as sp_addtype, sp_droptype, sp_addlogin and sp_droplogin. For changing certain database properties, we had sp_changedbowner and sp_dbcmptlevel, to name a few.

Gradually, Microsoft has started replacing these procedures with the more generic DDL commands ADD, DROP and ALTER. This is both good news and bad news. It’s good because these commands are more efficient and more predictable. It’s bad because the stored procedures were made up of T-SQL code that we could read and actually learn things from! I learned a lot of what I know about SQL Server metadata in the early days by reading the definitions of the system stored procedures.

Some of the changes sort of snuck up on me. I knew for example that sp_dboption had morphed into ALTER DATABASE, but I just discovered recently that you don’t even need to specify the database name. You can use the word ‘current’ to indicate the current database. This change was introduced in SQL Server 2012, but no one told me.  For example, we can run the following:


However, this doesn’t apply to all options. And it’s not clear which ones.

The general ALTER DATABASE documentation seems to imply ‘current’  applies to all options, except for ones that aren’t even listed on the page:

ALTER Database


Now, if you look in the docs for the page for the ALTER DATABASE file options, it doesn’t show using ‘current’ at all.

However, the commands that I’ve tested, including the one to add a new filegroup, and then one to add two files to that filegroup, both seem to work using ‘current’.

And if you look at the page for the ALTER DATABASE SET options, there is a note:

alter CURRENT database

This message admits that using ‘current’ doesn’t work for every option, but it specifically doesn’t tell us which ones. It basically says “Try it and see!”.

So what about changing the database owner? That is a database property and is viewable in sys.databases. However, changing the owner uses a completely different command: ALTER AUTHORIZATION.

Even though you might think that ALTER DATABASE is all you need to change any database property, it just isn’t so.

And there’s more. Now in SQL Server 2016, you can change configuration options for a specific database. I’ll tell you about that feature next time.


Have fun!


Published Wednesday, September 20, 2017 9:50 AM by Kalen Delaney
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



drsql said:

Excellent!  I learned something the easy way today, by reading your blog!  I find there are so many features I don't see because I am too busy with other ones. I just blogged about AT TIME ZONE, and I only recently learned of the READCOMMITTEDLOCK hint.

Keeps life exciting, I reckon.

September 23, 2017 8:38 AM

Tara said:

July 17, 2018 3:50 AM

obat diabetes said:

August 1, 2018 11:14 PM

obat bronkitis anak secara alami said:

August 7, 2018 7:47 PM

ketty said: Reviews has helped many students to choose the right website. It has also helped the students to score good grades in their academics. Our writing services review will help you to find the best writing websites for your success.

August 9, 2018 2:09 AM

obat sinusitis said:

August 10, 2018 7:20 PM

masker wajah alami untuk menghilangkan jerawat said: Masker wajah alami untuk menghilangkan jerawat Obat liver alami aman dan terbaik Obat herbal sinusitis kronis terbaik Obat bronkitis anak secara alami Obat Kanker Payudara terbaru 2018 Cara meningkatkan nafsu makan secara alami Obat herbal diabetes menurunkan gula darah Obat herbal kusta terbaik Obat benjolan di leher tanpa operasi Obat Herbal Kanker Otak tuntas hingga akar Obat TBC alami aman tanpa efek samping Obat radang amandel sembuh tanpa operasi Obat asam urat alami tanpa efek sampinfg Obat tetes Sariawan secara alami Cara mengobati mata bengkak dengan cepat Obat tradisional kanker lambung terbaik Obat kanker paru paru Obat usus buntu alami tanpa operasi Obat asam lambung naik cara alami

August 20, 2018 8:56 PM

obat herbal koresterol terbaik said:

August 23, 2018 10:55 PM

obat usus buntu alami tanpa operasi said:

August 24, 2018 12:19 AM

obat benjolan di tubuh said:

August 30, 2018 7:39 PM

obat wasir said:

September 4, 2018 8:25 PM said:

Well thanks for posting such an outstanding idea. I like this blog & I like the topic and thinking of making it right.

September 5, 2018 3:29 AM

famitofu said:

Remember to play it everyday to laugh more

September 14, 2018 2:30 AM

obat katarak said:

September 16, 2018 5:44 PM

obat maag said:

September 17, 2018 7:09 PM

obat limfoma said:


September 23, 2018 6:49 PM

Michael said:

Looking  for someone write my programming assignment  here are Expert assignment helpers  are well efficient and  capable of creating unique assignments for college or university students with programming assignment help.

November 19, 2018 9:22 PM

anjunpal said:

Thanks for this article. It contains the information i was searching for and you have also explained it well. We are also a app provider that deal in the any sector. |

December 2, 2018 12:24 PM

mason said:

The Internet is day-by-day becoming the backbone of an active and ever-growing life.

January 17, 2019 7:33 AM

headfix said:

So, you have a Cisco router and you want to make some changes in your wireless/wired network settings. We’ll tell you how to access your Cisco router’s login page so that you can make the changes according to your wish.

January 21, 2019 10:34 AM

Becky Spencer said:

We all live a busy life and often we forget about important tasks or events at hand. This is why businesses use text blasts to send out reminders to the clients or customers in real-time to ensure they never miss out. Plus, this also builds a positive relationship with your audience.

February 13, 2019 10:40 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement