In the SSMS you can generate CREATE statements per object by using the object context menu, or generate CREATE statements for multiple objects by using the "Generate script" wizard.
To generate CREATE statements of objects by using T-SQL is not natively implemented in the SSMS. However the system table sys.sql_modules contains the CREATE statements of procedures, functions, triggers and views. It's not that hard to derive that code and dump it to files.
I always wanted to script tables too, in a way SSMS can do it for me. I searched in several blogs for a way to script tables. Most of the solutions are scripts that extract definitions from sys tables, but they all had flaws. How does SSMS do it? Well it uses dll's on the background. You can use them in Powershell by yourself too, but I like to keep everything on board of a procedure. So I created two procedures (dbo.prc_script_tables_multi and dbo.prc_script_tables_one) to script tables to files. The first one scripts every table (of a given database) to an individual file, the second one scripts all tables to one file. Both procedures create a temporarily Powershell script, launches it, and will be deleted afterwards. The Powershell script delivers the scriptfile(s) to the given path.
The Powershell script is based on this blog thread:
click here
Documentation of the Microsoft.SqlServer.Management.Smo ScriptingOptions Class
click here
The two main procedures use 2 functions (dbo.fun_FolderExist and dbo.fun_FileExist) and 1 other procedure (dbo.prc_save_text_to_file) all are in the source code. Also OLE Automation and xp_cmdshell has to be enabled on the server.
The current settings produce (in my opinion) a normal CREATE TABLE script. Only the IF NOT EXISTS BEGIN END block addition can be influenced by the parameter @includeifnotexists.
With a little tweaking it is possible to change the procedure to your own wishes.
Remember to always test new procedures on a test environment.
Happy computing!
Gerrit Mantel, LUMC, The Netherlands
Comments for the main procedures
SQL Server procedure dbo.prc_script_tables_multi
Purpose:
Script all tables for a given database to individual script files on a given path.
Parameters:
@dbname NVARCHAR(128), default '', Database name
@path VARCHAR(265), default '', Path to dump scripts to
@includeifnotexist BIT, default False, Include "IF NOT EXIST {table} BEGIN ... END" logics surrounding CREATE TABLE statement.
Notes:
Database must be on current server.
Path to dump files to must exist, and you must have read and write permissions.
The script files will be created on the path: @path\@dbname\TABLE
The filenames are in format [{schema}].[{tablename}].sql
Illegal DOS characters in tablenames will cause the powershell script to fail: \ / : * ? " < > |
Examples:
EXEC dbo.prc_script_tables_multi @dbname='PROG', @path='C:\Data\Prog';
EXEC dbo.prc_script_tables_multi @dbname='PROG', @path='C:\Data\Prog', @includeifnotexists=1;
Used objects:
- dbo.fun_FolderExist (User Defined-Function in local database).
- dbo.fun_FileExist (User Defined-Function in local database).
- dbo.prc_save_text_to_file (Stored procedure in local database)
SQLServer configuration:
- OLE Automation has to be enabled.
- xp_cmdshell has to be enabled
SQL Server procedure dbo.prc_script_tables_one
Purpose:
Script all tables for a given database to one script file on a given path
Parameters:
@dbname NVARCHAR(128), default '', Database name
@path VARCHAR(265), default '', Path to dump scripts to
@includeifnotexist BIT, default False, Include "IF NOT EXIST {table} BEGIN ... END" logics surrounding CREATE TABLE statement.
Notes:
Database must be on current server.
Path to dump file to must exist, and you must have read and write permissions.
The script file that will be created is: @path\@dbname$TABLE.sql
Examples:
EXEC dbo.prc_script_tables_one @dbname='PROG', @path='C:\Data\Prog';
EXEC dbo.prc_script_tables_one @dbname='PROG', @path='C:\Data\Prog', @includeifnotexists=1;
Used objects:
- dbo.fun_FolderExist (User Defined-Function in local database).
- dbo.fun_FileExist (User Defined-Function in local database).
- dbo.prc_save_text_to_file (Stored procedure in local database)
SQLServer configuration:
- OLE Automation has to be enabled.
- xp_cmdshell has to be enabled
More »
