Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
 Posted - 2008-02-04 : 20:00:08
|
As im diving into my new DBA role and reading as much as I can, I am a little confused on DMV's and system tables.I've been reading through the book "SQL Server 2005 Bible", which has been very helpful. I tend to use it in conjunction with BOL whenever I come across something I want to learn more.Last Friday, I tinkered around with DMV's, which was really cool, but I ran into something today that confused me.Basically, it was finding out the recovery model for all the DB's on the server. The code in the book was:SELECT [name], recovery_model_descFROM sys.databases; Which turned exactly what it says it will.however, I am confused.I initially thought I needed to specify something in the "[name]" section, but realized, that is not the case.My question is, why is that? How do I know when to use [] around something?I found this article:[url]http://www.databasejournal.com/features/mssql/article.php/3587906[/url]I have been reading it.I guess I am just really young and raw to T-SQL to know when to use the language corectly.Is it due to the fact that the rules or syntax is a little different when using system tables?Hope that makes sense.Thanks.TCG |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-04 : 22:30:15
|
| If object name has blank or special character or is reserved word, it needs [] around. |
 |
|
|
CShaw
Yak Posting Veteran
65 Posts |
Posted - 2008-02-05 : 00:37:15
|
| You can also use those brackets around any field name. Or table name. SELECT [name], [recovery_model_desc]FROM [dbo].[sysdatabases];There are a lot of applications that Generate SQL code and many of them use the brackets around the tables and such.On a side note, I wrote part of that book along with Paul. If you have any questions or comments you can e-mail him directly as well. He does a good job at getting those answered, and well I am sure he would be glad to hear that you like the book.Chris Shawwww.SQLonCall.com |
|
|
|
thecoffeeguy
Yak Posting Veteran
98 Posts |
Posted - 2008-02-05 : 13:39:29
|
quote:
Originally posted by CShaw
You can also use those brackets around any field name. Or table name. SELECT [name], [recovery_model_desc]FROM [dbo].[sysdatabases];There are a lot of applications that Generate SQL code and many of them use the brackets around the tables and such.On a side note, I wrote part of that book along with Paul. If you have any questions or comments you can e-mail him directly as well. He does a good job at getting those answered, and well I am sure he would be glad to hear that you like the book.Chris Shawwww.SQLonCall.com
Appreciate the help.Did not realize that I made a mistake in my initial post on the actual query.It should be:SELECT [name], recovery_model_descFROM sys.databases Which brings a quick question:1.) Looks like a "." is required when querying off sys?Appreciate the feedback and help.TCG |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-02-05 : 22:26:20
|
| They are different, dbo.sysdatabases is system table while sys.databases is view. |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-02-06 : 06:19:36
|
| The "." would be required too if the object was in the dbo schema and your default schema was another. This is nothing specific to sys but more general to schemas. |
|
|
|
|
|
|
|
|
System Tables, Dynamic Management Views: Confused