Dynamic SQL Templates

I am sure that you have heard about SQL Templates by now, after all they have been around for a while. However, the scripting tasks have become a little more challenging with all the lovely new features of the latest version of SQL SERVER (2005). Therefore, the availability of the templates, and their effective exploitation, is even more important.

I sometimes give my students tasks to automate and allow them to use any features that exist on the server to aid them. Then I make sure they have the scripts to achieve the tasks by the end of the course, or soon after if it has been a particularly challenging class ;-)

I have found that taking the existing scripts and showing the students how you can make them dynamic adds a lot of value for a lot of the students. It also gives them lots of ideas of things they can do to help others back at work.

I will be including examples of some of these dynamic templates in future blogs as well. This is why:-

Firstly, as a value added element for the students that would have liked to have completed the extra exercises but didn't have time.

Secondly, to suggest further ways for the scripts to be extended, for the students that managed to complete the tasks during the course.

Lastingly, to ensure that both sets continue their learning process back at work and to feed the enthusiasm they often demonstrate back into their work environment.

Dynamic Snapshot Template

The purpose of this script is to create a dynamic snapshot of a given database. It is presented as a starting point to progress your learning about the way that Templates can be used to ease your administration tasks!

-- =============================================

--

A Dynamic Snapshot Template

-- =============================================

USE master

GO

-- Drop snapshot database if it already exists

IF EXISTS

(

SELECT name

FROM sys.databases

WHERE name = N'<Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>'

)

DROP DATABASE <Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>

GO

-- Create the snapshot database

USE <Database_Name, sysname, Database_Name>

GO

DECLARE @table TABLE (

name VARCHAR(20)

, fileid INT

, filename VARCHAR(200)

, filegroup VARCHAR (20)

, size VARCHAR(20)

, maxsize VARCHAR(20)

, growth VARCHAR(20)

, usage VARCHAR(10)

)

INSERT INTO @table EXEC SP_HELPFILE

DECLARE

@DynamicSQL VARCHAR(8000)

SELECT

@DynamicSQL = 'CREATE DATABASE <Database_Name, sysname,

Database_Name>_<Snapshot_Id,,Snapshot_ID>ON' + CHAR(10)

SELECT @DynamicSQL = @DynamicSQL

+ '( NAME ='

+ name

+ ', FILENAME = '

+ ''''+filename

+ '.ss'''+')'

+ + char(13)

+ ',' FROM @Table WHERE name NOT LIKE '%log%'

SELECT @DynamicSQL = LEFT(@DynamicSQL,len(@DynamicSQL)-1) + ' AS SNAPSHOT OF <Database_Name, sysname, Database_Name>'

EXECUTE (@DynamicSQL)

-- =============================================

--

A Dynamic Snapshot Template

-- =============================================

When you encounter <A,B,C> within the script

<>contains the whole section that will be replaced within the script when you choose the option shown below from the Query options

Screen clipping 1

A is the prompt for the user

B is the Data Type of the value being expected

C is the default value that will be used if a value is not provided

So given

SELECT name

FROM sys.databases

WHERE name = '<Database_Name, sysname, Database_Name>_<Snapshot_Id,,Snapshot_ID>'

There are two parameters expected.

The first request is for Database_Name which is to be of type sysname and a value of Adventureworks has been entered for it.

The second parameter expected is Snapshot_id, it will default to text type and has not yet been provided. If it is not provided then the value Snapshot_ID will be used in the script.

Screen clipping 2

If OK was to be pressed in the current state then the inserted line would look as follows:-

SELECT name

FROM sys.databases

WHERE name = 'Adventureworks_Snapshot_ID'

Note: The Data Type is not checked and is informational only, it can be left blank if required.

If alternatively the values below were entered then the same query would read

Screen clipping 3

SELECT name

FROM sys.databases

WHERE name = 'MyDB_1'

Once you understand the way the script works then have a play with it. Some of the possible extensions of the script can include

Rename the snapshot if it already exists rather than dropping it

Introduction of formal error handling

Facilitate the capacity to database default the Snapshot ID to the current date and time

Created with Microsoft Office OneNote 2003One place for all your notes

A tool for the rest

Students that attend my courses are used to me going on about formatting SQL carefully for other people to read as well as themselves in a few months time and how the brain is a fantastic pattern matching machine tool which will aid them in their coding and enhance team work and simplify testing of code ... blah blah blah, I am sure some think! You also always get someone highlighting that even if they did do it the way suggested, most of the time they are maintaining other peoples code who didn't come on one of my courses etc. Well now there is a tool for you to sort out other peoples code that came before. Take a look at Online SQL and PL/SQL Formatter it is fantastic! So no more excuses for retaining the status quo at the level found.