T-SQL Tuesday #91 – Start Talking

T-SQL Tuesday is a monthly blog gathering for the SQL Server/Data Professional community  It is the brainchild of Adam Machanic (B|T) and is not limited to just things around the SQL Server database engine. Each month a blogger hosts the event and anybody who wants to contribute can write a post about that month’s topic. You can find a list of all topics at http://tsqltuesday.com/.  

This month’s T-SQL Tuesday topic is about DevOps.  It is being hosted by Grant Fritchey (B|T).

Grant asks some specific questions in this month’s posting:

  1. How do we approach DevOps as developers, DBAs, report writers, analysts and database developers?
  2. How do we deal with data persistence, process, source control and all the rest of the tools and mechanisms, and most importantly, culture, that would enable us to get better, higher functioning teams put together?

We’ll discuss each one, but first a recap.

What exactly is DevOps?  Wikipedia tells us that devops is:  “DevOps (a clipped compound of “software DEVelopment” and “information technology OPerationS“) is a term used to refer to a set of practices that emphasize the collaboration and communication of both software developers and information technology (IT) professionals while automating the process of software delivery and infrastructure changes.

Great.  Now we know what it is.  It is intended to be a set of practices that stress on the collaboration and communication of basically everybody (and I mean everybody) that might be involved in application development and delivery.   Ironically, this set of practices has morphed into an actual job title over the years.  My employer just recently hired a “DevOps Engineer”.

So, let’s go back to Grant’s questions.

How do we approach DevOps as developers, DBAs, report writers, analysts and database developers?

Honestly, I think it’s time for us data professionals to get out of the data center and get on board with the DevOps movement.  Let us take off the cloak of invisibility and get our hands dirty.  If we look at the overall “DevOps” role, we should want to have DevOps in our world.  While it may be a hard journey, in the long run it will make our jobs easier.  How would you like to have push button deployments or less manual code reviews?  I know that I do.

How do you do this?  Easy.  Talk.  Communicate.  Collaborate.  Break it down now.  Go talk to your respective teams (app dev, infrastructure, DBA’s, management, etc) and get them talking.  Everybody will have their own opinion and that is okay.  The important part is to get everybody talking.

If you need a starting point, ask this simple question: “What if we could implement a process to deliver application changes to the wild, that could potentially have little to no impact to our customers?”.  Think about that.  Do you work in an environment where deployments cause outages for your customers? I bet for the majority of us, that is true.

If your customers have a better end user experience, meaning little to no down time, isn’t that the name of the game?

DevOps helps to answer that question.

How do we deal with data persistence, process, source control and all the rest of the tools and mechanisms, and most importantly, culture, that would enable us to get better, higher functioning teams put together?

In my opinion, the answer is simple.  Trust.  As data professionals, we often question the process because once you involve data (the data is the business) then we get very distrustful of changes.  This is probably rightfully so.  How many times as a deployment gone sideways for you?  Ever have to roll back a deployment because it barfed?  I know that I have been there and usually it’s not that much fun.  Hopefully every DBA has a recovery strategy in place to handle such events.

In order to reach a true “DevOps” method to deliver application changes into the wild, we have to get our hands off of it. And I mean OFF.  This means tools must be in place in order to facilitate this. This also means that we have to TRUST the tools to do their job and do it well.   Tools such as Octopus Deploy, Team City, Jenkins, TFS, Red Gate, etc.  All of these third-party vendors pour money, time and effort into making them as rock solid as possible.

Trusting the tools is difficult for most database administrators.  We want to see the guts in deployment, making sure dangerous things do not happen to our precious databases and their contents.

Experience

I was a part of a database continuous delivery project at Farm Credit Services of America in conjunction with Alex Yates (B|T) and Bob Walker (B|T), who have both blogged about the experience.  I have also been pushing a similar project at Farm Credit Mid-America.

I’ll admit when the project started up at FCSA, I was that distrusting, skeptical DBA that thought this would never work.   Yes, it was hard.  It was cumbersome and clunky in the beginning.  Then it got easier and the pieces fell into place.  Iterations to delivery changes to the wild got easier.  Quickly, I began to trust the process and how it was going to work.

I highly recommend Bob’s series of blog posts about the process.  You can find them here. Alex also has several excellent posts around the process here.

Summary

In short, I’m a large support of the DevOps movement especially concerning database lifecycle management (DLM) and continuous delivery.  While difficult, there are organizations out there that can help you get there.  I assure you that the end results will be worth every penny.

Time to shed the cloaks of invisibility.  Start talking.

 

