SQL to generate “Create Table” statements for all tables given a schema in MS SQL Server
I needed to import data from my development database to my production one in SQL Server. Usually I’d just use the “import” wizard, but I wanted to retain the table structure (specifically, the Primary Keys and other Indexes don’t come across when you do this).
The solution had to be compatible with MS SQL Server 2016 (some solutions I found used functions that weren’t available until 2017).
Luckily everything was in the same schema, so I was able to create SQL that generated the appropriate CREATE TABLE statement, that I could then run on the destination database server:
The results will look like this:
While you could write SQL to actually run the code in a loop, this way you can first view the tables that would be created. The advantage of this is an additional check in the table structure; specifically I noticed some tables didn’t have a Primary Key.
