I had every intention of doing Replication Part 2 this month but had a thought I liked better. I will get back to replication but let’s do something else this month.

I’m a firm believer that the GUI’s Microsoft has made for us are amazing. Azure Data Tools and SQL Server Management Studio are both awesome tools. That said, this month I have a challenge for you.

Scripting only.

Pretend like you are going to have to use sqlcmd, or the DAC (dedicated admin connection). You have no choice, you have to script rather than using a GUI to do the work for you. That means that if you need to take a backup you write a script. Want to see the list of databases? sys.databases. etc. Virtually anything can be done via scripting, and frequently you can do more, faster by scripting things out. Obviously don’t let this stop you from getting your job done. There are a few things that you are better off using the GUI. (Unless you decide to use PowerShell?) But can you force yourself to script? Spend a few extra minutes looking up that command you need?

I promise you it’s worth it. After some practice I can start a backup or restore many, many, many times faster with a script than I can with the GUI. (I mean assuming I’m not going to be restoring a couple hundred log files. Although to be fair, I can do that pretty quickly too.)

So. Challenge given! Do you accept?

-- Ctrl-Shift-M to update parameters
CREATE DATABASE ScratchPad_<user_abbreviation, char(3), ABC>;
GO
USE ScratchPad_<user_abbreviation, char(3), ABC>;
GO
IF DATABASE_PRINCIPAL_ID('db_execute_all_dbo') IS NULL CREATE ROLE [db_execute_all_dbo] AUTHORIZATION [dbo];
IF DATABASE_PRINCIPAL_ID('<user_name, sysname, domain\abcdef>') IS NULL CREATE USER [<user_name, sysname, domain\abcdef>] FOR LOGIN [<user_name, sysname, US\abcdef>] WITH DEFAULT_SCHEMA =  [dbo];
EXEC sp_addrolemember @rolename = 'db_execute_all_dbo', @membername = '<user_name, sysname, domain\abcdef>';
EXEC sp_addrolemember @rolename = 'db_ddladmin',   @membername = '<user_name, sysname, domain\abcdef>';
EXEC sp_addrolemember @rolename = 'db_datareader', @membername = '<user_name, sysname, domain\abcdef>';
EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = '<user_name, sysname, domain\abcdef>';
GRANT EXECUTE ON SCHEMA::[dbo]  TO [db_execute_all_dbo]  AS [dbo];
GRANT VIEW DEFINITION TO [<user_name, sysname, domain\abcdef>]  AS [dbo];

You’ll notice I have a comment at the top to remind me of the shortcut to fill in the template parameters. It’s not one I use often so I need the help. When I use the shortcut it brings up the two parameters. The green in the background is where the parameters are that are about to be filled in.

Each parameter has a name, a data type (that seems to be for display only), and a default value. If you look back at the code you’ll see that they are set up like this:

<name, data type, default value>

Fill in each of the values. You can use TAB or UP-Arrow | DOWN-Arrow to flip between them. Once you are happy with what you have, and it’s perfectly reasonable to just leave the defaults if you’ve set them up correctly, hit OK to actually make the replacement.

-- Ctrl-Shift-M to update parameters
CREATE DATABASE ScratchPad_ABC;
GO
USE ScratchPad_ABC;
GO
IF DATABASE_PRINCIPAL_ID('db_execute_all_dbo') IS NULL CREATE ROLE [db_execute_all_dbo] AUTHORIZATION [dbo];
IF DATABASE_PRINCIPAL_ID('domain\abcdef') IS NULL CREATE USER [domain\abcdef] FOR LOGIN [domain\abcdef] WITH DEFAULT_SCHEMA =  [dbo];
EXEC sp_addrolemember @rolename = 'db_execute_all_dbo', @membername = 'domain\abcdef';
EXEC sp_addrolemember @rolename = 'db_ddladmin',   @membername = 'domain\abcdef';
EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'domain\abcdef';
EXEC sp_addrolemember @rolename = 'db_datawriter', @membername = 'domain\abcdef';
GRANT EXECUTE ON SCHEMA::[dbo]  TO [db_execute_all_dbo]  AS [dbo];
GRANT VIEW DEFINITION TO [domain\abcdef]  AS [dbo];

At this point I now have a script that I can use over and over again to create this database and apply the permissions. My actual script is quite a bit more complicated and has values for database size, file locations, etc. I don’t need to change them often but with the default values I don’t really have to.

For a long time now SSMS has had the option of scripting for a specific version of SQL Server. This lets you script specifically for 2008 R2, 2012, 2016 etc. Just as an example the script for an extended event changed quite a bit over the first version or two. It’s never huge things, but it does help to script to your target server.

Well in SSMS 16 this was what the option looked like:

