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…