Posted in General Conversations | Tagged | 3 Comments

I’m Speaking! Capital Area SSUG

I like it when I get referred to speak at various functions. Whether it’s a user group, conference or just something informal, I really enjoy speaking (yes, I’m crazy) and giving back to the community.

This time I’ll be speaking for the Capital Area SQL Server User group out of Albany, New York.  This will be a remote session and I’ll be speaking on Continuous Delivery & Your Salvation.

Continuous Delivery & Your Salvation

DSC_0778Continuous Integration & Delivery isn’t a new concept. Application Developers have been doing it for quite a while and now it’s time for Database Professionals to catch up. Whether you are a database administrator, database developer, or even an application developer, database continuous delivery can help you find your salvation. I’ll show you how to apply these concepts to areas of security, collaboration, testing, support and deployments. We will look at what continuous delivery means, demonstrate some tools that can help you easily get it into place, and dive into why it’s important.

If you are in the Albany, New York area tonight from 5:30PM to 7:30PM(ish) please stop by!!  You can register for the event here.   If you do decide to go, please make sure to RSVP so that the organizers have an accurate head count for food.

Speaking of food, the only sad thing about this, is the group is having BBQ this evening. Why is that sad? Because I love good BBQ, I’m missing the food!!

See you virtually there!

 

Posted in Presenting | Tagged | Leave a comment

Reverse It

Recently I had a conversation with a colleague that wasn’t aware of the REVERSE function in SQL Server.  I had sent them a code snippet and they inquired as to what the purpose of the function was.  Essentially, this function that reverses any string value.

For example, the phrase:

The brown dog jumped over the lazy fox

reversed looks like this

xof yzal eht revo depmuj god nworb ehT

Or in Management Studio

Awesome.  Where can I use this?  I use this function when I need to get file names for data or log files of a database.  These are the steps to do this:

  1. Reverse the full path of the file name
  2. Use the LEFT function to return all of the characters left of the first instance of “\”
  3. Reverse the string back to normal orientation
