Technical Article

SQL Server procedure to script tables

,

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fun_FolderExist] (
  @dirname VARCHAR(265))
RETURNS INT
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Check if folder exists.
The function returns 0 (folder does not exist), or 1 (folder exists).

EXAMPLES
SELECT dbo.fun_FolderExist('C:\Program Files')
1
SELECT dbo.fun_FolderExist('C:\xxxx')
0

HISTORY
24-nov-2015 - Created by Gerrit Mantel

TAGS
<program>
  <description>Check if folder exists</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2015-11-24</created>
  <lastmodified>2015-11-24</lastmodified>
</program>
*/  DECLARE @fso INT
  DECLARE @hr INT
  DECLARE @ofolder INT
  DECLARE @name VARCHAR(265)
  DECLARE @result INT

  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
  EXEC @hr = sp_OAMethod @fso, 'FolderExists', @ofolder OUT, @dirname;
  EXEC @hr = sp_OADestroy @fso;

  SET @result = @ofolder;
  RETURN @result;
END

GO


CREATE FUNCTION [dbo].[fun_FileExist] (
  @filename VARCHAR(265))
RETURNS INT
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Check if file exists.
The function returns 0 (file does not exist), or 1 (file exists).

EXAMPLES
SELECT dbo.fun_FileExist('C:\oneTouch_drive.cvf')
1
SELECT dbo.fun_FileExist('C:\xxxx.txt')
0

HISTORY
24-nov-2015 - Created by Gerrit Mantel

TAGS
<program>
  <description>Check if file exists</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2015-11-24</created>
  <lastmodified>2015-11-24</lastmodified>
</program>
*/  DECLARE @fso INT
  DECLARE @hr INT
  DECLARE @ofile INT
  DECLARE @result INT

  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
  EXEC @hr = sp_OAMethod @fso, 'FileExists', @ofile OUT, @filename;
  EXEC @hr = sp_OADestroy @fso;

  SET @result = @ofile;
  RETURN @result;
END

GO


CREATE PROCEDURE [dbo].[prc_save_text_to_file] (
  @text VARCHAR(MAX), 
  @filename VARCHAR(265))
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Write text variable to file.

OLE Automation Procedures has to be enabled.
Existing files will be overwritten.
If path does not exist, no error is raised. Check path before with function fun_FolderExist.
If file could not be created, no error is raised. Check file afterwards with function fun_FileExist.
Latin-1 character set is used, 1 byte per character.

Parameters
  @text       VARCHAR(MAX)  = (nonbinary) text
  @filename   VARCHAR(265)  = filespec

Check path

HISTORY
25-jan-2017: Created by Gerrit Mantel
31-jan-2017: Changed by Gerrit Mantel
- Latin-1 character set is used

TAGS
<program>
  <description>Write text variable to file</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2017-01-25</created>
  <lastmodified>2017-01-31</lastmodified>
</program>
*/  DECLARE @objtoken INT

  EXEC sp_OACreate 'ADODB.Stream', @objtoken OUTPUT;
  EXEC sp_OASetProperty @objtoken, 'Type', 2;
  EXEC sp_OASetProperty @objtoken, 'ContentType', 'text/plain'
  EXEC sp_OASetProperty @objtoken, 'Encoding', 'quoted-printable'
  EXEC sp_OASetProperty @objtoken, 'Charset', 'iso-8859-1'
  EXEC sp_OAMethod @objtoken, 'Open';
  EXEC sp_OAMethod @objtoken, 'WriteText', NULL, @text;
  EXEC sp_OAMethod @objtoken, 'SaveToFile', NULL, @filename, 2;
  EXEC sp_OAMethod @objtoken, 'Close';
  EXEC sp_OADestroy @objtoken;
END

GO


CREATE PROCEDURE [dbo].[prc_script_tables_multi] (
  @dbname NVARCHAR(128) = '',
  @path VARCHAR(265) = '',
  @includeifnotexists BIT = 0)
