Useful T-SQL (MSSQL 2000, SQLEXPRESS, MSDE) Queries

Most of database jobs in the MSSQL2000 or MSSQL2005 world are done with Enterprise Manager or SQL Server Management Studio Express respectively. In some occasions where those tools are corrupted or even doesn't exist (especially if you are using MSDE) you have to use T-SQL queries.

Most useful T-SQL queries;

• How to CREATE a new database;

CREATE DATABASE DB1
ON
( NAME = DB1_dat,
FILENAME = 'C:\DB1_dat.MDF' )
LOG ON
( NAME = DB1_log,
FILENAME = 'C:\DB1_log.LDF' )
COLLATE SQL_Latin1_General_CP1254_CI_AS

• How to BACKUP a database;

BACKUP DATABASE DB1
TO DISK = 'C:\DB1.Bak'

• How to RESTORE a database from a backup file;

RESTORE DATABASE DB1
FROM DISK = 'c:\DB1.bak'

• How to DETACH a database

sp_detach_db DB1

• How to ATTACH a database

EXEC sp_attach_db @dbname = N'DB1',
@filename1 = N'c:\DB1_dat.mdf',
@filename2 = N'c:\DB1_log.ldf'

• "is dependent on column 'Description'." error:

ALTER TABLE Table1
DROP COLUMN Depth

Note: if you get such errors like;
-The object 'DF__Table1__Descript__5C37ACAD' is dependent on column 'Description'.
-The object 'CK__Table1__Height__5D2BD0E6' is dependent on column 'Height'.
-The object 'PK__Table1__5B438874' is dependent on column 'RowId'.

Let's explain the errors. The first error is raised while trying to drop column name "Description" from the table. The error states that there is an object dependent to this column. Note that the name of the object starts with "DF_" which tells that this is a "Default Constraint" defined for the column. So we have to drop this constraint object first, to avoid the error. We can do that with the following query;

ALTER TABLE Table1
DROP CONSTRAINT DF__Table1__Descript__5C37ACAD

The rest of the errors are respectively for the objects Check Constraint for the column "Height" and a Primary Key Constraint for the column "RowId". We can handle the errors running the DROP CONSTRAINT commands for each constraint.

• How to CREATE INDEX for a table

nonclustered index
CREATE NONCLUSTERED INDEX Idx_Depth
ON Table1 (Depth)

clustered indexCREATE CLUSTERED INDEX Idx_Depth2
ON Table1 (Depth)

• How to make a Case Sensitive Search;

If a database is created with default collation and the default database collation is case insensitive then the search for varchar columns will be case insensitive. To get the result we want, we can force the query to use another collation which is case sensitive.
SELECT * FROM Table1 WHERE description COLLATE Latin1_General_CS_AS = 'avalue'

• How To Shrink Database Log File

We first find the logical names;
Select name,* from sysfiles

name field gives us the logical name information. Using the logical names we run the sql below;

DBCC SHRINKFILE(Northwind_log, 1)
BACKUP LOG Northwind WITH TRUNCATE_ONLY
DBCC SHRINKFILE(Northwind_log, 1)

Continue reading →