Paul Fenton SQL

SQL code I happen to find useful

Database Restore History

without comments

A script to list restores for a given database. This is potentially especially useful for troubleshooting log shipping.

DECLARE @dbname sysname , @days int
SET @dbname = 'MY_DB_NAME' --substitute for whatever database name you want
SET @days = -7 --previous number of days, script will default to 30
SELECT
      rsh .destination_database_name AS [Database],
      rsh .user_name AS [Restored By],
       CASE WHEN rsh.restore_type = 'D' THEN 'Database'
       WHEN rsh. restore_type = 'F' THEN 'File'
       WHEN rsh. restore_type = 'G' THEN 'Filegroup'
       WHEN rsh. restore_type = 'I' THEN 'Differential'
       WHEN rsh. restore_type = 'L' THEN 'Log'
       WHEN rsh. restore_type = 'V' THEN 'Verifyonly'
       WHEN rsh. restore_type = 'R' THEN 'Revert'
       ELSE rsh. restore_type END AS [Restore Type],
      rsh .restore_date AS [Restore Started],
      bmf .physical_device_name AS [Restored From],
      rf .destination_phys_name AS [Restored To]
       FROM msdb. dbo.restorehistory rsh
       INNER JOIN msdb.dbo .backupset bs ON rsh.backup_set_id = bs.backup_set_id
       INNER JOIN msdb.dbo .restorefile rf ON rsh.restore_history_id = rf.restore_history_id
       INNER JOIN msdb.dbo .backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
       WHERE rsh. restore_date >= DATEADD(dd , ISNULL (@days, -30 ), GETDATE()) --want to search for previous days
       AND destination_database_name = ISNULL( @dbname, destination_database_name) --if no dbname, then return all
       ORDER BY rsh.restore_history_id DESC
GO

Written by Paul

April 7th, 2016 at 10:14 am

Posted in Uncategorised

Rename all constraints

without comments

This is a script that I’ve used in the past to try to clear up a situation where every single developer on the database has randomly chosen their own naming convention for constraints…

(Original author: Luis Cazares).

-------------------------------------------------------------------------------------------------
-- Purpose: The following script renames the constraints with the following naming convention:
--           - Primary Keys: PK_TableName
--           - Foreign Keys: FK_TableName_ReferencedTableName[_ColumnName] (Column name is only included when a table is referenced more than once)
--           - Defaults: DF_TableName_ColumnName
--           - Unique Constraints: UQ_TableName[_Num] (Adds a numeral if more than one UQs exist in a table)
--           - Check Constraints: CK_TableName_ColumnName
-- Author : Luis Cazares
-------------------------------------------------------------------------------------------------
DECLARE @SQL varchar (600);

