eitanblumin / sp_generatetableddlscript

This procedure can be used to generate a CREATE TABLE script for a given table in SQL Server

Hi there 👋

My name is Eitan Blumin (pronouns: He/Him)

I'm a Senior SQL Server Consultant and Team Leader at Madeira Data Solutions.

Check out our GitHub organization at github.com/MadeiraData.

Use an RSS reader to subscribe to my GitHub Gists feed, and be notified about new gists before anyone:

https://gist.github.com/EitanBlumin.atom

Check out my blog here: https://www.eitanblumin.com

You can also find me on Twitter: https://twitter.com/EitanBlumin

And on Facebook: https://www.facebook.com/EitanBluminBlog

And a few other social networks 😉

sp_generatetableddlscript's Issues

add support for "Not for replication" property

add support for in-memory tables

add support for Filestream columns

add support for non-default filegroups

also add optional parameter to set whether to include filegroups and partition schemas in the script

Max length of TEXT data type is returned incorrectly

The max length value is taken from INFORMATION_SCHEMA, where the character max length is incorrect for the data type.

discovered by Nathan Lifshes

INCLUDE index columns are being generated as covering index columns

Hello,

If the origin table has an index with INCLUDE columns, the output script has those columns as covering columns.

Origin

CREATE INDEX idx1 ON MyTable (Col1) INCLUDE (Col2, Col3)
Output
CREATE INDEX idx1 ON MyTable (Col1, Col2, Col3)

add support for XML document collections

add support for column sets

add optional parameter to copy identity seed

Optionally use CHECKIDENT...RESEED or inject into the IDENTITY property the current seed value of the existing table

add support for Rule objects

add support for collations different from Database Default

Available alternatives

The following resources provide alternative solutions for implementing the same (or similar) use case, and can be used for further research/inspiration:

• https://www.sqlservercentral.com/articles/create-table-ddl-via-tsql-1
• https://www.techrepublic.com/blog/the-enterprise-cloud/script-table-definitions-using-tsql/
• https://www.mssqltips.com/sqlservertip/5699/auto-generate-create-table-script-based-on-sql-server-query/
• https://www.c-sharpcorner.com/UploadFile/67b45a/how-to-generate-a-create-table-script-for-an-existing-table/

add support for Sparse columns