Data Sample Scripter
20160503 Update
- Refines handling of tables with self-referencing foreign keys to insure any other foreign keys on the table are satisfied first.
20151007 Update
- Adds explicit casts to date and time types to avoid code analysis warnings about potential code loss on data type conversion
- Adds support for tables with foreign keys referencing themselves. This would have caused an infinite loop with FK awareness enabled before the code change.
This script will generate INSERT statement wrappers for VALUES elements (<schema name>_<table name>_frontInsertWrapper and <schema name>_<table name>_backInsertWrapper) for a given list of tables, as well as a script that in turn generates values elements for the tables (<schema name>_<table name>_valueGeneratorScript).
If fkAwareness is enabled, the script will also generate these scripts for all tables referenced via foreign key by those in the list, recursively. The scripts will be presented in the order of dependency, i.e., the first scripts will be for tables with no dependencies/foreign keys, followed by the tables that reference them, etc.
If fkAwareness and includeDeleteStatement are enabled, an additional script is returned that presents conditional delete statements for all involved tables in reverse dependency order (deleteStatementsForTableSet).
By entering the tables referenced in a stored procedure you would like to test, this script will provide not only a list of all the tables that will need data, but it will write most of the code for you. The valueGeneratorScripts can be tailored with WHERE clauses to restrict your data samples, and these same WHERE clauses can be copied to the deleteStatementsForTableSet.
set nocount on;
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- Set these values to affect script execution
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
declare @fkAwareness tinyint = 1, -- 1 = dynamically finds table dependencies and adds tables
@includeDeleteStatement tinyint = 1; -- 1 = includes conditional delete statements for table set, requires @fkAwareness = 1
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
declare @tableListSubmitted table (
schemaQualifiedObjectNamenvarchar(128)null
);
insert @tableListSubmitted ( schemaQualifiedObjectName )
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- Add all the tables for which you'd like to generate scripts as ( 'schemaName.tableName' )
-- or ( '[schemaName].[tableName]' )
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
values('RVW.eddsUser')
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Here on down is the guts of the code. If you just want to generate scripts, no
-- need to read further.
--========================================================================
declare@objectIdint,
@columnIdint,
@quotedSchemaQualifiedNamenvarchar(261), -- two sysnames, four brackets, and a period
@hasIdentityFlag tinyint,
@hasFkFlag tinyint,
@addedForFkFlag tinyint,
@dependencyGroupCounter int, -- used to count dependency depth
@quotedColumnNamenvarchar(128), -- including to prevent need for lookups in nested cursor
@userTypeIdint,
@userTypeNamenvarchar(128), -- including to prevent need for lookups in nested cursor
@isNullablebit,
@columnNumberint,
@lineEndnchar(2)= CHAR(13) + CHAR(10),
@tabnchar(1)= CHAR(9),
@commaDelimiternchar(7)= N'+ N'', ''',
@nFrontQuotenchar(9)= N'N''N'''''' + ', -- for leading unicode string columns
@frontQuotenchar(8)= N'N'''''''' + ', -- for leading ASCII and hexadecimal string columns
@endQuotenchar(8)= N' + N''''''''', -- for closing string columns
@valuePlaceholdernchar(12)= N'{columnPlaceholder}', -- supports REPLACE statements
@frontWrappernvarchar(max), -- beginning of INSERT statement
@backWrappernvarchar(max), -- end of INSERT statement
@fkListDisplay nvarchar(max), -- holds list of FK's on table with column info
@workingColumnnvarchar(max), -- space to build individual column statement
@valueGeneratorStatementnvarchar(max), -- script to generate VALUE elements
@presentationStatementnvarchar(max);
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Assemble and order table list
--=======================================================================
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
declare @tableListPresented table (
quotedSchemaQualifiedNamenvarchar(261)null,
objectIdintnull,
hasFkFlag tinyint null,
referencedFlag tinyint null,
addedForFkFlag tinyint null,
dependencyGroup tinyint null -- this represents the set of tables at a particular depth in the dependency chain, 0 would have no FK's
);
insert @tableListPresented (
quotedSchemaQualifiedName,
objectId,
addedForFkFlag
)
select QUOTENAME(SCHEMA_NAME([schema_id]), '[')
+ N'.'
+ QUOTENAME(name, '[') as quotedSchemaQualifiedName,
[object_id] as objectId,
0 as addedForFkFlag -- submitted objects were not added for FK support
from @tableListSubmitted
inner join
-- This OBJECT_ID() call insures we only bring through tables
sys.objects on objects.[object_id] = OBJECT_ID(schemaQualifiedObjectName, 'U');
if @fkAwareness = 1
begin;
-- Determine if tables have any foreign keys. If so, make sure the referenced tables are
-- added to the list, and do so recursively
while exists ( select 1
from @tableListPresented
inner join
sys.foreign_keys on foreign_keys.parent_object_id = objectId
where not exists ( select 1 from @tableListPresented where objectId = foreign_keys.referenced_object_id )
)
begin;
insert @tableListPresented (
quotedSchemaQualifiedName,
objectId,
referencedFlag,
addedForFkFlag
)
-- We need to use SELECT DISTINCT to avoid adding the same table twice if another table has more
-- than one foreign key referencing it.
select distinct QUOTENAME(OBJECT_SCHEMA_NAME(foreign_keys.referenced_object_id), '[')
+ N'.'
+ QUOTENAME(OBJECT_NAME(foreign_keys.referenced_object_id), '[') as schemaQualifiedObjectName,
foreign_keys.referenced_object_id,
1 as referencedFlag,
1 as addedForFkFlag
from sys.foreign_keys
inner join
@tableListPresented on objectId = foreign_keys.parent_object_id
where not exists ( select 1 from @tableListPresented where objectId = foreign_keys.referenced_object_id );
-- Fill out missing metadata
if exists ( select 1 from @tableListPresented where hasFkFlag is null )
begin;
-- Mark all the tables with FK's
update @tableListPresented
set hasFkFlag = 1
where exists ( select 1 from sys.foreign_keys where foreign_keys.parent_object_id = objectId )
and
hasFkFlag is null;
-- By definition, every record left with NULL does not have an FK
update @tableListPresented
set hasFkFlag = 0,
dependencyGroup = 0 -- Tables without dependencies are in dependency group 0
where hasFkFlag is null;
end;
if exists ( select 1 from @tableListPresented where referencedFlag is null )
begin;
-- Mark all the referenced tables
update @tableListPresented
set referencedFlag = 1
where exists ( select 1
from sys.foreign_keys
where foreign_keys.referenced_object_id = objectId
and
foreign_keys.parent_object_id in ( select objectId from @tableListPresented )
)
and
referencedFlag is null;
-- By definition, every record left with NULL is not referenced
update @tableListPresented
set referencedFlag = 0
where referencedFlag is null;
end;
end;
-- Initialize dependency group counter and determine dependency groups
set @dependencyGroupCounter = 0;
while exists ( select 1 from @tableListPresented where dependencyGroup is null )
begin;
-- Increment dependency group
set @dependencyGroupCounter = @dependencyGroupCounter + 1;
-- Identify group membership
update @tableListPresented
set dependencyGroup = @dependencyGroupCounter
from @tableListPresented
where dependencyGroup is null
and
(
not exists (
select referenced_object_id
from sys.foreign_keys
where foreign_keys.parent_object_id = objectId
except
select objectId
from @tableListPresented
where dependencyGroup is not null
)
or
-- If a table only has a self-referencing FK, it will never trigger the above condition.
-- However, we also need to make sure other references are satisfied before the self-
-- reference is taken into consideration.
(
exists (
select 1
from sys.foreign_keys
where foreign_keys.parent_object_id = foreign_keys.referenced_object_id
and
foreign_keys.parent_object_id = objectId
)
and
not exists (
select foreign_keys.referenced_object_id
from sys.foreign_keys
where foreign_keys.parent_object_id = objectId
and
foreign_keys.referenced_object_id <> objectId
except
select objectId
from @tableListPresented
where dependencyGroup is not null
)
)
);
end;
if @includeDeleteStatement = 1
begin;
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Present ordered conditional deletes for table set
--=======================================================================
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
select N'--!!! --==THIS SCRIPT MUST BE ALTERED TO DO ANYTHING!==-- !!!'
+ @lineEnd
+ N'--!!! This script was generated by code with the intent of being !!!'
+ @lineEnd
+ N'--!!! altered to specify the data to be deleted and used in a test !!!'
+ @lineEnd
+ N'--!!! environment. Any data damage or loss caused by this script or !!!'
+ @lineEnd
+ N'--!!! its execution is the responsibility of the executor. !!!'
+ @lineEnd
+ REPLACE(
STUFF(
(
select @lineEnd +
REPLACE(deleteBlock.deleteBlock, N'{objectName}', quotedSchemaQualifiedName)
from @tableListPresented
cross apply (
select N'if exists ( select 1 from {objectName} where 0 = 1 )'
+ @lineEnd
+ N'begin;'
+ @lineEnd
+ @tab
+ N'delete {objectName} where 0 = 1;'
+ @lineEnd
+ N'end;'
+ @lineEnd as deleteBlock
) as deleteBlock
order by dependencyGroup desc,
quotedSchemaQualifiedName desc
for xml path ('')
), 1, 7, N'' -- end of STUFF
), N'
', N'' -- replaces extra line end characters added by FOR XML
) as deleteStatementsForTableSet;
end;
end;
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Construct and present statements for each table
--=======================================================================
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
declare tableCursor cursor
for select objectId,
quotedSchemaQualifiedName,
ISNULL(hasFkFlag, 0),
ISNULL(addedForFkFlag, 0)
from @tableListPresented
where objectId is not null
order by dependencyGroup,
quotedSchemaQualifiedName;
open tableCursor;
fetch next from tableCursor
into @objectId, @quotedSchemaQualifiedName, @hasFkFlag, @addedForFkFlag;
while @@FETCH_STATUS = 0
begin;
-- Initialize the wrapper values and identity flag to avoid old values persisting
-- across iterations and avoid NULL concatenation issues.
select @frontWrapper = N'',
@backWrapper = N'',
@fkListDisplay = N'',
@workingColumn = N'',
@valueGeneratorStatement = N'',
@presentationStatement = N'',
@hasIdentityFlag = 0;
-- determine if table has identity value
if exists (select 1 from sys.identity_columns where [object_id] = @objectId)
begin;
select @hasIdentityFlag = 1;
end;
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Build front end wrapper
--=======================================================================
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- If table was added to support foreign key relationships for submitted tables, make note
if @addedForFkFlag = 1
begin;
set @frontWrapper = N'-- Table was automatically added to support FK relationships'
+ @lineEnd;
end;
-- Begin standard script header
set@frontWrapper =@frontWrapper
+ N'--============================================================================'
+ @lineEnd
+ N'--||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||'
+ @lineEnd
+ N'--============================================================================'
+ @lineEnd
+ N'-- '
+ @quotedSchemaQualifiedName
+ N' test data'
+ @lineEnd;
-- If table has any foreign keys, list them out
if @hasFkFlag = 1
begin;
select @frontWrapper = @frontWrapper
+ REPLACE(
STUFF(
(
select @lineEnd
+ N'--'
+ @tab
+ N'Column(s) ( '
+ parentColumns.parentColumnList
+ N' ) has FK reference to '
+ QUOTENAME(OBJECT_SCHEMA_NAME(foreign_keys.referenced_object_id), '[')
+ N'.'
+ QUOTENAME(OBJECT_NAME(foreign_keys.referenced_object_id), '[')
+ N' ( '
+ referencedColumns.referencedColumnList
+ N' ) - '
+ QUOTENAME(OBJECT_NAME(foreign_keys.[object_id]), '[')
from sys.foreign_keys
cross apply (
select STUFF( -- Getting parent columns names using standard FOR XML comma-delimited list pattern
(
selectN', ' +
QUOTENAME(columns.name,'[')
fromsys.foreign_key_columns
inner join
sys.columns on columns.column_id = foreign_key_columns.parent_column_id
and
columns.[object_id] = foreign_key_columns.parent_object_id
where foreign_key_columns.constraint_object_id = foreign_keys.[object_id]
order byforeign_key_columns.constraint_column_id
for xml path('')
), 1, 2, ''
) as parentColumnList
) as parentColumns
cross apply (
select STUFF( -- Getting referenced columns names using standard FOR XML comma-delimited list pattern
(
selectN', ' +
QUOTENAME(columns.name,'[')
fromsys.foreign_key_columns
inner join
sys.columns on columns.column_id = foreign_key_columns.referenced_column_id
and
columns.[object_id] = foreign_key_columns.referenced_object_id
where foreign_key_columns.constraint_object_id = foreign_keys.[object_id]
order byforeign_key_columns.constraint_column_id
for xml path('')
), 1, 2, ''
) as referencedColumnList
) as referencedColumns
where foreign_keys.parent_object_id = @objectId
order by foreign_keys.parent_object_id,
foreign_keys.[object_id]
for xml path('')
), 1, 0, N'' -- end of STUFF, nothing is stuffed
), N'
', N'' -- replaces extra line end characters added by FOR XML
);
-- Add a final line end
set @frontWrapper = @frontWrapper
+ @lineEnd;
end;
-- add bottom bumper
select@frontWrapper =@frontWrapper
+ N'--============================================================================'
+ @lineEnd
+ N'--||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||'
+ @lineEnd
+ N'--============================================================================'
+ @lineEnd;
-- set IDENTITY_INSERT, if necessary
if @hasIdentityFlag = 1
begin;
select@frontWrapper =@frontWrapper
+ N'set IDENTITY_INSERT '
+ @quotedSchemaQualifiedName
+ N' on;'
+ @lineEnd;
end;
-- basic INSERT beginning
select@frontWrapper =@frontWrapper
+ N'insert '
+ @quotedSchemaQualifiedName
+ N' ('
-- add comma-delimited, quoted column list
select@frontWrapper =@frontWrapper
+ STUFF(
(
select', ' +
QUOTENAME(columns.name,'[')
fromsys.columns
where[object_id] = @objectId
order bycolumns.column_id
for xml path('')
), 1, 2, ''
);
-- basic INSERT ending
select@frontWrapper =@frontWrapper
+ N')'
+ @lineEnd
+ N'VALUES'
+ @lineEnd;
-- present front wrapper
select@presentationStatement =N'select N'''
+ REPLACE(@frontWrapper, N'''', N'''''')
+ N''' as '
+ SCHEMA_NAME([schema_id])
+ N'_'
+ name
+ N'_frontInsertWrapper'
fromsys.objects
where[object_id] = @objectId;
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Build back end wrapper
--=======================================================================
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- terminate INSERT statement
select@backWrapper =N';'
+ @lineEnd;
-- set IDENTITY_INSERT, if necessary
if @hasIdentityFlag = 1
begin;
select@backWrapper =@backWrapper
+ N'set IDENTITY_INSERT '
+ @quotedSchemaQualifiedName
+ N' off;'
+ @lineEnd;
end;
-- terminate batch to help keep transaction size smaller
select @backWrapper =@backWrapper
+ N'go'
+ @lineEnd;
-- present back wrapper
select@presentationStatement =@presentationStatement
+ N', N'''
+ REPLACE(@backWrapper, N'''', N'''''')
+ N''' as '
+ SCHEMA_NAME([schema_id])
+ N'_'
+ name
+ N'_backInsertWrapper'
fromsys.objects
where[object_id] = @objectId;
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Build value generator
--=======================================================================
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- All value generators start the same:
set@valueGeneratorStatement =N'--============================================================================'
+ @lineEnd
+ N'--||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||'
+ @lineEnd
+ N'--============================================================================'
+ @lineEnd
+ N'-- '
+ @quotedSchemaQualifiedName
+ N' data scripting'
+ @lineEnd
+ N'--============================================================================'
+ @lineEnd
+ N'--||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||'
+ @lineEnd
+ N'--============================================================================'
+ @lineEnd
+ N'select'
+ @tab
+ N'N''( '''
+ @lineEnd;
-- In order to build out the value generator, we need to step through the
-- columns in the table. There's probably a set-based way to do it, but I
-- haven't quite figured it out.
declare columnCursor cursor
for selectcolumns.column_id,
QUOTENAME(columns.name, '['),
columns.user_type_id,
types.name,
columns.is_nullable,
ROW_NUMBER() over (order by columns.column_id)
fromsys.columns
inner join
sys.types on types.user_type_id = columns.user_type_id
wherecolumns.[object_id] = @objectId
order bycolumns.column_id;
open columnCursor;
fetch next from columnCursor
into @columnId, @quotedColumnName, @userTypeId, @userTypeName, @isNullable, @columnNumber;
while @@FETCH_STATUS = 0
begin;
-- If this isn't the first column, add a delimiting comma
if @columnNumber > 1
begin;
set @valueGeneratorStatement =@valueGeneratorStatement
+ @tab
+ @tab
+ @commaDelimiter
+ @lineEnd;
end;
-- If the column is nullable, use the appropriate wrapper. Otherwise,
-- just use the placeholder.
if @isNullable = 1
begin;
-- NULL handling wrapper
set@workingColumn =@tab
+ @tab
+ N'+ case'
+ @lineEnd
+ @tab
+ @tab
+ @tab
+ N'when '
+ @quotedColumnName
+ N' is not null'
+ @lineEnd
+ @tab
+ @tab
+ @tab
+ @tab
+ N' then N''/*'
+ @quotedColumnName
+ N'=*/'' + '
+ @valuePlaceholder
+ @lineEnd
+ @tab
+ @tab
+ @tab
+ N'else N''/*'
+ @quotedColumnName
+ N'=*/NULL'''
+ @lineEnd
+ @tab
+ @tab
+ N' end'
+ @lineEnd;
end;
else begin;
-- non-NULL direct value
set @workingColumn =@tab
+ @tab
+ N'+ N''/*'
+ @quotedColumnName
+ N'=*/'' + '
+ @valuePlaceholder
+ @lineEnd;
end;
-- Replace @valuePlaceholder with the appropriate value transformation
-- Not all transformations are figured out yet...
-- All string values are cast to NVARCHAR (rather than VARCHAR for ASCII strings)
-- because the overall string that we are building is a unicode string. The leading
-- quote on the value will appropriately type the value when the generated script
-- is run.
set@workingColumn =case @userTypeId
when 34 -- image, presented as hexadecimal string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'CAST(' + @quotedColumnName + N'as nvarchar(max))' + @endQuote))
when 35 -- text, presented as hexadecimal string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'CAST(' + @quotedColumnName + N'as nvarchar(max))' + @endQuote))
when 36 -- uniqueidentifier, presented as hexadecimal string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'CAST(' + @quotedColumnName + N' as nvarchar(36))' + @endQuote))
when 40 -- date, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'N''CAST('' + ' + @frontQuote + N'CAST(' + @quotedColumnName + N' as nvarchar(10))' + @endQuote + N' + N'' AS date)'''))
when 41 -- time, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'N''CAST('' + ' + @frontQuote + N'CAST(' + @quotedColumnName + N' as nvarchar(8))' + @endQuote + N' + N'' AS time)'''))
when 42 -- datetime2, presented as ASCII string in ODBC Canonical style (may truncate long values?)
then REPLACE(@workingColumn, @valuePlaceholder, (N'N''CAST('' + ' + @frontQuote + N' + CONVERT(nvarchar(27), ' + @quotedColumnName + N', 121)' + @endQuote + N' + N'' AS datetime2)'''))
when 43 -- datetimeoffset, presented as ASCII string in ISO 8601 style
then REPLACE(@workingColumn, @valuePlaceholder, (N'N''CAST('' + ' + @frontQuote + N' + CONVERT(nvarchar(34), ' + @quotedColumnName + N', 121)' + @endQuote + N' + N'' AS datetimeoffset)'''))
when 48 -- tinyint, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(3))'))
when 48 -- signed smallint, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(6))'))
when 56 -- signed int, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(11))'))
when 58 -- smalldatetime, presented as ASCII string in ODBC Canonical (no fractional seconds) style
then REPLACE(@workingColumn, @valuePlaceholder, (N'N''CAST('' + ' + @frontQuote + N'CONVERT(nvarchar(19), ' + @quotedColumnName + N', 120)' + @endQuote + N' + N'' smalldatetime)'''))
when 59 -- real, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'STR(' + @quotedColumnName + N', 56, 16)'))
when 60 -- money, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(25))'))
when 61 -- datetime, presented as ASCII string in ODBC Canonical style
then REPLACE(@workingColumn, @valuePlaceholder, (N'N''CAST('' + ' + @frontQuote + N'CONVERT(nvarchar(23), ' + @quotedColumnName + N', 121)' + @endQuote + N' + N'' AS datetime)'''))
when 62 -- float, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'STR(' + @quotedColumnName + N', 73, 16)'))
--when 98 -- sql_variant
when 99 -- ntext, presented as hexadecimal string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'CAST(' + @quotedColumnName + N'as nvarchar(max))' + @endQuote))
when 104 -- bit, presented as ASCII character
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nchar(1))'))
when 106 -- decimal, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(39))'))
when 108 -- numeric, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(39))'))
when 122 -- smallmoney, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(13))'))
when 127 -- signed bigint, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (N'CAST(' + @quotedColumnName + N' as nvarchar(20))'))
--when 128 -- hierarchyid
--when 129 -- geometry
--when 130 -- geography
when 165 -- varbinary, presented as hexadecimal string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'CAST(' + @quotedColumnName + N'as nvarchar(max))' + @endQuote))
when 167 -- varchar, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'REPLACE(' + @quotedColumnName + N', N'''''''', N'''''''''''')' + @endQuote))
when 173 -- binary, presented as hexadecimal string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'CAST(' + @quotedColumnName + N'as nvarchar(max))' + @endQuote))
when 175 -- char, presented as ASCII string
then REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'REPLACE(' + @quotedColumnName + N', N'''''''', N'''''''''''')' + @endQuote))
--when 189 -- timestamp
when 231 -- nvarchar, presented as unicode string
then REPLACE(@workingColumn, @valuePlaceholder, (@nFrontQuote + N'REPLACE(' + @quotedColumnName + N', N'''''''', N'''''''''''')' + @endQuote))
when 239 -- nchar, presented as unicode string
then REPLACE(@workingColumn, @valuePlaceholder, (@nFrontQuote + N'REPLACE(' + @quotedColumnName + N', N'''''''', N'''''''''''')' + @endQuote))
--when 241 -- xml
when 256 -- sysname, presented as unicode string
then REPLACE(@workingColumn, @valuePlaceholder, (@nFrontQuote + N'REPLACE(' + @quotedColumnName + N', N'''''''', N'''''''''''')' + @endQuote))
-- If we don't know the type or have a specific handler, toss it in a unicode string and hope for the best
else REPLACE(@workingColumn, @valuePlaceholder, (@frontQuote + N'REPLACE(' + N'CAST(' + @quotedColumnName + N'as nvarchar(max))' + N', N'''''''', N'''''''''''''''')' + @endQuote))
end;
-- Update the value generator statement
set@valueGeneratorStatement =@valueGeneratorStatement
+ @workingColumn;
-- Proceed to the next column
fetch next from columnCursor
into @columnId, @quotedColumnName, @userTypeId, @userTypeName, @isNullable, @columnNumber;
end;
close columnCursor;
deallocate columnCursor;
-- Add FROM clause to value generator
set@valueGeneratorStatement =@valueGeneratorStatement
+ @tab
+ @tab
+ N' + N'' ),'''
+ @lineEnd
+ @tab
+ @tab
+ N'as '
+ QUOTENAME(@quotedSchemaQualifiedName, '[')
+ @lineEnd
+ N'from'
+ @tab
+ @quotedSchemaQualifiedName
+ N';'
+ @lineEnd;
-- present value generator script
select@presentationStatement =@presentationStatement
+ N', N'''
+ REPLACE(@valueGeneratorStatement, N'''', N'''''')
+ N''' as '
+ SCHEMA_NAME([schema_id])
+ N'_'
+ name
+ N'_valueGeneratorScript'
fromsys.objects
where[object_id] = @objectId;
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--=======================================================================
-- Present scripts
--=======================================================================
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- Terminate presentation statement
select@presentationStatement =@presentationStatement
+ N';';
begin try
exec (@presentationStatement);
end try
begin catch
selectN'ERROR attempting to present scripts. Script value dump:'as ERROR,
LEN(@presentationStatement)as presentationStatementLength,
@presentationStatementas presentationStatement,
LEN(@frontWrapper)as frontWrapperLength,
@frontWrapperas frontWrapper,
LEN(@backWrapper)as backWrapperLength,
@backWrapperas backWrapper,
LEN(@valueGeneratorStatement)as valueGeneratorStatementLength,
@valueGeneratorStatementas valueGeneratorStatement,
N'Error Info:'as [ERROR INFO],
ERROR_MESSAGE()as errorMessage,
ERROR_LINE()as errorLine,
ERROR_NUMBER()as errorNumber,
ERROR_STATE()as errorState,
ERROR_SEVERITY()as errorSeverity;
end catch;
fetch next from tableCursor
into @objectId, @quotedSchemaQualifiedName, @hasFkFlag, @addedForFkFlag;
end;
close tableCursor;
deallocate tableCursor; 
