Generating Create Table statements without using the Right-Mouse Button – crazy application of the string_agg function (for no reason)

Your manager walks in and says to you, I need you to create a shell of all tables that reside in our Microsoft SQL Server by lunchtime.

But there is one stipulation, you aren’t allowed to use the right mouse button.

OR rather specifically you aren’t allowed to use Right Click, Create Table as… prompt.

Pretty tough assignment?

Well, it can be done with 4 lines of code.

We are going to generate the CREATE TABLE statements for all of the base tables in the Sales schema of the Sales schema of the Adventure Works database.

Here we go…

The information relating to the table’s properties can be found in information schema.

It looks something like this:

Again, a nifty use of the STRING_AGG function is going to be used.

We are going to roll up the columns concatenated with the datatype with the string function and we are going to aggregate it by the Table_Name.

If this method was going to be used in a production restore, the concatenation of the string_agg function would need to be more sophisticated.

But it’s 3am in the morning here, so we’re going with this.

The left() statement in the where clause is just to eliminate the views (this is database specific nomenclature – you can call your views whatever you like – as long as it’s not ‘Too long to compile').

The code snippet is here:

SELECT CONCAT('CREATE TABLE ',TABLE_NAME,' (',STRING_AGG(CONCAT(COLUMN_NAME, ' ',DATA_TYPE),', '),')')

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_SCHEMA = 'Sales'AND LEFT(TABLE_NAME,1) != 'v'

GROUP BY TABLE_NAME

The result set is here:

Select two at random, we will see that both statements have generated the requisite code.

CREATE TABLE PersonCreditCard (BusinessEntityID int, CreditCardID int, ModifiedDate datetime)

CREATE TABLE SalesReason (SalesReasonID int, Name nvarchar, ReasonType nvarchar, ModifiedDate datetime)