SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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)

SQL Server procedure to script tables

By Gerrit Mantel,

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:
https://blogs.msdn.microsoft.com/buckwoody/2009/07/02/powershell-and-sql-server-script-all-tables/

Documentation of the Microsoft.SqlServer.Management.Smo ScriptingOptions Class
https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions.aspx

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 filename 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

Total article views: 307 | Views in the last 30 days: 307
 
Related Articles
SCRIPT

Generate Drop / Create Script for views, stored Procedures

This Script generates drop and create scripts for views or stored procedures.

FORUM

script to create indexes existing in a database

script to create indexes existing in a database

SCRIPT

Database Snapshot Create/Drop/Restore SQL Script.

Database Snapshot Creat/Drop/Restore SQL Script for Single or multiple data files of databases.

ARTICLE

Creating a Script from a Stored Procedure

Ryan demonstrates how he arrived at a solution that allows you to create scripts from a stored proce...

BLOG

A Script A Day - Day 6 - Drop and Create Database Snapshots

Today's Script will drop all database snapshots and create a database snapshot for all online read w...

Tags
create table    
 
Contribute