sql-server-2008

Renaming the physical file names for Datafiles in SQL Server

  • Update data file location
    ALTER DATABASE <your-database-name>
    MODIFY FILE (NAME = logical_file_name, FILENAME = '<full-path-of-your-mdf-file' )
  • Update log file location (optionally)
    ALTER DATABASE <your-database-name>
    MODIFY FILE (NAME = logical_file_name, FILENAME = '<full-path-of-your-ldf-file' )
  • Detach the database
  • Rename the file(s) on disk
  • Re-attach the database

Drop a list of stored procedures whose names begin with a certain string

DECLARE @SPName AS NVARCHAR(128)
       
-- For each SP...
DECLARE MyCursor CURSOR
FOR
SELECT OBJECT_NAME(M.object_id) as SPName
FROM
        sys.sql_modules M
        INNER JOIN sys.objects O ON M.object_id = O.object_id
WHERE
        O.type_desc = 'SQL_STORED_PROCEDURE'
        AND OBJECT_NAME(object_id) like '<Prefix>%'

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @SPName

WHILE @@FETCH_STATUS = 0
BEGIN
        PRINT 'Deleting SP ' + @SPName
        EXEC ('DROP PROCEDURE ' + @SPName)  
               
        FETCH NEXT FROM MyCursor INTO @SPName
END
CLOSE MyCursor
DEALLOCATE MyCursor

How to drop a list of tables, including the associated foreign key constraints

DECLARE
     @TableName AS SYSNAME
   , @RefTableName AS SYSNAME
   , @ConstraintName AS SYSNAME

-- For each table...
WHILE 1 = 1
BEGIN
   SET @TableName = NULL
   
   SELECT TOP 1 @TableName = name FROM sys.tables WHERE name LIKE '<TablePrefix>%'
   IF @TableName IS NULL
      BREAK;
       
      -- For each FK referring this table
      WHILE 1 = 1
      BEGIN
         SET @ConstraintName = NULL
                 
         SELECT
            @ConstraintName = FK.name,
            @RefTableName = OBJECT_NAME(C.parent_object_id)
         FROM
            sys.foreign_keys AS FK
            INNER JOIN sys.foreign_key_columns AS C
                        ON FK.OBJECT_ID = C.constraint_object_id
            INNER JOIN sys.tables T ON t.OBJECT_ID = C.referenced_object_id
         WHERE
            OBJECT_NAME (FK.referenced_object_id) = @TableName

         IF @ConstraintName IS NULL
            BREAK;

         PRINT 'Dropping constraint ' + @ConstraintName
         EXEC ('ALTER TABLE [' + @RefTableName + '] DROP CONSTRAINT ' + @ConstraintName)
      END        
       
      PRINT 'Dropping table ' + @TableName
      EXEC ('DROP TABLE [' + @TableName + ']') 
END

GO

List all stored procedures that are using CLR functions/procedures

The following takes each CLR function/procedures and returns all stored procedures/function that are using it:

SELECT DISTINCT
        O.name AS ClrObjectName,
        OBJECT_NAME(SM.object_id) AS IsUsedInObject,
        AM.assembly_class + '.' + AM.assembly_method AS AssemblyMethod
FROM   
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
        INNER JOIN sys.sql_modules SM ON SM.definition LIKE '%' + O.name + '%'
ORDER BY 1, 2

List of all CLR functions and procedures associated with a .NET assembly

The following script returns one row for each function, procedure or trigger that is defined by a common language runtime (CLR) assembly:

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

More details on Books online.

Get all foreign keys that refer a specific table and column

SELECT
        OBJECT_NAME(constraint_object_id) as FKName,
        OBJECT_NAME(parent_object_id) as TableName,
        COL_NAME(parent_object_id, parent_column_id) AS ColumnName,
        OBJECT_NAME(referenced_object_id) AS ReferencedTableName,      
        COL_NAME(referenced_object_id, referenced_column_id) as ReferencedColumnName   
FROM
        sys.foreign_key_columns FKC
WHERE
        OBJECT_NAME(referenced_object_id) = '<ReferencedTableName>'
        AND COL_NAME(referenced_object_id, referenced_column_id) = '<ReferencedColumnName>'
ORDER BY 1

Traversing hierarchies with nodes having multiple parents

Given a table called Relations with columns IdNode, IdParentNode that describes relations between nodes, the following query uses a recursive CTE to return all ancestors (at all levels) for all child nodes.

