Recent Articles:

From the Vault: Detangling Nested Views

June 2, 2014 Uncategorized No Comments

I’ll be in France and largely AFK for a few weeks this summer, so I’m posting some golden oldies. You can find the original article and comments here

I hate nested views. I hate them, I hate them, I hate them. This is the Nth shop I have been in that makes use of one view that pulls FROM and JOINs other views, which in turn pull from other views, and it’s foot-stompingly frustrating to work with.

I’ve talked before about how inappropriate views hurt performance, and I will talk in the future specifically about how nested views hurt performance, but today I just want to give you the start of a solution.  In time, I hope to develop this code into something beautiful, into a tool that will rewrite nested views into their simplest components, whilst simultaneously washing your dog and paying your overdue utility bills. But for now, this is what we have.

Note: This script is workable, but not complete. It doesn’t currently take different schemas into account, and there’s a lot more functionality I want to add to it.  Consider this a draft, or an early beta, and use it as intended.

A nested view is like a tree: the top node (the parent view) references one or more child nodes (child tables and views), which in turn may reference another level of child nodes, and so on.  Here we have a script that takes a given view and works through each level of nodes, to ferret out all the referenced objects:

----------------------------------------------------------------------------
-- Create temp table, variables
----------------------------------------------------------------------------
-- Create a temp table to hold the view/table hierarchy
CREATE TABLE #viewHierarchy
( id INT IDENTITY(1,1)
, parent_view_id INT
, referenced_schema_name NVARCHAR(255)
, referenced_entity_name NVARCHAR(255)
, join_clause NVARCHAR(MAX)
, [LEVEL] TINYINT
, lineage NVARCHAR(MAX)
)
DECLARE @viewname NVARCHAR(1000),
@count INT,        -- Current ID
@maxCount INT    -- Max ID of the temp table

-- Set the name of the top level view you want to detangle
SELECT @viewName = N'vw_stadium_tickets_current_football',
@count = 1
----------------------------------------------------------------------------
-- Seed the table with the root view, and the root view's referenced tables.
----------------------------------------------------------------------------
INSERT INTO #viewHierarchy
SELECT NULL parent_view_id
, 'dbo' referenced_schema_name
, @viewName referenced_entity_name
, NULL join_clause
, 0 [LEVEL]
, '/' lineage

INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, 1 [LEVEL]
, '/1/' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')

