Templates are a powerful tool in so many trades and crafts. From decals and stickers all the way up to the largest cruise ships in the world, templates can be found everywhere in just about everything that do.
In SQL Server, templates are readily available for your use in so many different ways that I am sure we are unaware of most of them.
Templates
A good example of a template in SQL Server, that we clamor about frequently, is the unattended install. I am sure you have probably used that kind of template as you work to save yourself some hair and brain cells trying to remember the exact setup needed for your environment. With an unattended install, you build it once and then use the script to build all future installs.
Even though we may not call the unattended install script a template, it is. Think about the definition(s) of a template for a minute:
- something that serves as a model for others to copy.
- a preset format for a document or file, used so that the format does not have to be recreated each time it is used.
Looking at this definition, we can easily see that the unattended install routine fits perfectly as a “template”. This is just an example of the availability of templates within SQL Server. There are many different kinds of templates – many we probably don’t ever think about as being “templates” and many that we just flat out don’t bother to think about at all.
I have one specific kind of template in mind that I want to introduce and discuss. This template type comes with Extended Events. I actually introduced the concept very briefly in a recent post about the XE Profiler feature. That article is just one of many articles about Extended Events that I have written. You are welcome to catch up on the series of articles here.
XE
Extended Events has had templates available for quite some time. If you recall from the article about XE Profiler, templates were not introduced as a part of the XE Profiler feature – the templates were introduced quite some time prior to that.
There are really three distinct areas of focus for templates that I would like to discuss. I have them somewhat illustrated in this next screenshot.
In this image, you will see that I have three color coded regions. In green, I have the option to create a template from file. In blue, I have the XE specific templates that have no correlation to profiler. And then in red I have the templates that were created to simulate the behavior of the Profiler based templates while providing some sort of comfort zone for the profiler fanatics out there.
Today, I will focus on the templates that are readily available (red and blue zones). In a future article, I will cover the creation of a custom template (green zone). That said, this is not going to be a traditional article with the technical geekery showing the ins and outs of all of those templates. Oh no, this article will be an entirely different flavor of geekery. Today, I will be focusing on something that is more about fun. I will be exploring the available templates strictly via script (no XE GUI).
Exploratory Surgery
If you are like me, you sometimes just don’t care to pick up the mouse to browse the file system or even browse the SSMS hierarchy to find the exact screen that has some specific information that you want. For me, I ran into this when thinking about the templates in XE. What templates are there in XE again? Crap, now I have to open the GUI to figure that out. I wanted a quick way to be able to look at all of the templates – and not just the templates available to me in the version of SSMS that I am using, I want to see everything out there on the system.
The question is how to do this. The templates are not listed in any system table or view that I could find. The list of templates is specific to the version of SSMS that you are using and the templates are all xml files stored in a directory on the OS. A thought occurred to me while contemplating this dilemma – can I maybe use the registry?
The registry does give me some options so maybe, I can use xp_regread. I won’t be able to use xp_instance_regread since this is not a registry key that appears at the instance level, rather it is an SSMS related reg string. One problem that comes to mind is that I would need to find the SQLPath for each of the versions of SSMS installed (red arrows indicate each of the registry strings I might have to query). I start to think to myself after realizing this that I do not like the looks of this option – but it could be done.
I then thought of another option before going too far down the registry route. When I install an application, that application may modify my “Path” environment variable for the OS. This sounds a little promising. I still have some of the drawback with multiple versions of SSMS installed – but I think I can manage that more easily from the “Path” environment variable.
Let’s take a crack at a script.
DECLARE @SourceDir varchar(1024)
, @SQL VARCHAR(4096)
, @Paths VARCHAR(MAX)
, @i AS INT
, @SSMSVersion INT;
IF OBJECT_ID('TempDB..#DirTree', 'U') IS NOT NULL
BEGIN
DROP TABLE #DirTree;
END
IF OBJECT_ID('TempDB..#DirList', 'U') IS NOT NULL
BEGIN
DROP TABLE #DirList;
END
IF OBJECT_ID('TempDB..#Data', 'U') IS NOT NULL
BEGIN
DROP TABLE #Data;
END
IF OBJECT_ID('TempDB..#Number', 'U') IS NOT NULL
BEGIN
DROP TABLE #Number;
END
CREATE TABLE #Number(Number INT);
CREATE TABLE #Data(pathvar VARCHAR(MAX));
CREATE TABLE #DirTree
(
RowNum INT IDENTITY(1, 1)
, Name VARCHAR(256)
, FilePath VARCHAR(1024)
, Depth BIT
, SSMSVersion INT
, IsFile BIT
, FileDate DATE
);
CREATE TABLE #DirList
(
FName VARCHAR(1000)
, FileDate DATE
);
INSERT INTO #Data
EXEC xp_cmdshell 'path';
SET @Paths = ';';
SELECT@Paths = @Paths + REPLACE(pathvar, 'Path=', '') + ';'
FROM#Data
WHERE pathvar IS NOT NULL;
SET @i = 1;
WHILE @i < LEN(@Paths)
BEGIN
INSERT INTO #Number
SELECT@i;
SET @i = @i + 1;
END;
DECLARE cpath CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT--Paths [SQL Server Paths]
REPLACE(paths,'BINN\ManagementStudio','Templates\sql\xevent') [SQL Server Paths]
,RIGHT(REPLACE(paths,'\Tools\BINN\ManagementStudio\',''),3) AS SSMSVersion
FROM(SELECTSUBSTRING(
@Paths , Number, CHARINDEX(';', @Paths, Number) - Number) Paths
FROM#Number
WHERE SUBSTRING(@Paths, Number - 1, 1) = ';' ) AS Data
WHERE Paths LIKE '%SQL%'
AND RIGHT(Paths, 17) = 'ManagementStudio\'
UNION
SELECT--Paths [SQL Server Paths]
REPLACE(paths,'BINN\ManagementStudio','Templates\sql\dbscopedxevent') [SQL Server Paths]
,RIGHT(REPLACE(paths,'\Tools\BINN\ManagementStudio\',''),3) AS SSMSVersion
FROM(SELECTSUBSTRING(
@Paths , Number, CHARINDEX(';', @Paths, Number) - Number) Paths
FROM#Number
WHERE SUBSTRING(@Paths, Number - 1, 1) = ';' ) AS Data
WHERE Paths LIKE '%SQL%'
AND RIGHT(Paths, 17) = 'ManagementStudio\';
OPEN cpath;
FETCH NEXT FROM cpath INTO @SourceDir, @SSMSVersion;
WHILE @@FETCH_STATUS <> -1
BEGIN
INSERT INTO #DirTree
( Name
, Depth
, IsFile
)
EXEC master.dbo.xp_dirtree @SourceDir, 1, 1;
UPDATE dt
SET Filepath = @SourceDir + Name
,dt.SSMSVersion = @SSMSVersion
FROM #DirTree dt
WHERE FilePath IS NULL;
FETCH NEXT FROM cpath INTO @SourceDir,@SSMSVersion;
END;
CLOSE cpath;
DEALLOCATE cpath;
SELECT dt.SSMSVersion, Name AS XETemplate
, CASE WHEN filepath LIKE '%dbsco%' THEN 'AzureDB' ELSE 'SQLServer' END AS XEScope
, FilePath
FROM #DirTree dt
ORDER BY RowNum;Ouch, my eyes hurt already! What have I done here? I have written multiple loops. Never fear! Loops are not all evil. Sometimes, it makes sense to use a looping mechanism. This is one of those times where it makes some sense. This script is a limited use script for the eyes of the DBA only.
I can hear the moans about the use of xp_cmdshell and xp_dirtree and so forth. I am not listening to those grumbles. Again, this script is for fun and on my machine. The discussion on securing the server and xp_cmdshell is a discussion for another time.
In this script, I use the path variable to determine all of the flavors of SSMS that have been installed. Since the path for SSMS is all the way to the BINN folder, I need to do a little replace to get the correct paths for the xe templates. I also added a bit of a union in there because I had to duplicate the results – sorta. You see there are templates for AzureDB that, depending on your Azure Subscription or lack thereof, you may never see. I wanted to include those in my result set!
Looking at these results on a machine with SSMS 14 and SSMS 17, I would see something like the following.
I highlighted a few different sections of the results to help quickly show some of the templates available between different SSMS versions as well as those for Azure DB versus a local instance of SQL Server. Things are a tad different if we have SSMS 16 installed. Here is a sample result from a machine with SSMS 16 installed.
The main point of interest here (at least for me) is the removal of a template from SSMS 16 to SSMS 17. This is probably a template you don’t necessarily want to run on a production system and that is likely why it is gone from the available templates that get installed.
The Wrap
I am sure you can probably find an alternate routine to query these templates via TSQL that may be more efficient. I would be interested to hear about it.
What’s next now that I can get these templates? Well, a thought occurred to me to use this type of routine to create a means to use TSQL to read one of those template files to create the XE session from pure TSQL statements and eliminate the need to use the GUI. I think that would be a lot of TSQL/XE Geekery based fun.
