Generate database content in SQL script by SQL script

A few years back I had a very though project, out team had to release a new version of the intranet portal based on junky and very ugly code that we did inherit after previous company. We have been the 3rd team working on the release, we did manage to deliver, but at a high cost of morale, man-hours and compromises. Suffice to say that we did each 400 man-hours per month, at the peak. Never again.

Team members needed to change database schema and test-data daily and independently, no one had time to integrate changes frequently, so „master” database backup was outdated most of the time. There where tools (eg. http://www.red-gate.com) that we couldn’t afford, at least we thought so, at the time 😉 . Soon this was a major source of problems. I have decided that we MUST do a daily database change integration and ALL team members have to work on a current database.

You need for your saw to be sharp, when you must do things repeatably, reliably and you have no time. I decided to write a neat script that would generate a SQL code with database content, that could then be manipulated in the text editors and kept in the CVS repository. Actually this script work as a tandem with SQL Server 2000 generate scripts function, for schema changes merging.

This is that background. I realise that the script could be written better or cleaner, but this is not a production code, it is supposed to create some output in a desired way, and it does. I use that script successfully for 5 years now (as other projects can benefit from it too). I have added some tweaks and modifications over time, but the foundation concept is the same and it work for well for me.

Features:

• Works from Query Analyser/Management Studio
• Allows you to generate content SQL script in 2 steps:
  • Generate content generation script
  • Generate content SQL script with result of the previous step
• Takes care of table relationships and dependencies (sorry no circular dependencies support 🙁 )
• Identity insert support
• Generates an SQL script that can be easily manipulated in notepad and merged during CVS/SVN updates/commits
• Allows you to keep database schema and content (test data, initial dictionary tables fill-up) only in clean SQL scripts, no backups necessary 🙂
  • Integrated with NAnt, NUnit and Cruise Control gives a powerful mechanism for reliable and repeatable test automation

How it works:

• It uses SQL Server system tables to find out table relations and schema (uses a special view for this).
• Sorts tables in order of their dependencies
• Generates a content generation SQL script for a specific database.

What it needs:

• Query results in Text
  • „Include column headers in the result set” – Off
  • „Maximum number of characters displayed in each column” – set to maximum, 8192.

-- ===================================================================
-- Generates database a script that will generate a script with
-- database content in SQL insert statements.
-- Works with MS SQL Server 2000/2005
--
--  Copyright 2002-2006 Janusz Skonieczny
--
--   Licensed under the Apache License, Version 2.0 (the "License");
--   you may not use this file except in compliance with the License.
--   You may obtain a copy of the License at
--
--       http://www.apache.org/licenses/LICENSE-2.0
--
--   Unless required by applicable law or agreed to in writing, software
--   distributed under the License is distributed on an "AS IS" BASIS,
--   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
--   See the License for the specific language governing permissions and
--   limitations under the License.
--
-- Author: Janusz Skonieczny
--         jskonieczny@gmail.com
--         http://skonieczny.pl/software
--
-- ===================================================================
-- Last changes made:
-- $Id:  $
-- ===================================================================
-- Search & Replace db name : MyDatabase
--
-- Contact the author if you have any questions.
-- ===================================================================

USE [MyDatabase]

set nocount on

-- ========================================================================
-- Creates a a helper view with table names, table id's and tables
-- connected by FK
-- ========================================================================

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW v_Dependencies
AS

SELECT dep.name 	as name, ss.name as schema_name,
dep.id,
case when  dep.name = depc.name then null
else depc.name
end as depends
--	depc.name	as depends
--	depon.name,
--	dep.xtype,
--	depc.*,
--	''
FROM sysobjects dep
-- 	LEFT JOIN sysdepends on dep.id = sysdepends.id
-- 	LEFT JOIN sysobjects depon on sysdepends.depid = depon.id
--	LEFT OUTER JOIN (SELECT * from sysconstraints where sysconstraints.status & 3 = 3) const on dep.id = const.id
--	LEFT OUTER JOIN sysobjects depc on const.constid = depc.id
LEFT OUTER JOIN sysforeignkeys fk on dep.id = fk.fkeyid
LEFT OUTER JOIN sysobjects depc on fk.rkeyid = depc.id

LEFT OUTER JOIN sys.objects so on so.object_id = dep.id
LEFT outer JOIN sys.schemas ss on ss.schema_id = so.schema_id
WHERE
dep.xtype = 'U' AND dep.name <> 'dtproperties' AND dep.name <> 'sysdiagrams'
--and (sysconstraints.status & 3 = 3 or sysconstraints.status is null)
--and dep.xtype <> 'S' AND dep.name <> 'dtproperties'
--order by dep.xtype,depc.name
--order by name

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

DECLARE @database VARCHAR(255)
SELECT @database = '['+DB_NAME()+']'

-- ========================================================================
-- Sorts tables acording to their dependecies
-- (highest priority, least dependencies)
-- ========================================================================
declare @tablesTable Table(name nvarchar (128),id int,depends nvarchar (128),priority int,schema_name nvarchar(128))
declare @tablesTable2 Table(name2 nvarchar (128),id2 int,depends2 nvarchar (128),priority2 int,schema_name nvarchar(128))
declare @count int
declare @stopCount int
set @count = 1
set @stopCount = 1

-- select * from v_Dependencies

insert into @tablesTable (name,id,depends,priority,schema_name)
select name,id,depends,0,schema_name
from v_Dependencies v

update @tablesTable set priority = 2 where depends is null or depends = name

-- ustaw najnisze z jej priority dla kazdej z tabel -------
delete from @tablesTable2
insert into @tablesTable2 select * from @tablesTable
update @tablesTable set priority = (select min(priority2) from @tablesTable2 where name = name2)
delete from @tablesTable2
insert into @tablesTable2 select * from @tablesTable
-- ---------------------------------------------------

while @count > 0 and @stopCount < 40  begin

update @tablesTable set priority = priority + 1 where
depends in (select name2 from @tablesTable2 where priority2 > priority)
or depends = name or depends is null

-- ustaw najnisze z jej priority dla kazdej z tabel -------
delete from @tablesTable2
insert into @tablesTable2 select * from @tablesTable
update @tablesTable set priority = (select min(priority2) from @tablesTable2 where name = name2)
delete from @tablesTable2
insert into @tablesTable2 select * from @tablesTable
-- ---------------------------------------------------

select @count = count(*) from @tablesTable where priority = 0

update @tablesTable set priority = priority + 1 where priority > 0

select @stopCount = @stopCount + 1
--select name,depends,priority from @tablesTable order by name, Priority desc
end
delete from @tablesTable2
insert into @tablesTable2 (name2,priority2,id2) select schema_name+'.['+name+']',priority, id from @tablesTable group by name, priority, id, schema_name

-- ========================================================================

DECLARE @dbid INT

SELECT @dbid = dbid
FROM master..sysdatabases
WHERE name = @database

DECLARE @sql1 VARCHAR(8000)
DECLARE @sql2 VARCHAR(8000)
DECLARE @name NVARCHAR(255)
DECLARE @field_name VARCHAR(255)
DECLARE @type_name VARCHAR(255)
DECLARE @field_cnt INT
DECLARE @id INT
DECLARE @identity_fields INT
DECLARE @apos8 VARCHAR(8)
DECLARE @depends VARCHAR(2000)

set @apos8 = '''''''''' -- > '''' --> ''

print '-- ============================================================================== '
print '--  Skrypt generujacy skrypt z insertami wypeniajcymi baz pusta danych :'
print '--     '+ @database
print '--  Wygenerowany w dniu: ' + CONVERT(nvarchar,getDate(),120) + ' przez ' + SYSTEM_USER
print ''
print '--  Skrypt powinien zachowa zalenoci pomiedzy tabelami, to trzeba przetestowa '
print '--  $Id: $ '
print '-- ============================================================================== '

print 'use ' + @database
print 'set nocount on'

print ' PRINT ''-- ============================================================= '''
print ' PRINT ''--  Skrypt zawierajcy dane z bazy: ' + @database + ''''
print ' PRINT ''--  Wygenerowany w dniu: '' + CONVERT(nvarchar,getDate(),120) + '' przez '' + SYSTEM_USER '
print ' PRINT ''--  $Id: $ '''
print ' PRINT ''-- ============================================================= '''
print ' PRINT ''use ' + @database + ''''
print ' PRINT ''set nocount on'''
print ' PRINT '''''
print ' PRINT ''-- ============================================================= '''
print ' PRINT ''-- Oczysc baze '''
select ' PRINT ''DELETE ' + name2 + '''' from @tablesTable2 order by priority2 asc
print ' PRINT ''-- ============================================================= '''

DECLARE cur_tables CURSOR FOR
SELECT name2, id2
FROM @tablesTable2
order by Priority2 desc
--WHERE xtype = 'U' AND name <> 'dtproperties'
--FOR READ ONLY

OPEN cur_tables

FETCH NEXT FROM cur_tables
INTO @name, @id

WHILE @@FETCH_STATUS = 0
BEGIN
print ''
print ' PRINT ''-- ============================================================= '''
print ' PRINT ''-- 	' + @name + ''''
if (select count (*) from @tablesTable where name = @name and depends is not null) > 0 begin
print ' PRINT ''--'''
print ' PRINT ''-- Depends on: '''
select ' PRINT ''-- 	' + depends + '''' from @tablesTable where name = @name and depends is not null
end
print ' PRINT ''-- ============================================================= '''
print ' PRINT '''''
print ''
SELECT @sql1 = 'SELECT ''INSERT INTO ' + @name + ' ('
SELECT @sql2 = ''
SELECT @field_cnt = 0

SELECT @identity_fields = COUNT(*)
FROM syscolumns
WHERE id = @id AND (status & 128 <> 0)

IF @identity_fields > 0
BEGIN
SELECT 'SELECT ''SET IDENTITY_INSERT ' + @name + ' ON'''
END

DECLARE cur_fields CURSOR FOR
SELECT syscolumns.name, systypes.name
FROM syscolumns INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE id = @id
FOR READ ONLY

OPEN cur_fields

FETCH NEXT FROM cur_fields
INTO @field_name, @type_name

WHILE @@FETCH_STATUS = 0
BEGIN

IF @type_name <> 'sysname'
BEGIN
IF @field_cnt > 0
BEGIN
SELECT @sql1 = @sql1 + ','
SELECT @sql2 = @sql2 + ' + '','''
END

SELECT @field_cnt = @field_cnt + 1

SELECT @sql1 = @sql1 + '[' + @field_name + ']'
SELECT @sql2 = @sql2 + '+ '

IF @type_name = 'int' OR @type_name = 'smallint' OR @type_name = 'bit' OR @type_name = 'tinyint'  OR  @type_name = 'bigint' OR  @type_name = 'float' OR  @type_name = 'money' begin
SELECT @sql2 = @sql2 + 'ISNULL (CONVERT (varchar, [' + @field_name + ']), ''NULL'') COLLATE POLISH_CI_AS'
end
ELSE IF @type_name = 'nvarchar' OR @type_name = 'varchar' OR @type_name = 'char' OR @type_name = 'nchar' or @type_name = 'uniqueidentifier' BEGIN
SELECT @sql2 = @sql2 + 'CASE WHEN [' + @field_name +']'
SELECT @sql2 = @sql2 + ' IS NULL THEN ''NULL'' ELSE '
SELECT @sql2 = @sql2 + ' + '''''''' + REPLACE ([' + @field_name + '], '''''''', '''''''''''') COLLATE POLISH_CI_AS + '''''''' END'
END
ELSE IF @type_name = 'ntext' or @type_name = 'text'BEGIN
SELECT @sql2 = @sql2 + 'CASE WHEN [' + @field_name +']'
SELECT @sql2 = @sql2 + ' IS NULL THEN ''NULL'' ELSE '
SELECT @sql2 = @sql2 + ' + '''''''' + REPLACE (CAST([' + @field_name + '] as nvarchar(4000)), '''''''', '''''''''''') COLLATE POLISH_CI_AS + '''''''' END '
END
ELSE IF @type_name = 'binary' OR @type_name = 'image' BEGIN
SELECT @sql2 = @sql2 + '''NULL'''  -- binariues not suported
END
ELSE IF @type_name = 'datetime' begin
SELECT @sql2 = @sql2 + 'CASE WHEN [' + @field_name +']'
SELECT @sql2 = @sql2 + ' IS NULL THEN ''NULL'' ELSE '' '
SELECT @sql2 = @sql2 + 'CONVERT (datetime, '''''' + '
SELECT @sql2 = @sql2 + 'CONVERT (varchar, '
SELECT @sql2 = @sql2 + @field_name
SELECT @sql2 = @sql2 + ', 121) + '''''', 121)'' END'
end
ELSE
PRINT 'Unknown type : ' + @type_name + ' in table : ' + @name + ' field : ' + @field_name
END

FETCH NEXT FROM cur_fields
INTO @field_name, @type_name
END

CLOSE cur_fields
DEALLOCATE cur_fields

SELECT @sql2 = @sql1 + ')
VALUES (''' + @sql2 + ' + '')''
FROM '  + @name
select @sql2

IF @identity_fields > 0
BEGIN
SELECT 'SELECT ''SET IDENTITY_INSERT ' + @name + ' OFF'''
END

FETCH NEXT FROM cur_tables
INTO @name, @id
END

CLOSE cur_tables
DEALLOCATE cur_tables

DROP VIEW v_Dependencies

You can download it here.