Notice how you can select any version of SQL from 2005 to 2016.

But as of v17.9 the option has changed (I’m not sure if it changed somewhere between 17 and 17.9, I haven’t checked).

The important thing to notice here is Match script settings to source. Now rather than having to select the scripting version it automatically selects the version of the server you are on. And of course, if you need to generate a script for a different version you have that option too. Working on an upgrade for example, where you need a script for a higher level of SQL, or perhaps you’re halfway through the upgrade process, and your dev environment is a later version than your production environment so you need to script to a lower version.

In the first part of the script (and by script I mean a .sql file) I have a commented out backup command. A few things you’ll notice here. I have two spaces after the word DATABASE. This is so I can just type (or copy and paste) the database name right in. I also have the end of my file I’m restoring to (_CO.BAK). This is because this is for ad-hoc backups and they will almost always be copy only so I want a CO extension on the end. I just need to type in the path (that I’ll get from the later script) and a date (I like to have a date in the filename) and I’m good to go. Last but not least, there is COMPRESSION, COPY_ONLY and STATS so I don’t forget them. COPY_ONLY so I don’t mess something up, COMPRESSION because not every server I work on has backup compression on by default and STATS because I find it annoying if I can’t tell about how long my backup has to go.

-- BACKUP DATABASE  TO DISK = '_CO.BAK' WITH COMPRESSION, COPY_ONLY, STATS = 10

Next I have a commented out restore command. Pretty similar to the backup command but in this case I also have a sample of the MOVE clause because I end up using it a lot for ad-hoc restores.

/*
RESTORE DATABASE  FROM 
DISK = '_CO.BAK'
WITH MOVE ''    TO '.mdf'
	,MOVE '_log' TO '.ldf'
	,STATS = 10
*/

Note: Both of the commands above are for ad-hoc tasks. Upgrades, refreshes, moves, DR, etc. You should have a regular backup schedule using jobs and you should probably even have a regular restore schedule to test your backups.

This next commented out script is really handy if you are doing big restores/backups. It tells you what backups/restores are running, the percent complete, ETA, elapsed time and ETA in minutes and hours along with the command being run. I don’t think I wrote this but unfortunately I didn’t make a note where I got it from. If you wrote it please tell me so I can add an attribution.

/*
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent Complete],
		CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
		CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
		CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
		CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
		CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
								CASE WHEN r.statement_end_offset = -1 THEN 1000 
								ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
								FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
*/

And last but not least my most common script. Which is why it’s not commented out. This way I just hit CTRL-E and it runs. This script tells me what backups have been taken, how long they took, how big they are and where they go. This is particularly useful if I’m working on an instance the first time and want to know where the backups are going, or if I need to take a backup and want to know an approximate size and how long it will take.

USE msdb
GO
SELECT TOP 100 backupset.database_name, backupset.type, 
		backupset.name, backupmediafamily.physical_device_name, backupset.backup_finish_date,
		backupmediafamily.logical_device_name, backupmediafamily.device_type, 
		CAST(backupset.backup_size/1024/1024 as int) AS Size_in_MB,
		datediff(minute,backupset.backup_start_date,backupset.backup_finish_date) backuptime_minutes, is_copy_only
FROM msdb.dbo.backupset
JOIN msdb.dbo.backupmediafamily
	ON backupset.media_set_id = backupmediafamily.media_set_id
WHERE 1=1
   AND backupset.type = 'D' -- Full (D), Differential (I) or Log (L) 
-- AND physical_device_name NOT LIKE 'VNB%' -- Ignore backups going to tape
-- AND backupset.database_name like '' -- Only backups for a specific DB or group of DBs
ORDER BY backupset.backup_finish_date DESC, backupset.database_name DESC;

Now you may not need all of these pieces. Take some or all and hopefully they will be as useful to use as they have been to me. And BOY are they useful at times.

I was wrong.

I know, shocking right?

I still don’t use it often, but Object Explorer Details has some pretty useful features.

First of all, opening and closing it.

Opening it is as simple as hitting F7 or View->Object Explorer Details.

Closing it is just like closing any other other tab. Just hit the X.

Now, what’s it good for? Well, to start with you can use it to script out multiple items at once. I’ve used this recently to script out everything under a specific schema. The upside is that I was able to filter on just that schema. (Which is another very useful feature by the way.) The downside is that the objects are broken up by type. So you’ll have to go through each of the object types. Generally it’s a good idea to query sys.objects to see what type of objects you have in the schema.

It can also come in handy as a quicky, sortable, report. For example, telling you how large the tables in a database are. By row count as well as disk space.

There are lots of columns available to be added and removed and to the best of my knowledge, all of them are sortable. Right-click on one of the columns and you get the list of possible columns. Obviously, the columns available are different depending on the type of object you are looking at.