SELECT @maxCount = MAX(id)
FROM #viewHierarchy
----------------------------------------------------------------------------
-- Loop through the nested views.
----------------------------------------------------------------------------
WHILE (@count < @maxCount) -- While there are still rows to process...
BEGIN
SELECT @count = @count + 1

    -- Get the name of the current view (that we'd like references for)
SELECT @viewName = referenced_entity_name
FROM #viewHierarchy
WHERE id = @count

    -- If it's a view (not a table), insert referenced objects into temp table.
IF EXISTS (SELECT name FROM sys.objects WHERE name = @viewName AND TYPE = 'v')
BEGIN
INSERT INTO #viewHierarchy
SELECT DISTINCT @count parent_view_id
, referenced_schema_name
, referenced_entity_name
, '' join_clause
, NULL [LEVEL]
, '' lineage
FROM sys.dm_sql_referenced_entities(N'dbo.' + @viewName,'OBJECT')
SELECT @maxCount = MAX(id)
FROM #viewHierarchy
END
END
--------------------------------------
--------------------------------------
WHILE EXISTS (SELECT 1 FROM #viewHierarchy WHERE [LEVEL] IS NULL)
UPDATE T
SET T.[Level] = P.[Level] + 1,
T.Lineage = P.Lineage + LTRIM(STR(T.parent_view_id,6,0)) + '/'
FROM #viewHierarchy AS T
INNER JOIN #viewHierarchy AS P ON (T.parent_view_id=P.ID)
WHERE P.[Level]>=0
AND P.Lineage IS NOT NULL
AND T.[Level] IS NULL
SELECT       parent.*
,child.id
,child.referenced_entity_name ChildName
FROM #viewHierarchy parent
RIGHT OUTER JOIN #viewHierarchy child ON child.parent_view_id = parent.id
ORDER BY parent.id, child.id

This generates a table that we join to itself to get a hierarchical view of parent and child nodes:

So far, this is useful in quickly getting an overall picture of how complicated a nested view is, how many times it hits a particular table, etc.  Like I said, this is the basics, and I’d like to do more. If you do more with it, drop a comment or email and let me know what you did! I’m always interested in seeing improvements and expansions.

Feel free to use this work in any way you like, but be kind and slap a link back here in the comments.

Happy days,

Jen

And as always, thanks to the Simple-Talk SQL Prettifier for its help today.

The Week in Awesome – Plugins, Cars, Food, and France


awesomeThis week in awesome:

Denny Cherry – our own @mrdenny – has created a Password Vault WordPress plugin. That looks seriously cool.

Google has created a prototype of a totally self-driving car. It looks like a toy, but feels like the future.

As long as we’re talking about cars, go look at The Oatmeal’s great new comic “What it’s like to own a Tesla Model S“, if you don’t mind a bit of naughty language. Which I don’t. Warning: Will make you want to buy a Tesla Model S.

Sean at PluralsightLast week, Sean and I flew to Chicago to film a career course with @pluralsight. We are, shall we say, extremely excited about it.

@ScriptingWife says “New #PowerShell User Group locations I am working with to get a group started, Orange County CA, Orlando FL, New Jersey, Austin TX, Denver”  I’ll  be looking out for that.

Here’s an article that makes the wild and crazy claim that coding is not easy. I’m actually down with that, as I am with the “Recursive Recipe for Learning to Program” given therein. (Via @ShawnHooper.)

Me: All I want is to be able to consistently copy-paste from remote. Is that too much to ask, lord?
@markvsql: Lord, won’t you get me some Copy and Paste. I have to keep setting it up, it’s just such a waste…
Me: Prove that you love me, and fix this, in haste…oh lord, won’t you get me, some copy and paste.
[You really have to know your Janis Joplin for this one.]

In Twitter recipe news: “Fry the bacon in the waffle iron, then make a waffle. It’s amazing.”  @dmmaxwell.

@spacechelle let us know that there’s going to be a Disney-Star-Wars-half-marathon-thingy in 2015. Count me in.

Success convinces us that we are doing things the right way. There is nothing quite as effective, when it comes to shutting down alternative viewpoints, as being convinced you are right.
- Pixar founder Ed Catmull (via wilwheaton)

@billinkcSubmitting a Connect item to send mild to strong electrical currents to people storing concatenated lists in columns
[I'd support this.]

Finally, tonight is the season 5 ender for our weekly webshow, DBAs@Midnight. Come see us live at http://webshow.MidnightDBA.com at 11pm Central time, because we’re next live on the air sometime in, oh, August maybe. (Now seemed like a good time to end the season, as we’re about to spend a few weeks in France. I know you’ll miss me, but we’ll always have Twitter and all the “from the vault” posts I scheduled to appear here!)

Happy days,
Jen
http://www.MidnightDBA.com/Jen

P.S. Some nonzero percentage of you readers nodded approvingly at the Oxford comma in the title. I nod approvingly, right back at you.

Applied SQL: Orphan a user, fix orphaned users (sp_FixOrphanedUsers)

olivertwistEver heard of orphaned users? It’s a database user account, disconnected from its associated login. You typically get this when you take a database backup, and restore it to a different server.

Microsoft provides us with the ever helpful sp_change_users_login to find and repair orphaned users, but even so, it’s kind of a PITA* manual operation. To simplify and provide for automation, I’ve put together the brand-spanking-new sp_FixOrphanedUsers.

First, let’s orphan some users

If you’d like to test this, here is a repro scenario that creates an orphaned user on a single instance. It orphans a user by doing the following:

  1. Create a login “OliverTwist”**.
  2. Create a database.
  3. Create a user in that database from the “OliverTwist” login.
  4. Back up the database, then drop it.
  5. Drop the login.
  6. Restore the database.

I will say that steps 4 and 6 aren’t strictly necessary; just dropping the login will orphan the user. But I’d like to drill the idea of orphans being associated with restores, because that’s the most common scenario in real life.

Important: ALWAYS review code before you run it, even on a test instance. 

Now let’s resolve a single orphaned user

When you have an orphaned user, it’s really easy to detect and fix them. In our case, we’d find the orphan by running

EXEC OrphanedUserDemo..sp_change_users_login 'Report';

And then, we could fix that orphan by first creating the missing login, and then running

EXEC OrphanedUserDemo..sp_change_users_login 'AUTO_FIX', 'OliverTwist';

(Or, if we want the missing login to be created automatically, then “EXEC OrphanedUserDemo..sp_change_users_login ‘AUTO_FIX’, ‘OliverTwist’, NULL, ‘NewPasswordOMG!!!’;“)

But this is a manual process, and therefore slow and tedious and not entirely worth our time.

Let’s resolve many orphaned users

Create the stored procedure sp_FixOrphanedUsers in master, and run it as [DBName]..sp_FixOrphanedUsers, and it fixes all orphaned users in that database, if it can do so automatically.

Better still, you could run sp_FixOrphanedUsersAllDB (included) to run this process against all databases on the SQL Server instance.

If you have a regular refresh process – like restoring a set of databases to UAT – you could create a second step in the job to run sp_FixOrphanedUsersAllDB, and greatly simplify your life.

Limitations and caveats

Of COURSE there are limitations, silly reader!

  • Don’t trust code you download from the web. ALWAYS review code before you run it, even on a test instance.
  • This is the first iteration of the procedure(s). Doubtless, you and other readers will have helpful suggestions on how to improve the thing.
  • In this iteration, the SP doesn’t have the ability to specify a password to auto-create missing logins. I think this is a really bad practice. (“Oh, this server doesn’t have a login for ‘GodlyAdminGuy’? Let’s create on with ‘P@ssw0rd!’ for the default!” Yeah, no.)
  • And other things I haven’t yet thought of, but will doubtless be pointed out to me enthusiastically and repeatedly. Feel free, but be cool.

Downloads

Happy days,
Jen McCown
http://www.MidnightDBA.com/Jen

*PITA = “Pain In The Analytics”, so far as you know.

** GET IT??

MidnightSQL Consulting

Need help? Got an emergency? Write us at Support@MidnightDBA.com!

We can schedule time to help with your backup/restore issues, high availability and disaster recovery setup, performance problems, and a great deal more. Very often, we're even available on the moment for downtime issues and emergencies.

For more information about MidnightSQL consulting, email us or check out www.MidnightSQL.com. Happy days!

Where are We?

May 10: SQL Saturday Houston
August 2: SQL Saturday Baton Rouge (planned)
August 22-23: SQL Saturday Oklahoma City Precon!

Blog Posts by Category

DBAs@Midnight

How to Eat Pop-tarts
Watch DBAs@Midnight live on Fridays,m 11pm Central time

The best database career advice you’ve never heard!

DBARoadmap.com

The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!

Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)

Become a DBA. Become a BETTER DBA. Use the Roadmap.

Visit www.DBARoadmap.com for info, forums, and more!

Add to Cart View Cart

Cunningham’s Law

"The best way to get the right answer on the Internet is not to ask a question, it's to post the wrong answer."
Relevant: http://xkcd.com/386/