sp_getddla and sp_getddl
If you have ever worked with SQL Server, and wanted to script a table, you know it is a GUI process, that behind the scenes, is calling SQL Management Objects(SMO) to create the script for you, which you can then copy and paste.
But what if you want to script the table and put it in a column in some table some place? can it be done via TSQL?
Well the answer may surprise you. Microsoft does not make it possible as a built in feature,
Yuo can google it up yourself: Every other solution out there involves using something OUTSIDE of sql server to do it: Common Language Runtime, PowerShell, xp_cmdshell etc to do the work elsewhere and return some results.
but if you look hard enough, if you dig deep enough, you can find all the elements of the table, or any object in fact, in the meta data tables like sys.tables, sys.columns, and a whole slew of system objects, and actually script it yourself.
Coming up with this solution was not for the faint of heart; it requires a stored procedure that is over a thousand lines of code,thousands of hours over the years in order to pull all the elements together into a single cohesive output. This works on everything 2008 and above. Tested in SQL2008 through SQL2022, as well as Azure SQL Database/Azure Managed Instances(but not as a system proc), AWS RDS SQL Versions as well.
sp_GetDDLa_Latest.txt sp_GetDDL_Latest.txt
I am constantly improving this ten-plus year old procedure with more improvements as I trip over yet one more feature that I find I need to include.
Here is a scripted table as an example of some of this scripts actions:
[dbo].[USZipCodes]The bad news: I don’t take unpaid requests for enhancements. If you suggest something, I’ll think about it, but we all have our own priorities. What I do ask, is that if you make a change and feel it is valuable , send me a copy, so I can consider merging it into my code base.
What it does
- sp_getddla returns multi line results, perfect for copying and pasting
- sp_getddl returns a varchar(max) string, perfect for automation, inserting into a column for example.
- when placed in master and marked as system object, it works in every database.
- Handles CaSe SensITive Collations
- Scripts any Table in the current database
- Scripts any in scope #temp table
- Scripts any Schema
- Scripts any object(procedure, function, view, trigger)
- all tables are fully qualified as [schemaname].[tablename]
- includes a drop if exists…create format. be careful!
- Scripts includes all constraints: default/primary key/unique/foreign keys/check constraints.
- includes the indexes on a table definition
- includes the trigger(s) on the table definition
What it does not do
- Does not script NVARCHAR tables like [データベース](Database in Japanese), because , well, because I don’t NEED it to.
- Does not handle partitioned tables…script currently creates an ugly stub of the table, with maybe one column.
- memory optimized tables
- tables with temporal logging tables (that is a work in progress, partially complete)
Usage Notes and Improvements Over Time
--#################################################################################################
-- Real World DBA Toolkit version 5.08 Lowell Izaguirre lowell@stormrage.com
--#################################################################################################
-- USAGE: exec sp_GetDDLa GMACT
-- or exec sp_GetDDLa 'bob.example'
-- or exec sp_GetDDLa '[schemaname].[tablename]'
-- or exec sp_GetDDLa #temp
--#################################################################################################
-- copyright 2004-2022 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.
--developer utility function added by Lowell, used in SQL Server Management Studio
-- http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt
--Purpose: Script Any Table, Temp Table or Object(Procedure Function Synonym View Table Trigger)
--#################################################################################################
-- see the thread here for lots of details: http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx
-- You can use this however you like...this script is not rocket science, but it took a bit of work to create.
-- the only thing that I ask
-- is that if you adapt my procedure or make it better, to simply send me a copy of it,
-- so I can learn from the things you've enhanced .The feedback you give will be what makes
-- it worthwhile to me, and will be fed back to the SQL community.
-- add this to your toolbox of helpful scripts.
--#################################################################################################
--
-- V300 uses String concatination and sys.tables instead of a cursor
-- V301 enhanced 07/31/2009 to include extended properties definitions
-- V302 fixes an issue where the schema is created , ie 'bob', but no user named 'bob' owns the schema, so the table is not found
-- V303 fixes an issue where all rules are appearing, instead of just the rule related to a column
-- V304 testing whether vbCrLf is better than just CHAR(13) or CHAR(10), some formatting cleanup with GO statements
-- also fixed an issue with the conversion from syscolumns to sys.columns, max-length is only field we need, not [precision]
-- V305 user feedback helped me find that the type_name function should call user_type_id instead of system_type_id
-- also fixed issue where identity definition missing from numeric/decimal definition
-- V306 fixes the computed columns definition that got broken/removed somehow in V300
-- also formatting when decimal is not an identity
-- V307 fixes bug identified by David Griffiths-491597 from SSC where the @TABLE_ID
-- is reselected, but without it's schema , potentially selecting the wrong table
-- also fixed is the missing size definition for varbinary, also found by David Griffith
-- V308 abstracted all SQLs to use Table Aliases
-- added logic to script a temp table.
-- added warning about possibly not being marked as system object.
-- V309 added logic based on feedback from Vincent Wylenzek @SSC to return the definition from sys.sql_modules for
-- any object like procedure/view/function/trigger, and not just a table.
-- note previously, if you pointed sp_GetDDLa at a view, it returned the view definition as a table...
-- now it will return the view definition instead.
-- V309a returns multi row recordset, one line per record
-- V310a fixed the commented out code related to collation identified by moadh.bs @SSC
-- changed the DEFAULT definitions to not include the default name.
-- V310b Added PERSISTED to calculated columns where applicable
-- V310b fixed COLLATE statement for temp tables
-- V310c fixed NVARCHAR size misreported as doubled.
-- V311 fixed issue where indexes did not identify if the column was ASC or DESC found by nikus @ SSC
-- V311a fixed issue where indexes did not identify if the index was CLUSTERED or NONCLUSTERED found by nikus @ SSC 02/22/2013
-- V312 got rid of all upper casing, and allowing all scripts to generate the exact object names in cases of case sensitive databases.
-- now using the case sensitive name of the table passed: so of you did 'exec sp_getDDLA invoicedocs , it might return the script for InvoiceDocs, as that is how it is spelled in sys.objects.
-- added if exists(drop table/procedure/function) statement to the scripting automatically.
-- toggled the commented out code to list any default constraints by name, hopefully to be more accurate..
-- formatting of index statements to be multi line for better readability
--V314 03/30/2015
-- did i mention this scripts out temp tables too? sp_getDDLa #tmp
-- scripts any object:table,#temptable procedure, function, view or trigger
-- added ability to script synonyms
-- moved logic for REAL datatype to fix error when scripting real columns
-- added OmaCoders suggestion to script column extended properties as well.
-- added matt_slack suggestion to script schemaname as part of index portion of script.
-- minor script cleanup to use QUOTENAME instead of concatenating square brackets.
-- changed compatibility to 2008 and above only, now filtered indexes with WHERE statements script correctly
-- foreign key tables and columns in script now quotenamed to account for spaces in names; previously an error for Application ID instead of [Application ID]
--V315 Fixes Aliases and column names that prevented Case Sensitive collations from working.
-- Adds code if the procedure scripted is a system object
-- index scripts featuring filtered indexes is now included
-- index scripts now include filegroup name and compression settings
-- foreign key cascade delete/update settings now included as identified by Alberto aserio@SSC)
-- Fixes related to scripting extended events as identified by Alberto aserio@SSC)
--V316 Fixes Identified 07/27/2016 by mlm( m.martinelli@SSC)
-- Added logic resolving error when custom data type are defined using name greater than 16 char.
-- Added handling for data types: binary, datetime2, datetimeoffset, time
-- Added Set Based logic for Handling FOREIGN KEYS when one foreign key is define on more then one field
-- Added SPARSE column property
--V317 Fixes Identified 03/30/2017 by Lowell
-- Scripting of Foreign key column(s) are now quotenamed
-- Scripting column store indexes was broken, now fixed for column store indexes
--V318 Fixes Identified 02/14/2018 by Lowell
-- Scripting of with collation added/required for scripting SharePoint/ReportServer , or databases with non standard collations
-- Scripting enhanced to definitively handle case sensitive collations as well.