Month: January 2010

SQL : Find Current Database Name

The following T-SQL will identify the name of the current Database and set the variable @dbname to the name of the database.

/* Establish Current Database Context */
declare @dbname varchar (200)
set @dbname = (SELECT DB_NAME())

This is useful when checking the existence of a table within the current database, for example:

IF object_id(@dbname +N’..tblFragStats’) IS NULL
    BEGIN
        CREATE TABLE tblFragStats (
           Date DATETIME, ObjectName CHAR (255), ObjectId INT,
           IndexName CHAR (255), IndexId INT,
           Lvl INT, CountPages INT,
           CountRows INT, MinRecSize INT,
           MaxRecSize INT, AvgRecSize INT,
           ForRecCount INT, Extents INT,
           ExtentSwitches INT, AvgFreeBytes INT,
           AvgPageDensity INT, ScanDensity DECIMAL,
           BestCount INT, ActualCount INT,
           LogicalFrag DECIMAL, ExtentFrag DECIMAL
            )
    END

This code will function on any version of Microsoft SQL, from 2000 SP4 onwards.

 SQL : Move TempDB

The following transact-SQL will move the tempdb data and log files to a different physical loation. Simply modify the FILENAME paths to suit, adding additional files where appropriate for your environment.

USE master
GO
ALTER DATABASE tempdb
    MODIFY FILE (NAME = tempdev, FILENAME = ‘N:\Data\tempdb.mdf’)
GO
ALTER DATABASE tempdb
    MODIFY FILE (NAME = templog, FILENAME = ‘N:\Data\tempdb.ldf’)
GO

After completing this you will need to take the SQL instance offline and bring it back onlin.