There are multiple ways to automate the generation of table-creation script based on existing tables in SQL server. The common way is to use the ‘generate script’ function to automate this process. This GUI method is relatively easy but it often produces a lot extra command which may not fulfill your expectation. Other methods include recursive CTE and cursor. In this blog, I am going to show you how to automate this process with FOR XML clause with path mode. If you are not familiar with this method, here is a nice blog you can check.
To create a table, we need to know the table name, schema name, column name, column data type. Fortunately, we can get all this information from two views: INFORMATION_SCHEMA.COLUMNS and INFORMATION_SCHEMA.TABLS.
Furthermore, we need to know the length for a string data type like varchar and the precision and scale number for a decimal data type. In contrast, we don’t need to define the length for a integer data type and a datetime data type. This information is also available in INFORMATION_SCHEMA.COLUMNS. We can use a CASE clause to decide whether we need to add those information. Similarly, we can use a CASE clause for nullable or non-nullable columns. Finally, we need to concatenate all columns information together with the FOR XML clause.
For the detailed query, please check the attached screenshot. You can also find a similar snippet from my github.
This snippet can be useful when you need to write table-creation script with limited length. For example, it can be used for modifying a Azure Data Factory pipeline JSON file.