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!