SQL/ Syndicated/ Tips and Tricks

Cheating on SQL… with Excel

, , , , 2 Comments

Viva Las Vegas and Las Laziness

In my presentation on Performance-Enhancing Laziness I’ll be presenting at Dev Connections this Fall in Las Vegas I’ll be providing a great number of productivity tips that will allow you to work a lot less – to get many more things accomplished in your day.  I wanted to give you a teaser that shows some of the things I’ll be discussing.

The Scenario

This tip in particular is key when you have to execute a similar command against a large number of items.  I’ve recently used it when coding Change Data Capture setup commands against 100 tables and in granting rights to a number of stored procedures to a single AD Group.  In order to demonstrate on a small scale let’s say we need to grant SELECT rights to a number of tables in AdventureWorks (for the sake of readability we’ll only grab ten at random). Our customer wants the database role named foo to have SELECT rights to these tables.

SELECT TOP 10 '[' + OBJECT_SCHEMA_NAME(object_id) + '].[' + name + ']'
FROM sys.[objects]
WHERE [type] = 'U';

 

 

 

 

 

 

 

This is the set of tables our fictional customer identified they need to have us grant SELECT rights for our custom database role identified as foo. We could easily do the cut and paste dance here. In the case of only 10 iterations of the code it’s not that big of an ordeal. But what if we were discussing fifty or hundreds of objects? What if we’re dealing with more complex queries? What I’m about to show you is a great time-saver. In this example we are in need of granting SELECT rights to a role for ten tables. The command syntax to do this for a single table looks like this:

GRANT SELECT ON [schema].[object] TO [foo];

Now that we know what our scope is (the ten tables) and what the syntax is, we can proceed with a simple Excel hack that will give us all we need without the repetitive copy & paste work.

Laziness in Action

Alright DBAs! OPEN UP… EXCEL!  It’s alright, SQL Server won’t mind.  SQL wants to make sure you’re happy and if that means straying off to visit the more conservative Excel for a bit of Naughty Accountant play then it’s okay.  You and SQL have an open relationship…  Not as open as MySQL, but you don’t know where MySQL has been!
Create a new workbook and copy in the listing of objects you’re going to apply the repetitive task to. It could be the output of the initial query identifying our tables we’ll be using in our example here but I’ve also recently had this be a list of tables supplied by our Data Warehouse Team to target for Change Data Capture. Point is, you need a column listing the only identifier that will be changing in these code statements. You’ll see I pasted them into cell A4. This is because I want to drop in the unadulterated T/SQL command into B1. I then want to split up the command in cells B2 and B3 as follows:

  • B2: The command text that precedes the information in column A (schema.table name in our example.)
  • B3: The command text that follows the information in column A (schema.table name in our example.)

Your workbook should look something like this:

 

At this point all that is left is to build the simple formula that concatenates the BEFORE, object name, and AFTER command text blocks together.  The formula is displayed in the screen shot below:

…and then to copy the values down for each item.

Re-Use is the Key

The great thing about this workbook (and workaround) is that you only need to create it once and can keep it handy for new repetitive work. All you will need to do is change cells B1-B3 and the values for column A. The formula remains unchanged. Then it’s simply a matter of copying the results in cells B4 on down and pasting them into a query window in SQL Server Management Studio. The paste action from Excel to SSMS pastes the values, not the formulas:

GRANT SELECT ON [Production].[ProductProductPhoto] TO [foo];
GRANT SELECT ON [Sales].[StoreContact] TO [foo];
GRANT SELECT ON [Person].[Address] TO [foo];
GRANT SELECT ON [Production].[ProductReview] TO [foo];
GRANT SELECT ON [Production].[TransactionHistory] TO [foo];
GRANT SELECT ON [Person].[AddressType] TO [foo];
GRANT SELECT ON [Production].[ProductSubcategory] TO [foo];
GRANT SELECT ON [dbo].[AWBuildVersion] TO [foo];
GRANT SELECT ON [Production].[TransactionHistoryArchive] TO [foo];
GRANT SELECT ON [Purchasing].[ProductVendor] TO [foo];

Parse, execute and you’re done.  You stepped out on SSMS but brought home all the tricks you learned in Excel and SSMS barely broke a sweat as a result.

Do you want to get more SQL Server training with a side of fun, networking, and rejuvenation? Then join me in 2013 for SQL Cruise 2013!