TSQL: Programmatically Create DDL scripts for Foreign Keys

I'll admit it, I'm lazy... I hate typing exercises. I don't need the practice and I'm sure the person paying my somewhat exhorbitant fee doesn't want to fork out for something so mundane either. That means I'm always on the hunt for a shortcut or two, I mean, process improvement.

These two snippets will generate the DDL to create all the foreign keys in the database.
The first will check if it exists first, the second drops it first. Use whichever suits.

SELECT 'IF NOT EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.name + ' WITH CHECK '
+ char(13) + char(9) + 'ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13) + 'END'
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

And the second one (ie does a drop first)...

SELECT DISTINCT 'IF EXISTS (SELECT * FROM sys.FOREIGN_KEYS WHERE name = ''' + fk.Name + ''' AND Parent_Object_Id = object_id(''['+ s.name + '].[' + t.name + ']'')) BEGIN '
+ char(13) + char(9) + 'ALTER TABLE ' + s.name + '.' + t.Name + ' DROP CONSTRAINT ' + fk.Name
+ char(13) + 'END'
+ char(13) + 'ALTER TABLE ' + s.name + '.' + t.name
+ char(13) + char(9) + 'WITH CHECK ADD CONSTRAINT ['+fk.Name +'] '
+ char(13) + char(9) + 'FOREIGN KEY(['+c.name+']) REFERENCES [' + fks.name + '].['+fkt.name+'] (['+fkc.name+']) '
+ char(13)
from sys.foreign_keys fk
inner join sys.foreign_key_columns fkcm on fkcm.constraint_object_id = fk.object_id
inner join sys.tables t on t.object_id = fkcm.Parent_Object_Id
inner join sys.tables fkt on fkt.object_id = fkcm.Referenced_Object_Id
inner join sys.columns c on c.object_id = t.object_id and c.column_id = fkcm.parent_column_id
inner join sys.columns fkc on fkc.object_id = fkt.object_id and fkc.column_id = fkcm.referenced_column_id
inner join sys.schemas s on s.schema_id = t.schema_id
inner join sys.schemas fks on fks.schema_id = fkt.schema_id
ORDER BY 1

Integration Services Gotcha #6631984: The Table Variable Data Flow Affair

I encountered a teeny little SSIS glitch today that completely did my head in for a good 30 minutes. Ok, I'll admit it, more like an hour.

In my data flow, I had an OLEDB Source which declared and populated a table variable then returned some data, which included a join on my table variable.

The source query worked perfectly, returning plenty of data when tested through SSMS. As soon as I ran the data flow, it would be successful but zero data.

That freakin' table variable was messin' with me. Took it out, replaced it with a subquery and whammo! Data.

Oh SSIS, you pernickety old man, you!

SQL 2008: Using MERGE to do a Type 2 Change

I'm going to do a brief overview of the SQL 2008 MERGE statement. Primarily for my own purposes - being to document the usage for type 2 changes so I don't forget it.

First let's begin with the basic type 1 usage.


All fairly self-explanatory. We have a source and a target. We join the two tables, identify new records, changed records and deleted records. And deal with them appropriately.

So let's move onto the type 2 changes. Here we need to:

• Insert New Records
• Disable changed records
• Add a new record for the changed records
  

That's 2 inserts and an update. The MERGE won't allow that so we have to nest our merge inside an insert. Luckily this works because MERGE allows output!

Ignore the outer INSERT for the moment. We have a MERGE statement which is very similar to our type 1 MERGE. Compare the Source and Target, INSERT new records as required. The difference being that when the matched record contains a change, instead of updating the changed field, we set the CurrentFlag and the EndDate so as to disable the record.

Of course that means we still don't have a record which contains our updated values. So, now lets turn our attention to the OUTPUT clause.

This will be familiar to anyone who used it in SQL 2005 in much the same context - to identify rows which have been modified/added/deleted. Really the origin of MERGE seems to be strongly rooted in the SQL 2005 OUTPUT clause. On the upside, unlike the SQL 2005 version and UPDATE is an UPDATE and not a DELETE and an INSERT. Small but handy! Anyway, where were we? Let's return the columns we need to create a new record and a special $Action column - this tells us what actually happened ie UPDATE, INSERT or DELETE. We need this because the OUTPUT will return everything that occured in the MERGE.


Now we have a set of rows that have been actioned by the MERGE. Still, we haven't actually done anything with them.

To do that, we enclose the entire MERGE statement, including it's OUTPUT clause of course, into brackets so we can make it the FROM clause for an INSERT. Our OUTPUT clause is return the columns we need for the INSERT remember? So now we just add our INSERT INTO at the front, add an alias to the closed bracket of the FROM clause AND, drumroll... whack a WHERE clause on the end. This where clause just filters the OUTPUT from the MERGE so we only insert new records for those that were updated, ignoring those that may have been inserted etc in the MERGE.

Easy!


-------------------------------------------------------------------------------------------------

For the purposes of copy/paste-ability, here is a text version of the TSQL. Images have been used above for improved readability.

--A simple type 1 change
MERGE INTO DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED BY TARGET THEN --ie new
INSERT (CustomerId,CustomerName,IsActive) VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
)
WHEN MATCHED AND c.CustomerName <> stg.CustomerName THEN --ie changed
UPDATE SET c.CustomerName = stg.CustomerName
WHEN NOT MATCHED BY SOURCE THEN --ie deleted
DELETE


--a type 2 change
INSERT INTO dbo.DimCustomer
SELECT CustomerId, CustomerName, IsActive, IsCurrent, Eff_Date, End_Date
FROM
( MERGE DimCustomer c
USING StageCustomer stg ON c.CustomerId = stg.CustomerId
WHEN NOT MATCHED THEN INSERT VALUES (
stg.CustomerId
,stg.CustomerName
,stg.IsActive
,1 --> IsCurrent
,GETDATE() --> Eff_Date
,NULL --> End_Date
)
WHEN MATCHED AND c.IsCurrent = 1 AND (c.CustomerName <> stg.CustomerName) THEN UPDATE SET
c.IsCurrent = 0
,c.End_date = GETDATE()
OUTPUT $Action as RowAction
,stg.CustomerId
,stg.CustomerName
,1 AS IsCurrent
,GETDATE() as Eff_Date
,NULL as End_Date
) m
WHERE m.RowAction = 'UPDATE'

SharePoint Magazine - Part 4 available now

My next article for SharePoint Magazine is available now.

Dissecting Dashboards (Part 4 of 6)

Sharepoint Saturday

Register now for Sharepoint Saturday in Sydney on August 8th.

http://www.sharepointsaturday.org/sydney/default.aspx

Can Dynamics CPM pick up where PerformancePoint left off?

Can Dynamics CPM pick up where PerformancePoint left off? And how does all of this fit in with SSRS Report Builder and Project Gemini, a key part of Microsoft's self-service BI drive? When you say things like "Microsoft Forecaster is a budgeting and planning application that allows companies to build budgets based on specific objectives", one has to wonder!

And "Enterprise Reporting is designed for sophisticated group reporting and consolidation needs, and includes advanced multi-dimensional consolidations, eliminations and multicurrency capabilities. Enterprise Reporting’s planning environment is easily customizable to fit diverse budgeting and forecasting needs across all industries and organizations. Enterprise Reporting provides strong analytical modules to make it extremely easy to create new ad-hoc reports on the fly"

Seriously? That's quite a bundle of tasks. Enquiring minds want to know more!

PerformancePoint Planning: Bug in the bsp_DI_ValidateLabelTable Proc

Somewhat irrelevant due to shut down of PerformancePoint Planning but it's an issue I encountered so I will record it for posterity and future generations (*snigger*).

There is a bug in the bsp_DI_ValidateLabelTable proc which causes it to fail when you are using a specific hierarchy in the model rather than the default 'all members' set. The problem arises when you pass in an incorrect model name.

The proc does not validate that the model name is actually valid before continuing (naughty naughty) which means you can actually cause errors if it just so happens that the value you pass in is a valid object - just not the name of the model itself. Let me show you want I mean.

First let's just mock up some object labels (please note these are NOT real. I would never be so generic but for the purpose of examination...).

Application: PPS

RootModelSite: PPS_Planning

Model: PPS

When calling PPSCMD you often refer to PPS:PPS_Planning.

When executing the bsp_DI_ValidateLabelTable proc, it asks for modelname as a parameter. In my example, I will have a scope in FK_relationships for PPS:PPS_Planning to identify the modelsite but that's not the one I need. Nope. I need PPS_Planning:PPS, which identifies the model.

But I'm half asleep and I've typed PPS:PPS_Planning in the past 24 hours so many times that I just don't think. Uh oh, all of a sudden the proc fails - but not after having made a few updates already.

Silly mistake to make, no question. Still, it does seem a bit dodgy to take an string input from a user, which is also a unique label used to identify a specific record, and not validate that it actually exists before making updates using that value!