AS
BEGIN
/*
SQLServer Stored Procedure

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.

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:
\ / : * ? " < > |

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

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;

HISTORY
03-oct-2017: Created by Gerrit Mantel

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

TAGS
<program>
  <description>Script all tables for a given database to individual script files on a given path</description>
  <generic>0</generic>
  <author>Gerrit Mantel</author>
  <created>2017-10-03</created>
  <lastmodified>2017-10-03</lastmodified>
</program>
*/  SET NOCOUNT ON

  DECLARE @body NVARCHAR(MAX)
  DECLARE @cmd NVARCHAR(4000)
  DECLARE @dump_folder VARCHAR(265)
  DECLARE @dump_file VARCHAR(265)
  DECLARE @message VARCHAR(512)
  DECLARE @srvname NVARCHAR(128)
  SET @srvname = @@SERVERNAME;

  -- Check @dbname
  SET @dbname = ISNULL( @dbname,'');
  IF @dbname = ''
  BEGIN
    SET @message = 'ERROR - Parameter  @dbname is not specified';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF NOT EXISTS ( SELECT * FROM sys.databases WHERE [name] = @dbname)
  BEGIN
    SET @message = 'ERROR - Database does not exist: ' + @dbname;
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Check @path
  SET @path = ISNULL(@path,'');
  IF @path = ''
  BEGIN
    SET @message = 'ERROR - Parameter @path is not specified';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF RIGHT(@path,1) = '\' SET @path = LEFT(@path,LEN(@path)-1);
  IF dbo.fun_FolderExist(@path) = 0
  BEGIN
    SET @message = 'ERROR - Path does not exist: '+ @path
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  SET @dump_folder = @path + '\' + @dbname + '\TABLE';

  -- Check dump_folder
  IF dbo.fun_FolderExist(@dump_folder) = 0
  BEGIN
    -- Folder does not exist, so make directory
    SET @cmd = N'mkdir "'+@dump_folder+'"'
    EXEC master..xp_cmdshell @cmd, NO_OUTPUT;
  END
  IF dbo.fun_FolderExist(@dump_folder) = 0
  BEGIN
    SET @message = 'ERROR - Folder can not be created: '+ @dump_folder
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  SET @body = N'';
  SET @body = @body + N'param(