And last but not least, most (if not all) of the context-sensitive options available in Object Explorer are available through Object Explorer Details.

So, summary? Object Explorer Details may not be something I use frequently, but it very much has its uses.

First a demo then some discussion.

You can open this window by either hitting F7 or by going to the View >> Object Explorer Details.

Then you can navigate to the objects you are interested in (tables, views, stored procedures etc). Note: As the name implies the Object Explorer Details window is linked to what you have selected in the Object Explorer.

Now select one or more objects. You can select multiple objects by using the shift or control key just like in Windows File Explorer. From there you right click on one of the selected objects and select either delete or script.

There are other methods of doing this of course, which I plan on going into in another post, but for now this has some great benefits. First of all you can filter the list just like in object explorer. So if you need to script just a portion of your 1000+ stored procedures you can filter the list first to make it easier to find them. Then let’s say you want to script all (or most) of a type of object, or filtered list of objects. Just hit control-A to select them all, then unselect anything you don’t want to script by using the control key and the mouse.

Nice little productivity tip hu? Thanks Andrea!

For example when I scripted out a database it started with this:

CREATE DATABASE [Test]
 CONTAINMENT = NONE
 ON  PRIMARY 
 ....

Run that on a 2005 instance, I dare you :). Contained databases weren’t introduced until SQL 2012, so this script won’t run on 2008 or 2008 R2 databases either. So what’s the solution? Change one of the scripting settings!

Now when I run the same script I get this:

CREATE DATABASE [Test] ON  PRIMARY 
....

And there we go. A 2005 version of the same script.

Note: this scripting option is also available in the Generate scripts advanced scripting options.

First here is a script to create a simple partitioned table so you can follow along if you like.

CREATE PARTITION FUNCTION fn_PartTestFunction (int)
AS RANGE LEFT FOR VALUES  (1,2,3,4)
GO

CREATE PARTITION SCHEME sch_PartTestScheme
AS PARTITION [fn_PartTestFunction]
ALL TO ( [PRIMARY] )
GO

CREATE TABLE PartTable (
		PartColumn Int,
		Column1 Int,
		Column2 Int,
CONSTRAINT pk_PartTable PRIMARY KEY (PartColumn, Column1) ON sch_PartTestScheme (PartColumn)
) ON sch_PartTestScheme (PartColumn)
GO

Once the PartTable is created right click on it in the object explorer and script the table out. You could also script out the primary key or clustered index with a similar result.

Here is the script you get.

CREATE TABLE [dbo].[PartTable](
	[PartColumn] [int] NOT NULL,
	[Column1] [int] NOT NULL,
	[Column2] [int] NULL,
 CONSTRAINT [pk_PartTable] PRIMARY KEY CLUSTERED 
(
	[PartColumn] ASC,
	[Column1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON)
)

You will notice that the partition scheme is not mentioned at all. This could be a problem if I am scripting this table out to create it in another location, or to save it in a version store somewhere.

As a side note, during my testing I scripted out the clustered index (or primary key) and noticed the partition scheme was also not mentioned. Interestingly (at least to me) when I dropped the clustered index (or primary key) and then recreated it without mentioning the partition scheme, the table and index both remained partitioned. I guess because I didn’t explicitly mentioned the filegroup location/partitioning option. Now if I ran a script like this:

ALTER TABLE [dbo].[PartTable] ADD  CONSTRAINT [pk_PartTable] PRIMARY KEY CLUSTERED 
(
	[PartColumn] ASC,
	[Column1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
		SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
		ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
		ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]

The partitioning is now removed because I have explicitly mentioned the filegroup location/partitioning option for the table.

So how do I script my partitioned table out and include the partition scheme associated with it?

I use the “Generate Scripts” option. Right click on the database name, go to Tasks, then Generate Scripts.

Then pick “Select specific database objects” and select the table from the tables list.

Proceed through the wizard and you get the following script.

CREATE TABLE [dbo].[PartTable](
	[PartColumn] [int] NOT NULL,
	[Column1] [int] NOT NULL,
	[Column2] [int] NULL,
 CONSTRAINT [pk_PartTable] PRIMARY KEY CLUSTERED 
(
	[PartColumn] ASC,
	[Column1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [sch_PartTestScheme]([PartColumn])
) ON [sch_PartTestScheme]([PartColumn])
GO

Note the partition scheme is now mentioned.

I’m using the 2008 R2 tools, so this may have been fixed (assuming it’s considered a bug) in the 2012 tools. So if anyone is using them and feels like testing it let me know.

Update

greenantim pointed out that I should take a look in the SSMS options. Once there I found “Script partition schemes”. Set this to TRUE and the partition schemes will start showing up when you do a “Script as”.