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

Kathleen Walker said:

Excellent! Thanks for sharing the information with us. is good way to improve LAN and WAN speed. When you are successfully completing the Netgear router setup process, then you can enjoy the live video, web brewing and more

April 15, 2019 11:35 PM

Jonathan Alex said:

There are different method to modify all the database changes and we can do this by change our database properties which is sometime needed and this blog is perfect method which is beneficial for us. Share all the method with because this company explore best content.

April 9, 2020 4:37 AM

peter said:  popular ip address

April 15, 2020 9:00 PM

International Relations Research Paper Services said:

Among other courses, international relations coursework writing services has become popular since students seek international relations research paper services and international relations essay writing services.

May 15, 2020 2:38 AM

chinwoo said:

I really need this information, I can share it on my profile? I  Will write the coppy source and author below. Thank

June 18, 2020 12:49 AM

canlitv said:

Show TV kanalı, 1 Mart 1991'de Fransa'ya 4 kişi tarafından yerleştirilmiş özel bir TV dir. Fransa'da ilk iletişimine "Başka Diyar" sloganıyla başladı. 1 Mart 1992'de Türkiye'ye geliyor. Türkiye'nin bugüne kadar kanallarla iletişim kurmaya başlaması. Kanal, 1991'den bu yana organizasyona kadar çok sayıda iyileştirme gerçekleştirdi. Bununla birlikte, eşlik eden Ciner Medya Grubu tarafından denetleniyor.

Turgay Ciner, hem medya toplantısının hem de kanalın sahibidir. 2013 yılından bu yana medya yöneticisi. Cine 5 ile bir süre kardeş istasyonu olan Show TV, Türk TV uğruna başka bir zamanın başlatıcısı oldu. Ana telekom zaman dilimi boyunca, haber bölgesinde doğal vurgular ve kabul ve izleme hızı genişledi.

Show TV Hakkında

Direktin ana kampı İstanbul'dadır. Kanal, genel olarak ve yurtdışında ana akımdır. Türkiye, tıpkı Kıbrıs, ABD, Kanada gibi Azerbaycan ile doğrudan iletişimi sürdürdü. TÜRKSAT 4A üzerinden 12219 H 6500 3/4 frekanstan bağlantı iletişimini izlemek mümkündür. Benzer şekilde, D SMART kanalı 23, Digitürk kanalı 22, Filbox 26 kanalı üzerinden izlemek de mümkündür.

Türkiye'de bugüne kadar düzenlemeyi ileten bir kanal olarak ön plana çıkıyor. Her şeye rağmen bu unsuru sürdürür ve iletişim kurduğu düzenleme ile varlığını ortaya çıkarır. Öyle ki, geçmişte iletilen TV şovları hala farklı kanallarda iletilmeye devam ediyor. İletilen düzenleme hafta içi günlerle sınırlıdır, ancak haftanın sonlarında bile. Bir düzenleme olarak iletişim kurar, ancak ek olarak farklı sınıflarda, örneğin parodi programı, dergi, rekabet, yaşam. Show TV kanalının kendi ve kardeş kanalları olarak farklı kanalları vardır.

Show Türk, Show Max, Bloomberg kanalları temel kanallar arasında. 4 Ocak 2011 tarihi ile HD yayın yapmaya başlayan kanal, iki farklı şekilde izlenebiliyor. Virtual Show TV'nin kendi sitesinde canlı olarak gözlemleme olasılığı vardır.

July 21, 2020 8:46 AM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement