T-SQL: Create Table DDL Script

A T-SQL script to export the definition of a table may be used in an automation. For example when I automate an import of a data into the database, I don’t create a staging table with NVARCHAR(MAX) columns, but export the structure of the destination table as a staging table.

Create database.

Script to export table.

The preliminry steps check the variables @Source and @Destination:

  • @Source – blank source table and not existing source table
  • @Destination – blank destination table

Based on @SourceSchemaName and @SourceTableName I pick @ObjectID and use it as an identifier of the scripted table.

Next I delete a destination table if it exists. A hint: manipulate the backend processes on a separate server/database/schema.

CREATE TABLE script simply joins the system tables where the information is and wraps the joined result with CASE statements.
I use a Dynamic SQL to point to the exact object (Database, Schema, Table).

Delete Database.

This script will be modified constantly in order to be as accurate as possible. Please be adviced that the responsability for your code is yours.

Any suggestions are welcomed.

Keep it simple :-)