;WITH cteTree AS
(
    SELECT  IdNode, IdParentNode, 1 as TreeLevel
    FROM Relations

    UNION ALL

    SELECT  cteTree.IdNode, R.IdParentNode, TreeLevel + 1
    FROM
                cteTree
                INNER JOIN Relations R ON cteTree.IdParentNode = R.IdNode
)
SELECT IdNode, IdParentNode, TreeLevel
FROM cteTree
OPTION(MAXRECURSION 10)

The OPTION(MAXRECURSION 200) means that it looks only 10 levels deep. You can use OPTION(MAXRECURSION 0) if you want to traverse all the levels, though you have to make sure you don't have a circular reference.

How to get database backup history

This script will allow you to see the last performed backups and their type (full, differential or log):

SELECT
        database_name
        , [backup_start_date]
        , CASE [type]
                WHEN N'D' THEN N'Full Backup'
                WHEN N'I' THEN N'Diff Backup'
                WHEN N'L' THEN N'Log Backup'
                ELSE N'Unknown'
          END AS N'backup_type'
        , [position]
        , [name]
        , [description]
        , [first_lsn]
        , [last_lsn]
        , [backup_finish_date]
FROM
        msdb.dbo.[backupset] AS S
        INNER JOIN msdb.dbo.[backupmediafamily] AS M ON S.[media_set_id] = M.[media_set_id]
WHERE [database_name] = N'<database_name>'
ORDER BY [backup_start_date];
GO

How to check consistency of your backups - CHECKSUM

Backups should always use WITH CHECKSUM:

BACKUP DATABASE [DatabaseName]
TO DISK = N'D:\Backups\DatabaseName.bak'
WITH INIT, CHECKSUM;
GO

Check in backup header (column IsDamaged) , if it contains a corrupted database (i.e. the database was already corrupted when the backup was performed):

RESTORE HEADERONLY
FROM DISK = N'D:\Backups\DatabaseName.bak';
GO

You can test the backup integrity:

RESTORE VERIFYONLY
FROM DISK = N'D:\Backups\DatabaseName.bak'
WITH CHECKSUM;
GO

Checking backup consistency is not an alternative to regular database consistency checking via DBCC CHECKDB.

Performance troubleshooting using Waits

WITH [Waits]
AS
(SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
        N'CLR_SEMAPHORE',    N'LAZYWRITER_SLEEP',
        N'RESOURCE_QUEUE',   N'SQLTRACE_BUFFER_FLUSH',
        N'SLEEP_TASK',       N'SLEEP_SYSTEMTASK',
        N'WAITFOR',          N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'CHECKPOINT_QUEUE', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'XE_TIMER_EVENT',   N'XE_DISPATCHER_JOIN',
        N'LOGMGR_QUEUE',     N'FT_IFTS_SCHEDULER_IDLE_WAIT',
        N'BROKER_TASK_STOP', N'CLR_MANUAL_EVENT',
        N'CLR_AUTO_EVENT',   N'DISPATCHER_QUEUE_SEMAPHORE',
        N'TRACEWRITE',       N'XE_DISPATCHER_WAIT',
        N'BROKER_TO_FLUSH',  N'BROKER_EVENTHANDLER',
        N'FT_IFTSHC_MUTEX',  N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'DIRTY_PAGE_POLL')
)
SELECT TOP 10
        W1.[wait_type] AS [WaitType],
        CAST (W1.[WaitS] AS DECIMAL(14, 2)) AS [Wait (seconds)],
        CAST (W1.[ResourceS] AS DECIMAL(14, 2)) AS [Resource wait (seconds)],
        CAST (W1.[SignalS] AS DECIMAL(14, 2)) AS [Signal wait (seconds)],
        W1.[WaitCount],
        CAST (W1.[Percentage] AS DECIMAL(4, 2)) AS [Percentage],
        CAST ((W1.[WaitS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgWait (seconds)],
        CAST ((W1.[ResourceS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgResource (seconds)],
        CAST ((W1.[SignalS] / W1.[WaitCount]) AS DECIMAL (14, 4)) AS [AvgSignal (seconds)]
FROM
        [Waits] AS W1
        INNER JOIN [Waits] AS W2 ON W2.[RowNum] <= W1.[RowNum]
GROUP BY W1.[RowNum], W1.[wait_type], W1.[WaitS],
        W1.[ResourceS], W1.[SignalS], W1.[WaitCount], W1.[Percentage];
GO

Pages

Subscribe to RSS - sql-server-2008