Script to Generate CREATE TABLE Definitions

Once the target environment is installed and configured, a database migration cannot commence before the database and the table structures that will hold the new data are in place. Creating table definitions can be performed using various methods, a couple of which are tools provided with SQL Server, and third-party tools. Another method is to recreate the tables from the scripts in the projects’ code library, if available that is. The approach I used was to generate the CREATE TABLE statements using T-SQL. Due to the use of the nvarchar(max) data type, the script will only work with SQL Server 2005 and later. In order to migrate database from earlier versions, what I did was restore a recent backup to an SQL Server 2005 instance, then execute this script to extract the object definitions. The script is quite self-explanatory. The Information Schema Views TABLES and COLUMNS views are being used to retrieve the main object structure, while the sys.identity_columns, sys.indexes, sys.data_spaces system views are used to retrieve which columns have the IDENTITY property set, and on which the FILEGROUP table is created. You will also notice that a Common Table Expression (CTE) was used to avoid iterating (or using a CURSOR) to retrieve information for the tables, columns, etc. When executed against a database the output will show something similar to the below sample extracted from the AdventureWorks database:

--------------------------------------------------
USE [AdventureWorks]
GO

--------------------------------------------------
CREATE TABLE [dbo].[AWBuildVersion] (
    [SystemInformationID] [tinyint] IDENTITY (1,1) NOT NULL,
    [Database Version] [nvarchar](25) COLLATE Latin1_General_CI_AS NOT NULL,
    [VersionDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[DatabaseLog] (
    [DatabaseLogID] [int] IDENTITY (1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DatabaseUser] [nvarchar](128) COLLATE Latin1_General_CI_AS NOT NULL,
    [Event] [nvarchar](128) COLLATE Latin1_General_CI_AS NOT NULL,
    [Schema] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
    [Object] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
    [TSQL] [nvarchar](MAX) COLLATE Latin1_General_CI_AS NOT NULL,
    [XmlEvent] [xml] NOT NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[ErrorLog] (
    [ErrorLogID] [int] IDENTITY (1,1) NOT NULL,
    [ErrorTime] [datetime] NOT NULL,
    [UserName] [nvarchar](128) COLLATE Latin1_General_CI_AS NOT NULL,
    [ErrorNumber] [int] NOT NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorState] [int] NULL,
    [ErrorProcedure] [nvarchar](126) COLLATE Latin1_General_CI_AS NULL,
    [ErrorLine] [int] NULL,
    [ErrorMessage] [nvarchar](4000) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY];

CREATE TABLE [dbo].[Usernames] (
    [ServerName] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
    [Username] [varchar](10) COLLATE Latin1_General_CI_AS NULL,
    [Password] [varbinary](max) NULL
) ON [PRIMARY];

CREATE TABLE [HumanResources].[Department] (
    [DepartmentID] [smallint] IDENTITY (1,1) NOT NULL,
    [Name] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [GroupName] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY];

When executing this script using SQL Server Management Studio you’ll have to set the output to redirect to test, and also change the option to display the maximum of 8192 characters. A copy of the script can be downloaded here.