[string]$server,
[string]$database,
[string]$path
)
Import-Module sqlps -DisableNameChecking
$s = New-Object Microsoft.SqlServer.Management.Smo.Server("$server")
$d = $s.Databases["$database"]
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($s)
$scripter.Options.ScriptBatchTerminator = $True
$scripter.Options.ToFileOnly = $True
$scripter.Options.Permissions = $True
$scripter.Options.DriAll = $True
$scripter.Options.Triggers = $True
$scripter.Options.ClusteredIndexes = $True
$scripter.Options.Indexes = $True
$scripter.Options.XmlIndexes = $True
$scripter.Options.NoCollation = $True
$scripter.Options.IncludeHeaders = $True
$scripter.Options.IncludeDatabaseContext = $True
$scripter.Options.IncludeIfNotExists = '+ CASE @includeifnotexists WHEN 1 THEN '$True' ELSE '$False' END + '
$scripter.Options.AnsiFile = $True
foreach($t in $d.Tables) {
 $scripter.Options.FileName = "$path\$t.sql"
 $scripter.Script($t)
}
"Done"
';
  
  -- Create Powershell script
  SET @dump_file = @dump_folder+'\tmp.ps1';
  EXEC [dbo].[prc_save_text_to_file] @body, @dump_file;
  IF dbo.fun_FileExist(@dump_file) = 0
  BEGIN
    SET @message = 'ERROR - File can not be created: '+ @dump_file
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Execute Powershell script
  SET @cmd = 'powershell.exe -ExecutionPolicy Unrestricted -command "& '+@dump_folder+'\tmp.ps1 -server '''+@srvname+''' -database '''+@dbname+''' -path '''+@dump_folder+'''"'
  EXEC master..xp_cmdshell @cmd;

  -- Delete Powershell script
  SET @cmd = N'del /F "'+@dump_folder+'\tmp.ps1"';
  EXEC master..xp_cmdshell @cmd, NO_OUTPUT;
END 

GO


CREATE PROCEDURE [dbo].[prc_script_tables_one] (
  @dbname NVARCHAR(128) = '',
  @path VARCHAR(265) = '',
  @includeifnotexists BIT = 0)
AS
BEGIN
/*
SQLServer Stored Procedure

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.

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

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


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;

HISTORY
03-oct-2017: Created by Gerrit Mantel

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

TAGS
<program>
  <description>Script all tables for a given database to one script file on a given path</description>
  <generic>0</generic>
  <author>Gerrit Mantel</author>
  <created>2017-10-03</created>
  <lastmodified>2017-10-03</lastmodified>
</program>
*/  SET NOCOUNT ON

  DECLARE @body VARCHAR(MAX)
  DECLARE @cmd NVARCHAR(4000)
  DECLARE @dump_file VARCHAR(265)
  DECLARE @message VARCHAR(512)
  DECLARE @srvname NVARCHAR(128)
  SET @srvname = @@SERVERNAME;

  -- Check @dbname
  SET @dbname = ISNULL( @dbname,'');
  IF @dbname = ''
  BEGIN
    SET @message = 'ERROR - Parameter  @dbname is not specified';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF NOT EXISTS ( SELECT * FROM sys.databases WHERE [name] = @dbname)
  BEGIN
    SET @message = 'ERROR - Database does not exist: ' + @dbname;
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Check @path
  SET @path = ISNULL(@path,'');
  IF @path = ''
  BEGIN
    SET @message = 'ERROR - Parameter @path is not specified';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  IF RIGHT(@path,1) = '\' SET @path = LEFT(@path,LEN(@path)-1);
  IF dbo.fun_FolderExist(@path) = 0
  BEGIN
    SET @message = 'ERROR - Path does not exist: '+ @path
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  SET @body = '';
  SET @body = @body + 'param(
[string]$server,
[string]$database,
[string]$filename
)
Import-Module sqlps -DisableNameChecking
$s = New-Object Microsoft.SqlServer.Management.Smo.Server("$server")
$d = $s.Databases["$database"]
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($s)
$scripter.Options.ScriptBatchTerminator = $True
$scripter.Options.ToFileOnly = $True
$scripter.Options.Permissions = $True
$scripter.Options.DriAll = $True
$scripter.Options.Triggers = $True
$scripter.Options.ClusteredIndexes = $True
$scripter.Options.Indexes = $True
$scripter.Options.XmlIndexes = $True
$scripter.Options.NoCollation = $True
$scripter.Options.IncludeIfNotExists = '+ CASE @includeifnotexists WHEN 1 THEN '$True' ELSE '$False' END + '
$scripter.Options.FileName = "$filename"
$scripter.Options.AppendToFile = $True
$scripter.Options.AnsiFile = $True
foreach($t in $d.Tables) {
 $scripter.Script($t)
}
"Done"
';
  
  -- Create Powershell script
  SET @dump_file = @path+'\tmp.ps1';
  EXEC [dbo].[prc_save_text_to_file] @body, @dump_file;
  IF dbo.fun_FileExist(@dump_file) = 0
  BEGIN
    SET @message = 'ERROR - File can not be created: '+ @dump_file
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Create script header
  SET @body = 'USE ['+@dbname+']
GO
/****** Objects:  TABLE       Script Date: '+CONVERT(VARCHAR,GETDATE(),121)+' ******/';

  SET @dump_file = @path+'\'+ @dbname+'$TABLE.sql';
  EXEC [dbo].[prc_save_text_to_file] @body, @dump_file;
  IF dbo.fun_FileExist(@dump_file) = 0
  BEGIN
    SET @message = 'ERROR - File can not be created: '+ @dump_file
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Execute Powershell script, append to script header
  SET @cmd = 'powershell.exe -ExecutionPolicy Unrestricted -command "& '+@path+'\tmp.ps1 -server '''+@srvname+''' -database '''+@dbname+''' -filename '''+@dump_file+'''"'
  EXEC master..xp_cmdshell @cmd;

  -- Delete Powershell script
  SET @cmd = N'del /F "'+@path+'\tmp.ps1"';
  EXEC master..xp_cmdshell @cmd, NO_OUTPUT;
END 

GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

External Article

What are Inline Indexes?

New releases of SQL Server arrive at a quick pace, and it's difficult to keep up with the many features introduced in each version. In this article, Phil Factor reviews a feature you may have missed, inline indexes. He covers the syntax and the many ways they can be used and then performs some performance tests to see if they can make a difference with table variables.

2019-03-18

3,669 reads