Work on development assets, then deploy to production. How can you work on development assets without realistic duplicates from production? I have actually heard bush-league ColdFusion programmers brag on faking the data on development. What? So, you are an expert in your customer's field too? Although I have had to learn a lot more about my customer's processes than I ever cared to over the years, I will never fool myself into believing I can create realistic pseudo-data by which to code to their needs. That's why I would always recommend forcing a daily copy of the database(s) from the production server to the development server and work from there.
The projects I have inherited at work were created by someone who thought far too highly of Foreign Keys (FK), they are everywhere. An FK constraint forces a link between data for two tables. Normally this would be a good thing, but the problem was that these databases were created without master-storage tables with numeric representation tying them to list tables.
Through Microsoft SQL 2008, an automation (job) can be easily created using SQL Server Integration Services (SSIS) to schedule a copy task for each database, except when they are bound together with FK constraints. Using the MATER table, you can scan the "sys.databases" table for all your databases (excluding the MATER, MODEL, MSDBDATA, and TEMPDB system databases). You could hard-code for specific database, but this solution will copy any new ones added later.
Create a CURSOR, which we'll use to parse through the total of all your database names and determine which tables are tied together using foreign key constraints and store the results (I'm storing them in the MASTER and not the TEMPDB because I need access to them later). After the results are stored, we can kill the FK's and copy the databases unconstrained.
USE [master];
IF OBJECT_ID('dbo.ForeignKeysList') IS NOT NULL DROP TABLE ForeignKeysList
CREATE TABLE ForeignKeysList (
DBName varchar(25),
FK_Table varchar(50),
FK_Column varchar(50),
PK_Table varchar(50),
PK_Column varchar(50),
Constraint_Name varchar(100))
DECLARE DBNames CURSOR
FOR
SELECT [name]
FROM sys.databases
WHERE Len(owner_sid) > 1
ORDER BY [name]
OPEN DBNames
DECLARE @Name varchar(50)
DECLARE @SQL varchar(max)
FETCH NEXT FROM DBNames
INTO @Name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC ('USE [' + @Name + ']')
SET @SQL='';
SET @SQL=@SQL+'SELECT ''' + @Name + ''' AS DBName,FK_Table = FK.TABLE_NAME,';
SET @SQL=@SQL+'FK_Column = CU.COLUMN_NAME,';
SET @SQL=@SQL+'PK_Table = PK.TABLE_NAME,';
SET @SQL=@SQL+'PK_Column = PT.COLUMN_NAME,';
SET @SQL=@SQL+'Constraint_Name = C.CONSTRAINT_NAME ';
SET @SQL=@SQL+'FROM [' + @Name +
'].INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C INNER JOIN ';
SET @SQL=@SQL+'[' + @Name +
'].INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN ';
SET @SQL=@SQL+'[' + @Name +
'].INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN ';
SET @SQL=@SQL+'[' + @Name +
'].INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ';
SET @SQL=@SQL+'(SELECT i1.TABLE_NAME, i2.COLUMN_NAME ';
SET @SQL=@SQL+'FROM [' + @Name +
'].INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1 INNER JOIN ';
SET @SQL=@SQL+'[' + @Name +
'].INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME ';
SET @SQL=@SQL+'WHERE (i1.CONSTRAINT_TYPE = ''PRIMARY KEY'')) AS PT ON ';
SET @SQL=@SQL+'PT.TABLE_NAME = PK.TABLE_NAME ';
SET @SQL=@SQL+'ORDER BY 1,2,3,4,5';
INSERT INTO ForeignKeysList
EXEC (@SQL);
FETCH NEXT FROM DBNames
INTO @Name
END
CLOSE DBNames
DEALLOCATE DBNames
DECLARE ForeignKey CURSOR
FOR
SELECT *
FROM ForeignKeysList
OPEN ForeignKey
DECLARE @DBName varchar(25)
DECLARE @FK_Table varchar(50)
DECLARE @FK_Column varchar(50)
DECLARE @PK_Table varchar(50)
DECLARE @PK_Column varchar(50)
DECLARE @Constraint_Name varchar(100)
FETCH NEXT FROM ForeignKey
INTO @DBName, @FK_Table, @FK_Column, @PK_Table, @PK_Column, @Constraint_Name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @SQL='';
SET @SQL=@SQL+'ALTER TABLE [' + @DBName + '].dbo.' + @FK_Table + ' ';
SET @SQL=@SQL+'DROP CONSTRAINT ' + @Constraint_Name;
EXEC (@SQL);
FETCH NEXT FROM ForeignKey
INTO @DBName, @FK_Table, @FK_Column, @PK_Table, @PK_Column, @Constraint_Name END
CLOSE ForeignKey
DEALLOCATE ForeignKey
Last in the job, after the databases are copied it's time to add the FK entries back from our MASTER database. Creating a CURSOR again, we loop through the results and ALTER each table based on the results and re-establish the foreign key.
USE [master];
DECLARE @SQL varchar(max)
DECLARE ForeignKey CURSOR
FOR
SELECT *
FROM ForeignKeysList
OPEN ForeignKey
DECLARE @DBName varchar(25)
DECLARE @FK_Table varchar(50)
DECLARE @FK_Column varchar(50)
DECLARE @PK_Table varchar(50)
DECLARE @PK_Column varchar(50)
DECLARE @Constraint_Name varchar(100)
FETCH NEXT FROM ForeignKey
INTO @DBName, @FK_Table, @FK_Column, @PK_Table, @PK_Column, @Constraint_Name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @SQL='';
SET @SQL=@SQL+'ALTER TABLE [' + @DBName + '].dbo.' + @FK_Table + ' ADD CONSTRAINT ';
SET @SQL=@SQL+@Constraint_Name + ' FOREIGN KEY (' + @FK_Column +
') ';
SET @SQL=@SQL+'REFERENCES dbo.' + @PK_Table + ' (' + @PK_Column
+ ')'
EXEC (@SQL);
FETCH NEXT FROM ForeignKey
INTO @DBName, @FK_Table, @FK_Column, @PK_Table, @PK_Column, @Constraint_Name END
CLOSE ForeignKey
DEALLOCATE ForeignKey
IF OBJECT_ID('dbo.ForeignKeysList') IS NOT NULL DROP TABLE ForeignKeysList
Don't forget, these kinds of automation could always go wrong if scheduled to happen while you are not present to save the day. Plan accordingly, execute wisely, and back-up often!
If you find this post useful please leave a comment and let me know how you used the information.