DECLARE RenamingCur CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
----Primary Keys
SELECT 'EXEC sp_rename ' +
    QUOTENAME( d .name, '''') + ', ' +
    QUOTENAME( 'PK_' +   OBJECT_NAME( d.parent_object_id ), '''' ) + ', ''OBJECT'';'
FROM sys .key_constraints d
WHERE type = 'PK'
UNION ALL
----Foreign Keys
SELECT 'EXEC sp_rename ' +
    QUOTENAME( f .name, '''') + ', ' +
    QUOTENAME( 'FK_' +   OBJECT_NAME( f.parent_object_id ) + '_' + OBJECT_NAME( f .referenced_object_id)
        + CASE WHEN COUNT(*) OVER( PARTITION BY f.parent_object_id , f.referenced_object_id) > 1
                THEN '_' + COL_NAME(fc .parent_object_id, fc.parent_column_id)
                ELSE '' END, '''')   + ', ''OBJECT'';'
FROM sys .foreign_keys f
JOIN sys .objects r ON f. referenced_object_id = r .object_id
JOIN sys .foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
UNION ALL
----Defaults
SELECT 'EXEC sp_rename ' +
    QUOTENAME( d .name, '''') + ', ' +
    QUOTENAME( 'DF_' +   OBJECT_NAME( d.parent_object_id ) + '_' + COL_NAME(d .parent_object_id, d.parent_column_id ), '''' ) + ', ''OBJECT'';'
FROM sys .default_constraints d
UNION ALL
----Unique Constraints
SELECT 'EXEC sp_rename ' +
    QUOTENAME( d .name, '''') + ', ' +
    QUOTENAME( 'UQ_' +   OBJECT_NAME( d.parent_object_id )
        + ISNULL ( NULLIF ( '_' + CAST( ROW_NUMBER() OVER(PARTITION BY d.parent_object_id ORDER BY i .column_id) AS VARCHAR(3 )), '1' ), '' ), '''')
        + ', ''OBJECT'';'
FROM sys .key_constraints d
JOIN sys .index_columns i ON d. parent_object_id = i .object_id AND d.unique_index_id = i.index_id AND key_ordinal = 1
WHERE d. type = 'UQ'
UNION ALL
----Check Constraints
SELECT 'EXEC sp_rename ' +
    QUOTENAME( d .name, '''') + ', ' +
    QUOTENAME( 'CK_' +   OBJECT_NAME( d.parent_object_id ) + '_' + COL_NAME(d .parent_object_id, d.parent_column_id ), '''' ) + ', ''OBJECT'';'
FROM sys .check_constraints d ;

OPEN RenamingCur;
FETCH NEXT FROM RenamingCur INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@SQL );
    FETCH NEXT FROM RenamingCur INTO @SQL;
END
CLOSE RenamingCur;
DEALLOCATE RenamingCur;

Written by Paul

January 22nd, 2016 at 12:23 pm

Posted in Uncategorised

Create a database snapshot

with one comment

This is a script I use to create a database snapshot. It’s based on something I found elsewhere on the internet. Changes I’ve made are to correct it so that it works if the database has more than one data file, and also to change the name of the snapshot taken so that successive snapshots are called DBNAME_ss, DBNAME_ss_1, DBNAME_ss_2 etc.

Create procedure [dbo].[Create_UserDb_Snapshot]
@database varchar( 50),
@exec_mode bit= 0
as
begin
       set nocount on
       declare @err_text nvarchar (200)
       if @database in ('master' ,'model' , 'tempdb' )
       begin
             set @err_text = 'Snapshot is not allowed for master, model and tempdb databases.'
             RAISERROR(@err_text ,10, 1)
             return;
       end
       if object_id ('tempdb..#usr_db_files_tbl' ) is not null
          drop table #usr_db_files_tbl
       create table #usr_db_files_tbl(
              [logicalName] varchar(100 ),
              [physicalName] varchar(200 )
        )
       insert into #usr_db_files_tbl(logicalName ,physicalName)
       select [mf]. [name],[mf] .[physical_name]
       from sys .master_files as [mf]
       where [mf]. [database_id]=db_id (@database ) and [mf] .[type] = 0 and [mf]. [name] not in ( 'master', 'model','tempdb' )

       if (select count(*) from #usr_db_files_tbl )=0
       begin
             set @err_text = 'The specified database does not exist.'
             RAISERROR(@err_text ,10, 1)
             return;
       end

       declare @dyn_sql nvarchar (max)
       declare @extension varchar (20 )= 'snap'+cast ( cast(RAND ()*10000 as int ) as varchar( 4))
       declare @curLogicalName varchar (100), @curPhysicalName varchar(200 )     

       declare snap_cursor cursor for
       select [t]. [logicalName], [t] .[physicalName] from #usr_db_files_tbl as [t]
       open snap_cursor

       declare @snapDbName nvarchar (100)
       declare @i integer = 0
       set @snapDbName= @database+ '_ss'
       while exists (select 1 from sys.databases where name = @snapDbName)
       begin
              set @i = @i + 1
              set @snapDbName= @database+ '_ss_' + CAST(@i as varchar(2))
       end

       fetch next from snap_cursor into @curLogicalName, @curPhysicalName
       set @dyn_sql= 'CREATE DATABASE '+ @snapDbName +' ON ' + char (10 )
       while (@@FETCH_STATUS =0 )
       begin
             set @dyn_sql+= '(name='''+ @curLogicalName +''', filename='''+replace( replace( @curPhysicalName ,right(@curPhysicalName , 4),'.' +@extension),right( @curPhysicalName,4 ),'.'+ @extension)+''')'
             SET @extension = 'snap'+cast ( cast(RAND ()*10000 as int) as varchar(4 ))
             fetch next from snap_cursor into @curLogicalName,@curPhysicalName
             if (@@FETCH_STATUS =0 )
             set @dyn_sql+= ', '+ char (10 )
       end

       set @dyn_sql+= char( 10 )
       set @dyn_sql+= 'AS SNAPSHOT OF '+ @database +char ( 10)

       if @exec_mode= convert( bit , 0)
        print char (10)+ @dyn_sql

       if @exec_mode= convert( bit , 1)
       begin
              begin try
               exec sp_executesql @dyn_sql
               print 'Snapshot database: ' +@snapDbName + ' created successfully.'
              end try
              begin catch
               set @err_text = 'Snapshot for database '+ @database+' cannot be created.'
               RAISERROR(@err_text ,16, 1)
              end catch
       end

       close snap_cursor
       deallocate snap_cursor
       drop table #usr_db_files_tbl
       WAITFOR DELAY '00:00:01';
       return;

end

Remember that you need SQL Server Enterprise Edition (or Developer Edition, which is really the same thing) to use database snapshots. You can then do

EXEC master .[dbo]. [Create_UserDb_Snapshot] @database = 'myDB' , @exec_mode = 1

to easily take a snapshot of any database.

Written by Paul

October 10th, 2015 at 10:20 am

Posted in Uncategorised

Is a particular SQL Server Agent job running?

without comments

I had a situation recently where I had to find out if a particular SQL Server Agent job was running, and if so, was it executing a particular step at the time?

This is what I came up with and was using:

ALTER PROC [dbo].[usp_is_job_running] @JobName sysname, @StepNumber int = NULL, @Result int = 0 OUTPUT
AS
BEGIN

CREATE TABLE #Temp1
(
	job_id uniqueidentifier NOT NULL,
	last_run_date nvarchar ( 20) NOT NULL,
	last_run_time nvarchar ( 20) NOT NULL,
	next_run_date nvarchar ( 20) NOT NULL,
	next_run_time nvarchar ( 20) NOT NULL,
	next_run_schedule_id INT NOT NULL,
	requested_to_run INT NOT NULL,
	request_source INT NOT NULL,
	request_source_id sysname
	COLLATE database_default NULL,
	running INT NOT NULL,
	current_step INT NOT NULL,
	current_retry_attempt INT NOT NULL,
	job_state INT NOT NULL)

DECLARE @job_owner    sysname
DECLARE @is_sysadmin    INT

SET @is_sysadmin    = isnull ( is_srvrolemember ( 'sysadmin' ), 0 )
SET @job_owner    = suser_sname ()

INSERT INTO #Temp1
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin , @job_owner

UPDATE #Temp1
SET last_run_time  = right ( '000000' + last_run_time, 6 ),
	next_run_time  = right ( '000000' + next_run_time , 6);

SELECT
	j.name AS JobName ,
	j.enabled AS Enabled ,
	CASE x.running
		WHEN 1 THEN    'Running'
	ELSE
		CASE h.run_status
			WHEN 2 THEN 'Inactive'
			WHEN 4 THEN 'Inactive'
		ELSE 'Completed'
		END
	END AS CurrentStatus ,
	coalesce ( x.current_step , 0) AS CurrentStepNbr ,
	CASE
		WHEN x.last_run_date > 0
		THEN convert ( datetime , substring ( x .last_run_date , 1, 4 ) +
			'-' + substring (x . last_run_date, 5 , 2 )    +
			'-' + substring (x . last_run_date, 7 , 2 ) +
			' '    + substring (x . last_run_time, 1 , 2 ) +
			':' + substring (x . last_run_time, 3 , 2 )    +
			':' + substring (x . last_run_time, 5 , 2 ) + '.000' ,121)
		ELSE
			NULL
	END AS LastRunTime ,
	CASE h . run_status
		WHEN 0 THEN 'Fail'
		WHEN 1 THEN 'Success'
		WHEN 2 THEN 'Retry'
		WHEN 3 THEN 'Cancel'
		WHEN 4 THEN 'In progress'
	END AS LastRunOutcome ,
	CASE
		WHEN h . run_duration > 0 THEN
		(h.run_duration / 1000000 ) * ( 3600 * 24 )
			+ ( h .run_duration / 10000 % 100) * 3600
			+ ( h .run_duration / 100 % 100) * 60
			+ ( h .run_duration % 100 )
		ELSE
		NULL
			END AS LastRunDuration
	INTO #RunningJobs
FROM
	#Temp1 x
	LEFT JOIN msdb.dbo.sysjobs j ON x.job_id = j.job_id
	LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
	LEFT OUTER JOIN msdb.dbo.sysjobhistory h
	ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
WHERE x.running = 1

IF EXISTS (SELECT 1 FROM #RunningJobs WHERE JobName = @JobName AND (@StepNumber IS NULL OR CurrentStepNbr = @StepNumber))
	SELECT @Result = 1
ELSE
	SELECT @Result = 0

END

However since publishing that, I’ve been pointed at the basis ofa much neater solution here

http://sqlstudies.com/2013/09/05/a-t-sql-query-to-get-current-job-activity/

So this is what I’m using from now on:

ALTER PROC [dbo].[usp_is_job_running] @JobName sysname, @StepNumber int = NULL, @Result int = 0 OUTPUT
AS
BEGIN

IF EXISTS(
	SELECT
		ja.job_id,
		j.name AS job_name,
		ja.start_execution_date,
		ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
		Js.step_name
	FROM msdb.dbo.sysjobactivity ja
	LEFT JOIN msdb.dbo.sysjobhistory jh
		ON ja.job_history_id = jh.instance_id
	JOIN msdb.dbo.sysjobs j
		ON ja.job_id = j.job_id
	JOIN msdb.dbo.sysjobsteps js
		ON ja.job_id = js.job_id
		AND ISNULL(ja.last_executed_step_id,0)+1 = js.step_id
	WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY agent_start_date DESC)
	AND start_execution_date is not null
	AND stop_execution_date is null
	AND j.name = @JobName
	AND  (@StepNumber IS NULL OR ISNULL(last_executed_step_id,0)+1  = @StepNumber)
)
	SELECT @Result = 1
ELSE
	SELECT @Result = 0 

END
go

 

Written by Paul

September 23rd, 2015 at 9:11 am

Posted in Uncategorised

SQL Resources

without comments

Some of the most useful SQL resources that I frequently refer to:

http://stackoverflow.com/

http://forums.sqlteam.com/

http://www.sqlservercentral.com

 

Written by Paul

September 8th, 2015 at 3:55 pm

Posted in Uncategorised

SQL Server Datetime formats

without comments

Mainly for my own reference, these are the SQL Server standard datetime formats. I can literally remember none of these.

Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM

 

Written by Paul

September 8th, 2015 at 8:45 am

Generate CREATE/DROP statements for all triggers of a given type

without comments

Presumably there’s an easy way of doing this via SSMS but I ran out of patience and found a genuinely useful script somewhere on the internet (if you wrote it, let me know so I can give credit!).

DECLARE @t VARCHAR (MAX)
SET @t = ''

SELECT @t = @t + 'IF EXISTS (SELECT 1 FROM sys.triggers WHERE object_id = OBJECT_ID(N''' + s.name + '.' + o.name +'''))
DROP TRIGGER ' + s.name + '.' + o.name + '
GO
' + OBJECT_DEFINITION (OBJECT_ID( s.name + '.' + o.name )) +'
GO
'
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.objects o2 ON o.parent_object_id = o2.object_id
WHERE o. [type] = 'TR'
AND (
OBJECTPROPERTY ( o.object_id , 'ExecIsInsertTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsUpdateTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsDeleteTrigger' ) = 1
)

SELECT @t AS [processing-instruction(x)] FOR XML PATH ('')

It’s obvious how to edit it to filter for a particular trigger type.

Why is it returning the results as XML? Well if you just SELECT the results you end up with a single unformatted line. If you PRINT it out you’ll end up with long strings being truncated abruptly after 8000 characters.

You get a Results pane like this:

20150901_Screenshot_of_output_from_XML_SELECT_in_trigger_export

Click on the hyperlink to display the whole statement. You’ll need to trim off the <?x at the start and the x?> at the end.

Written by Paul

September 1st, 2015 at 6:54 pm

Posted in Uncategorised

Tagged with , , ,