This script creates 5 tables in your SQL-Server 2008 Database using all the supported datatypes. These tables are intended as basis to test whether scripts handle all possible data types.


Generate insert statement from a SQL-Server Table (Tables provided by Test_Data.sql)

exec spgeninsert string_vals
exec spgeninsert string_vals, O
exec spgeninsert string_vals, G
exec spgeninsert string_vals, V, STR
exec spgeninsert string_vals, V, STR, '2'
exec spgeninsert string_vals, V, STR, 'char'
exec spgeninsert int_vals
exec spgeninsert int_vals, V, '', '', 'id =1'


The purpose of this procedure is to generate scripts, that can reinsert the contents of text columns into a table of your database.

When the datalength becomes greater than 4000 Query-Analyzer and SSMS refuse to show the full content. But it is possible to create Update statements showing the whole content. Well currently I only handle the case, when the content is a long multiline text who's linesize can be handled by the tool. I.e. most XML works fine.

-- generate update statement for SQL-Server
exec spgentextupdate 'stringvals', id, 'text'

-- generate statement only for row with id = 1
exec spgentextupdate 'stringvals', id, 'text', 'id = 1'

-- generate update statement for Oracle
exec spgentextupdate 'stringvals', id, 'text', '', 'O'


This is used to get some Information about the columns of a table or a view.
It even shows the actual max datalength of varchar and text columns and their sibblings nvarchar and ntext.

Last edited Nov 3, 2010 at 6:26 AM by Bernd_K, version 8


No comments yet.