Randomness – Wallpaper/Images

Hi guys, just to mix it up a bit I thought I’d post some wallpapers I’ve come across that I could share with you all. Sources include DEVIANTART and HYBRIDWORKS. If the artwork is yours, please let me know so I can credit you 🙂

Enjoy!

How do I execute the same SQL code multiple times?

Use the command GO. The following example demonstrates how GO can be used to execute the same code multiple times.

SELECT GETDATE() AS CurrentTime GO 3

The result below will be displayed with “Result to Text” mode turned on.

CurrentTime
———————–
2008-06-20 11:52:54.200 (1 row(s) affected)

CurrentTime
———————–
2008-06-20 11:52:54.217 (1 row(s) affected)

CurrentTime
———————–
2008-06-20 11:52:54.217 (1 row(s) affected)

How do I check if a SQL Server 2005 installation has been Service Packed?

You can check whether a SQL Server 2005 installation has been service packed by running the following SQL code:

SELECT SERVERPROPERTY (‘productlevel’)

If SQL Server has been service packed, it will return with the Service Pack version installed, eg. “SP2”. If it has not been service packed, it will return with the result “RTM”.

How do I create and use a Synonym for a table in SQL Server 2005?

To create a synonym, run the following SQL code:

USE AdventureWorks;
GO
CREATE SYNONYM MyLocation
FOR AdventureWorks.Production.Location;
GO

To use the synonym, run the following SQL code:

USE AdventureWorks;
GO
SELECT TOP 5 * FROM MyLocation;
GO

To drop the synonym, run the following SQL code:

USE AdventureWorks;
GO
DROP SYNONYM MyLocation;
GO

How do I list all tables WITHOUT a primary key?

Run the following SQL code:

SELECT T.TABLE_NAME AS “Tables without PKs”
FROM INFORMATION_SCHEMA.TABLES AS T
WHERE NOT EXISTS
(SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’
AND T.TABLE_NAME = TC.TABLE_NAME)
AND T.TABLE_TYPE = ‘BASE TABLE’

How do I pad an Integer to 4 digits with leading zeros?

Run the following SQL code:

SELECT RIGHT(‘0000’ + YourValue), 4)

Note: To pad to a different number of digits, replace the ‘0000’ and the 4 as appropriate, eg. SELECT RIGHT(‘00000000’ + YourValue), 8) will pad to 8 digits.

How do I check the Default Server Collation (case-sensitivity) of a SQL Server installation?

To check the Default Server Collation of a SQL Server installation, run the following SQL query:

sp_helpsort

You should get a result similar to this:

Note: To check the collation of a particular database, right click on the database in SQL Server Enterprise Manager (2000) or Management Studio (2005) and click Properties. The collation will be displayed in the Collation Name field.

How do I list all Primary Keys in a SQL Server 2000/2005 database?

Select the database you wish to list primary keys from and run the following SQL query:

SELECT
kcu.TABLE_SCHEMA AS ‘Schema’,
kcu.TABLE_NAME AS ‘Table’,
kcu.CONSTRAINT_NAME AS ‘Constraint Name’,
kcu.COLUMN_NAME AS ‘Column’
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as kcu
ON kcu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
AND kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
AND kcu.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND kcu.TABLE_NAME = tc.TABLE_NAME
WHERE
tc.CONSTRAINT_TYPE = ‘PRIMARY KEY’
ORDER BY
kcu.TABLE_SCHEMA, kcu.TABLE_NAME, kcu.CONSTRAINT_NAME

After running this script you should get a table listing the Schema, Table, Constraint Name and the Column for each Primary Key specified.

The screenshot below shows what results you will get if you run it on the AdventureWorks database:

How do I shrink/truncate the transaction log?

Every DBA’s nightmare… Your boss is running around the office with his hands waving in the air “We’ve run out of disk space! We need more disk space!”. After a bit of investigation you realise the transaction log has blown up to a disastrous size! But don’t stress, it is possible to truncate, or shrink the log file.

Note, however, that you will lose your current transaction log. All transaction logs between your last full backup and the time that you run the script below will be useless. If you need to restore, you will need to use your last full backup, or wait for your next full backup.

You will then need to run two SQL scripts:

a) Backup the current transaction log to NULL

BACKUP LOG DBName WITH TRUNCATE_ONLY

Where DBName is the name of the database whose transaction log you are trying to decrease in size.

b) Shrink the transaction log

DBCC SHRINKFILE(DBName_Log, 1)

Where DBName_Log is the name of the transaction log you are trying to shrink. This can be found on the Transaction Log tab of the Database Properties window.

Note: Once you have finished this task, it is recommended that you perform a Full backup of your database as soon as possible.

Welcome!

Hi everyone and welcome to DBA 4 Life!

I’m a DBA (for those that don’t know, DBA stands for Database Administrator) and I can’t recall how many times I’ve written a script or ran a query that I’d have to use over and over again. The problem is, whenever I need to do something it’s hard to remember how I did it previously. So this blog is as much for myself as it is for you.

Here, I can share what I’ve learnt with you all, and hopefully help you figure out how to run that tricky query, generate that ad hoc report, or restore that database that you’ve accidently blown away…