SELECT  name AS 'Database Name' ,
        REVERSE(physical_name) 'Path Reversed' ,
        REVERSE(LEFT(REVERSE(physical_name),
                     CHARINDEX('\', REVERSE(physical_name)) - 1)) AS 'File Name'
FROM    sys.master_files;

In Management Studio,

Voilá!!  We have the file names.  This is also helpful whenever you need to find the tail end of a string that has some type of delimiter.

Enjoy!

Posted in Administration | Tagged | Leave a comment

Ola Hallengren: Output File

I’m a huge fan of Ola Hallengren‘s free utility scripts. If you aren’t familiar with it, it’s a great tool to manage backups, index maintenance and the overall health of your SQL Servers. It is utilized around the globe and it is completely free.

Although his script is very thorough I still like to tweak and One of the things that I tend to change is the way that the log file for each respective job is named.  By default, the file name is configured in the following format:

JobName_JobID_StepID_YYYYMMDD_HHMMSS.txt

which results in file names that look like this:

If you have never looked, these files contain information about any failures or issues the job(s) might have encountered.  The difficult part is that when looking at the list, how do you know which file belongs to which job?  In the example above, one is a FULL backup, a DIFF backup and the third a TRAN LOG backup but it is difficult to determine which is which.  However, you can see that each JobID is a unique identifier, which correlates to the job ID of the specific job.

There are two ways to adjust this.

  1.  Update the maintenance script itself to adjust the file names.  In the script, configuration of job logging starts approximately on line 4127.  Your mileage may vary depending on which version you have.  You can look for the phrase “Log completing information” in the file to determine where it starts.  Once you find its location, you can adjust the file name to however you want.
  2. If you don’t want to adjust the script,  you can use the script below to manually adjust the output file name.  This script will produce the appropriate parameters for sp_update_jobstep.  You can then copy/paste the command into a query windows and execute in a controlled manner.
/***************************************************************
-- Author: John Morehouse
-- Date: May 2017
-- http://sqlrus.com

--THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.

--IN OTHER WORDS: USE AT YOUR OWN RISK.

--AUTHOR ASSUMES ZERO LIABILITY OR RESPONSIBILITY.

--You may alter this code for your own purposes.
--You may republish altered code as long as you give due credit.
***************************************************************/

-- NOTE: You will need to turn OFF SQLCMD mode if it is enable for this to work

;WITH myCTE AS (
    SELECT 'IndexOptimize - USER_DATABASES' AS 'Name', 'UserDBs' AS 'FilePart' UNION
    SELECT 'DatabaseBackup - USER_DATABASES - LOG', 'UserDBs_LOG' UNION
    SELECT 'DatabaseBackup - USER_DATABASES - FULL', 'UserDBs_FULL' UNION
    SELECT 'DatabaseBackup - USER_DATABASES - DIFF', 'UserDBs_DIFF' UNION
    SELECT 'DatabaseIntegrityCheck - SYSTEM_DATABASES', 'SystemDBs' UNION
    SELECT 'DatabaseBackup - SYSTEM_DATABASES - FULL', 'SystemDBs_FULL' UNION
    SELECT 'DatabaseIntegrityCheck - USER_DATABASES', 'UserDBs' 
)
SELECT output_file_name, REPLACE(output_file_name,'$(ESCAPE_SQUOTE(JOBID))', m.FilePart) AS 'Updated_Output_File_Name'
, 'EXEC sp_update_jobstep @job_id =''' + CAST(sjs.job_id AS VARCHAR(36)) + ''', @step_id= ' + CAST(sjs.step_id AS VARCHAR(10)) + ', @output_file_name = ''' + REPLACE(output_file_name,'$(ESCAPE_SQUOTE(JOBID))', m.FilePart) + ''''
  ,* FROM dbo.sysjobsteps sjs
	INNER JOIN myCTE m ON sjs.step_name = m.name 

Once adjusted, the log files are will be much easier to read, as shown below.  I can now quickly differentiate the log files not only between databases but also types.

Enjoy!

 

 

Posted in Administration | Tagged | Leave a comment

Experienced DBA: Rookie Mistake

M.A.S.H 4077.  Mobile Army Surgical Hospital.  One of my all time favorite TV shows that I still watch to this day.  There is one episode that has always stuck with me is one where Hawkeye has to deal with death of a friend during surgery. Afterwards, Colonel Blake imparts advice to Hawkeye:

While not life threating, I was recently reminded that:

  1.  I’m human and I make mistakes at times.
  2.  Even the most experienced DBA cannot change rule #1.

The other day I had to update some records, in Production.  I’m a firm believer of using explicit transactions and double checking things before committing a transaction.  This helps ensure things go as expected.  This also allows me a way to rollback the changes if they don’t.  It happens.

However, this means that I have to COMMIT said explicit transaction.  And not go to lunch without doing so.

Can you see my mistake?  I bet you can.

I changed a single record in a table, *thought* that I had committed it and went about my day.  I even left the office for an appointment over lunch.  While I was at my appointment, I got a call from a teammate asking if I had being doing anything with this particular database around 11:17 that morning.

Why, yes, yes I was as a matter of fact.

As soon as I saw his number on my phone, I realized my mistake.  An open transaction was left sitting there.  I quickly estimated in my head how much blocking that was generating.  Given this particular database, it wasn’t going to be pretty.

I told my teammate to kill my transaction which would roll back any changes.  I can fix the data again once I got back to the office.  The transaction was killed off and the blocking disappeared.  Problem solved.

I made a mistake.  A rookie mistake at that.

Resolution

I’m a fan of Red Gate tools.  I use SQL Prompt on a daily basis so I created a new snippet to help me remember to handle any transaction and/or check for open transactions.

Now I can use the snippet to automatically insert the syntax for the explicit transaction as well as a check (or reminder) to see what the transaction count is.

Reminder, always, always, check for open transactions before leaving for lunch!!

Learn from my rookie mistake.

Enjoy!

Posted in Administration | Tagged | 7 Comments

Knowing the Options

Have you every executed a query in SQL Server Management Studio, looked at the execution plan, and noticed that it was a different plan than what was generated on the server?

A potential reason for this could be a different option settings.  The options represent the SET values of the current session.  SET options can affect how the query is execute thus having a different execution plan.   You can find these options in two places within SSMS under Tools -> Options -> Query Execution -> SQL Server -> Advanced.

As well as Tools -> Options -> Query Execution -> SQL Server -> ANSI

@@Options

Using the interface to check what is set can get tiresome.  Instead, you can use the system function @@OPTIONS.  Each option shown above has a BIT value for all 15 options indicating whether or not it is enabled.

It would look like this: 001010101111000.  Each bit corresponds to an option in order from the table below:

Options
Value Configuration
1 DISABLE_DEF_CNST_CHK
2 IMPLICIT_TRANSACTIONS
4 CURSOR_CLOSE_ON_COMMIT
8 ANSI_WARNINGS
16 ANSI_PADDING
32 ANSI_NULLS
64 ARITHABORT
128 ARITHIGNORE
256 QUOTED_IDENTIFIER
512 NOCOUNT
1024 ANSI_NULL_DFLT_ON
2048 ANSI_NULL_DFLT_OFF
4096 CONCAT_NULL_YIELDS_NULL
8192 NUMERIC_ROUNDABORT
16384 XACT_ABORT

For example, the right most three bits are 0. These correspond to:

  • DISABLE_DEF_CNST_CHK
  • IMPLICIT_TRANSACTIONS
  • CURSOR_CLOSE_ON_COMMIT

So what does @@options really return?

Running @@OPTIONS on my machine it returns a value of 5496.  Useful right? Ok, not really.  It would be nice to know exactly which options are set and which aren’t without having to go through the UI.

@@OPTIONS takes the binary representation and does a BITWISE operation on it to produce an integer value based on the sum of which BITS are enabled.

Let’s assume for a moment that the only two options that are enabled on my machine are ANSI_PADDING and ANSI_WARNINGS.  The values for these two options are 8 and 16, respectively speaking.  The sum of the two is 24.

You have Options

Thankfully, there is an easier way.  The script below will do the work for you and display what options are enabled.

/***************************************************************
  Author: John Morehouse
  Summary: This script display what SET options are enabled for the current session. 
 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
***************************************************************/
SELECT 'Disable_Def_Cnst_Chk' AS 'Option', CASE @@options & 1 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'IMPLICIT_TRANSACTIONS' AS 'Option', CASE @@options & 2 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'CURSOR_CLOSE_ON_COMMIT' AS 'Option', CASE @@options & 4 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_WARNINGS' AS 'Option', CASE @@options & 8 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_PADDING' AS 'Option', CASE @@options & 16 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_NULLS' AS 'Option', CASE @@options & 32 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ARITHABORT' AS 'Option', CASE @@options & 64 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ARITHIGNORE' AS 'Option', CASE @@options & 128 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'QUOTED_IDENTIFIER' AS 'Option', CASE @@options & 256 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'NOCOUNT' AS 'Option', CASE @@options & 512 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_NULL_DFLT_ON' AS 'Option', CASE @@options & 1024 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_NULL_DFLT_OFF' AS 'Option', CASE @@options & 2048 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'CONCAT_NULL_YIELDS_NULL' AS 'Option', CASE @@options & 4096 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'NUMERIC_ROUNDABORT' AS 'Option', CASE @@options & 8192 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'XACT_ABORT' AS 'Option', CASE @@options & 16384 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled'

The output of the script will give you this:

 

 

 

 

 

 

 

 

 

Next time you are investigating an execution plan, remember to check to see your options are.

Enjoy!

Posted in Administration | Tagged | 2 Comments

T-SQL Tuesday #90 – The Elephant’s Name is Database

T-SQL Tuesday is a monthly blog gathering for the SQL Server/Data Professional community  It is the brainchild of Adam Machanic (B|T) and is not limited to just things around the SQL Server database engine. Each month a blogger hosts the event and anybody who wants to contribute can write a post about that month’s topic. You can find a list of all topics at http://tsqltuesday.com/.  

This month’s T-SQL Tuesday topic is about continuous database deployments and Database Lifecycle Management (DLM).  It is being hosted by James Anderson (B|T).

The Elephant

In my experience, the database is the final thing to be thought of when it comes to continuous deployment. Nobody wants to tackle it and it can be hard to get right.

It is the elephant in the room.

I’m sure that we all have been a part of a deployment where the database wasn’t deployed successfully.  If it was a large deployment meaning lots of changes (both schema and data), fixing things on the fly probably (and did) suck.

Thankfully, available tools have come a long way regarding databases and how they are being deployed.  These tools, such as Red Gate, Octopus Deploy, Team City (just to name a few) have made amazing strides in helping organizations implement database lifecycle management into their environments.

I’m a Fan

While realizing that continuous database deployments can be difficult, I’m a huge fan of them.  I’ve been pushing my current employer to get them implemented for some time.

In a nutshell, there are several benefits from having continuous database deployments:

  1. Source Control.  Source Control.  Do I need to repeat this again?  The database IS code. It belongs in a source control system of your choice.  Yes, it can be difficult.  Do it anyway.  You’ll thank me later.  Note: Backups are NOT a form of source control.
  2. Make It Suck Less (MISL).  I’m borrowing this phrase from a former co-worker (thanks Matt Meyer!) and it’s one that I use often.  Database continuous delivery makes my life easier as a DBA.  If I have the appropriate tools and processes in place, deployments become a point and click event.  Heck, you can even SCHEDULE it.  See #4.
  3. Process consistency.  I’m a DBA that supports multiple application development teams.  Each team wants to deploy database changes differently.  Some use DACPAC’s others use scripts.  This causes a bottleneck issue simply because there is (currently) one of me and 40 developers.  By having DLM in place, EVERYBODY follows the same path to get things into Production.  As a side note, I’m also a believer that the DBA’s should follow the same path, even for their own administrative databases they control.  Just because you might have sysadmin rights to production doesn’t mean you get to go outside the process in a non-emergency situation.
  4. Trusting the tools.  I need to trust the tools to ensure that things go smoothly.  This is hard for DBA’s I think.  We want to see what is changing.  We want to touch it, examine it, play with it, throw it against the wall and see what sticks.  We become the bottleneck for Agile environments.  If we trust the tools that are in place, we remove the bottleneck from the process and we help our development teams to become even more Agile.
  5. Security.  I’m a stickler for permissions and maintaining security in all environments.  Continuous database delivery helps me to lock things down.  If the process is implemented and the tools are trusted, then developers don’t need modification permissions to anything in any environment.  Sure, give them read permissions, but the ability to change things outside of source control should be prevented.  If you don’t lock things down to the process itself, mass chaos can erupt.

All of these helps to address the elephant in the room.

The Switch

Ok, I’ll admit that I wasn’t always sold on database continuous delivery.  I was that skeptical database administrator that thought it was all hokum.  That all changed.

Several years ago I was a part of a project at Farm Credit Services of America to ease deployment pain.  At the time, we were deploying things daily  and the database was definitely a bottleneck.  It was all very manual.

Then I got to meet Alex Yates (B|T) and Sean Newham (T) both from Red Gate at the time.  We were able to construct an agreement with Red Gate to get them onsite (all the way from the UK!) to help us architect a continuous delivery process.

After spending 3 days in a room with Alex, Sean and a team of developers, I was completely sold.  Alex and Sean clearly and efficiently explained all of the parts of the process and how things should work.  It was VERY clear to me that database continuous delivery would deliver all the points I’ve listed above.  Simply put, it would make my job EASIER!!!

Easier.  Let that sink in for a moment.  Why wouldn’t you want a process that makes things suck less?

[Plug] Alex now runs DLM Consultants and I can’t recommend him enough.  If you want to implement database lifecycle management in your organization but don’t know where to start, he can definitely help you out.

Summary

I’m a die-hard operational DBA.  I love the internals of SQL Server.  That being said, I am completely sold on the value that database continuous delivery brings to the table.  By implementing it correctly, you can secure your environment, make things suck less, trust the process and remove the database as a potential bottleneck.  If you do not have it in place currently, I would urge you to at least take a look at it.

It’s time we as DBA’s kicked the elephant out of the room.

 

 

Posted in TSQL Tuesday | Tagged , , | Leave a comment

I’m Speaking! SQL Saturday #617 – Pensacola

In early June, I’ll be traveling to Pensacola, Florida for their annual SQL Saturday. This will be my second time attending their event.  Last year was fantastic and I am looking forward to returning.

The sun, the beach, and of course SQL Server training! How can you beat a weekend in sunny Florida AND have free training as well? The answer is, you can’t!

Here is the session that I’ll be presenting in room 2171 @ 8:00AM, which is one of my favorites:

SQL Server Databaseology: A Deep Dive Into Database Internals

NullPlacementMatters_2Have you ever taken apart a toaster or an alarm clock just to see how it worked? Ever wondered how that database actually functions at the record level, behind the scenes? SQL Server Databaseology is the study of SQL Server databases and their structures down to the very core of the records themselves. In this session, we will explore some of the deep inner workings of a SQL Server database at the record and page level.  You will walk away with a better understanding of how SQL Server stores data and that knowledge will allow you to build better, faster databases.

Honestly, is it every too early to learn about internals?  I think not! Bring your coffee and let’s dive in!

How Can You Beat FREE Training?

Remember that these events are a FREE day of awesome SQL Server training and professional development.  The only cost to you is if you decide to purchase lunch! Check out the schedule and register.  In today’s economy, how can you afford to pass up FREE training?!?!  The answer is that you can’t.  Go register, get out there and learn.  I triple dog dare you.

If you make it out to see my session, make sure to come up and introduce yourself as I’d love to meet you!

See you at the beach!!

Posted in Community, Presenting | Tagged , , , | Leave a comment

SSIS – Adding Connections

Sometimes you get reminded that you don’t know everything about SQL Server and it’s various products Every time I run into something like this I am going to write a quick blog about it.  It’s something new I’m trying

While recently examining an existing SSIS package  before implementing some needed changes I noticed the connection manager for multiple flat files.

Normally, when processing multiple files, I use a sequence container and loop through them.  It’s usually a cumbersome process to setup and you must configure variables.  I was not aware the multiple flat file type of connection manager existed. No wonder because it’s notwithin the normal list.  This handy thing could only be found when I looked under “New Connection”.

Right click in the blank space in the Connection Manager and select “New Connection” from the resulting dialog window.

Suddenly a whole new list of connections is now available!

Just a reminder that it benefits, at times, to click around to discover new things!

Enjoy!

Posted in SSIS | Tagged | 1 Comment

Quick Script: Finding Specific Columns

I like scripts.  Scripts that help me do things.

Working in the financial industry, there are always things that you have to ensure are protected.  More specifically, social security number and date of birth.

If your environment is like most, there are legacy applications that have a database behind the scenes that when developed 20 years ago didn’t take this into account.  Yes, these types of systems still exist.  I promise you that.

Anyway, I wrote this quick script to interrogate each database on the server to look for a specific column name pattern.  Keep in mind, you might need to adjust the data types.  In the script below, I was looking for social security numbers and they are usually stored as a string.  This is due to the fact that a social security number can start with a zero and SQL server will happily truncate that off if it is stored as a numeric value.

NOTE:  I’ve run this on production systems before and have zero issues.  However, your mileage may vary so run it at your own risk.  Do not run code you find on the internet on production systems without testing it first.  

/***************************************************************
  Author: John Morehouse
  Summary: interrogate each database looking for a specific column name
 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
***************************************************************/
DECLARE @dbs AS TABLE ( name sysname, processed BIT)
DECLARE @x INT = 1
DECLARE @sql VARCHAR (2000)
DECLARE @dbName VARCHAR (50)

IF object_id ('tempdb..#results') IS NOT NULL
BEGIN
DROP TABLE #results
END

CREATE TABLE #results ( dbName sysname
, tableName VARCHAR (100)
, columnName VARCHAR (100)
, DataType VARCHAR (100)
, MaxDataLength INT
, theRowCount INT )

INSERT INTO @dbs ( name, processed )
SELECT name, 0 FROM sys.databases
WHERE database_id > 6
AND [state] = 0 --online

WHILE @x <= (SELECT COUNT( 1) FROM @dbs WHERE processed = 0 )
BEGIN
SELECT TOP 1 @dbName = name FROM @dbs WHERE processed = 0

SET @sql =
'INSERT #results (dbName, tableName, columnName, DataType, MaxDataLength, theRowCount)
SELECT ''' + @dbName + ''',t.name,c.name,st.name, c.max_length,p.rows
FROM [' + @dbName + '].sys.columns c
INNER JOIN [' + @dbName + '].sys.tables t ON c.object_id = t.object_id
INNER JOIN [' + @dbName + '].sys.systypes st ON c.user_type_ID = st.xusertype
INNER JOIN [' + @dbName + '].sys.partitions p ON t.object_id = p.object_ID
INNER JOIN [' + @dbName + '].sys.allocation_units au ON au.container_ID =
CASE
WHEN au.TYPE IN (1,3) THEN p.hobt_id
WHEN au.type IN (2) THEN p.partition_id
END
WHERE (c.name LIKE ''TIN%''
OR c.name LIKE ''TIN_TX%''
OR c.name LIKE ''%SSN%'') -- looking for specific column name
AND c.OBJECT_ID > 100 -- exluded system objects
AND st.name IN (''varchar'', ''nvarchar'') -- due to leading zeros, should be n/varchar data type
AND p.index_id IN (0,1) -- Heap or Clustered Index
AND au.type = 1 --IN_ROW_DATA only'

--PRINT @sql
BEGIN TRY
EXEC (@sql)
END TRY
BEGIN CATCH
SELECT ERROR_LINE () AS 'Error Line'
, ERROR_MESSAGE () AS 'Error Message'
, ERROR_NUMBER () AS 'Error Number'
, @dbName AS 'Database'
END CATCH

UPDATE @dbs
SET processed = 1
WHERE name = @dbName
END

SELECT * FROM #results
GO

Enjoy!

Posted in T-SQL | Tagged | Leave a comment