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 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 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.