Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Florian Reischl,
A script to script all data of a table. The gains of this script are:
Open the script on the database to script data from.
Due to the fact that this script works without any cursors for data collection it is by design much faster than many other.
DATETIME, DATE and TIME columns will not be scripted as strings but as binary data which are much faster handled by SQL Server.
Most scripts to export data into SQL do not support data from type VARBINARY and IMAGE or cut them. Data with less than 3998 bytes can be scripted without any other requirements. If you want to script really huge binary data just install the plain TSQL user defined function udf_varbintohexstr_big which is also available here. Now you can script any size of binary data.
Supported data-types are:
Because the first step of the script just creates another script which will script the data you are able to specify any WHERE/JOIN criteria for the data to be scripted if not the complete table needs to be.
It is also possible to remove specific columns from the generated script.
All character data will be masked to avoid SQL injection. All column names will be quoted to ensure a valid SQL output.
To be able to script huge VARBINARY or IMAGE data you need the udf_varbintohexstr_big which is published here. The function is plain TSQL.
Here the known issues of the script.
Sometimes a gain becomes an issue... It is only possible to script large IMAGE data with the udf_varbintohexstr_big so need the rights to install a user defined function on the source server. The destination server does not need this function.
The SQL Server Management Studio (SSMS) restricts the output for each cell to 40kb. The script can script any size of data but the data may be cutted by SSMS. To script any size of data just use a small program, perl/php/ps1 script to execute the script and write directly into a file.
Currently not supported data-types are:
A brief look at the Varbinary data type and its uses in SQL Server for beginners.
URGENT: Error executing scripts......
(fn_diagramobjects has the execution context ExecuteAsUser. ExecuteAsUser is not supported)
Connditional executing of script