sp_msforeachdb

This allows the ability to write a SQL statement for all databases.

Example:

If I want to backup all databases on my SQL Box…

EXEC sp_msforeachdb 'BACKUP DATABASE ? TO DISK = ''E:\ChrisReederTemp\?.bak'' WITH FORMAT'

Notice the question mark in the SQL statement. This is the wildcard for the database name.

Jon Galloway has a pretty good post on sp_msforeachdb with a great example of backup and restores.

Get it while it is hot!!!

ftp://ftp.red-gate.com/sqlpromptbeta/SQLPrompt3.5.0.000323.exe

Information on the release here.

Do you ever need/want to monitor if your Transaction Log on SQL Server is getting full???

If so, here is a way to do it:

DBCC SQLPERF(logspace) 

Below is an example of the output from Books Online:

Database Name Log Size (MB) Log Space Used (%) Status ------------- ------------- ------------------ ----------- pubs 1.99219 4.26471 0 msdb 3.99219 17.0132 0 tempdb 1.99219 1.64216 0 model 1.0 12.7953 0 master 3.99219 14.3469 0  

Funny site note: That status column… it will always be zero according to books online. He He…

If you have the same curiosity about the actual data file(s)…

sp_spaceused

Results from the master database:

 database_name database_size unallocated space ------------- ------------- ----------------- master 16.63 1.63 MB reserved data index_size unused -------- ------ ----------- -------- 14592 KB 10592 KB 1344 KB 2656 KB 

You can find other great DBCC commands here.

Tuesday, July 10, 2007, is our next meeting of Pee Dee Area .NET User’s Group. 

Chris Craft, from Taylor Data Systems, and Page Brooks, from ACS Technologies, will be presenting a follow up introduction to Silverlight, Microsoft’s new platform for delivering rich internet applications (RIAs).

We will further explore Silverlight and related technologies.

Here is the tentative schedule:

6:00 PM – 6:20 PM Socializing / Dinner
6:20 PM – 6:30 PM Introduction, Sponsor Time, and News
6:30 PM – 7:45 PM Presentation on Silverlight
7:45 PM – 8:00 PM Drawing and Wrap Up

Please subscribe to the PDANUG Upcoming Events and News Feed here.

Owner Qualification in SQL? Huh?

Depending on your SQL setup and user accounts, it is possible to have the same table name in a database with different owners. I would not advise it, but SQL allows for it… And I am sure there is a reason for this…

Examples:

dbo.Employees
chris.Employees
sqlhero.Employees

Which table does this statement  get data from?

SELECT *
FROM Employees

Answer… It depends on your SQL username. When doing a SELECT, you could get the wrong table. (Also applies to other object types like stored procedures, views, etc)

So, it is generally a good idea to qualify the owner of your SQL objects.

SELECT *
FROM dbo.Employees

Anyway… the primary reason for the post…

There is a great article on sqlblog.com discussing an even more “real world” reason for explicitly qualifying the owner. PERFORMANCE!

http://sqlblog.com/blogs/linchi_shea/archive/2007/07/05/performance-impact-of-procedure-calls-without-owner-qualification-sql-server-2000.aspx

In the post, Linchi Shea does a great job of explaining the performance impact of fully qualifying your object owners. In his example, you will see in SQL 2000 it makes a BIG difference in performance. SQL 2005 is a little faster too. But, every little bit helps when you have thousands of users calling the same procedure!

Microsoft will stop supporting SQL Server Service Pack 3a next Tuesday, July 10th.

Be sure you update your SQL Server Instances to SP4.

Call me Stupid… But I never knew this…

I had a query that was returning more data than I expected. The goal was to find all files that started with ‘backup_’ (literal underscore) and end with ‘.zip’ extension.

TSQL:

SELECT *
FROM MyFILES
WHERE MyFileName LIKE ‘backup_%.zip’

Results:

backuptemp.zip
backup_2007_07_01.zip
backup_2007_07_02.zip

Argggggggg!!!

I don’t want the temp backup!!!

Here is the problem. The underscore (”_”) is a wildcard character for the LIKE clause. You can find a really good description of the LIKE clause here. (on a sybase site)

So, how do you use a wildcard as a literal in the like clause? Simply wrap the wildcard in braces LIKE below.

SELECT *
FROM MyFILES
WHERE MyFileName LIKE ‘backup[_]%.zip’

Side note: What exactly does the underscore do?

SELECT *
FROM MyFILES
WHERE MyFileName LIKE ‘_mail%’

Will return files like:

gmail.jpg
email.jpg
zmail.jpg
emailbackups.zip

I allows for a wildcard, but for one character.