View or build create table schema statement programmatically with T-SQL [closed]

Question

Is it possible to view or build create statement for selected table?

For example SSMS has feature Table > Script Table as > Create To > New Query Editor Window that can generate similar statement (without constraints though).

But how to generate create statement with constraints programmatically with T-SQL?

There are two ways how to view a definition that might be useful for code generating

exec sp_help 'Foo'

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Foo' and TABLE_NAME = 'Bar'

But I don't want to invent a wheel. Maybe there already exist some migration script for copying the tables with constraints to another database, but I did not find it yet.

Any idea? 🤔

Answer 1

I have been using sp_GetDDLa of Lowell Izaguirre and I use it successfully in many automation works.

I suggest you have a look at it and do your own tests.

I am very thankful for these procedures.

--#################################################################################################
-- Real World DBA Toolkit version 5.08 Lowell Izaguirre [email protected]
--#################################################################################################
-- USAGE: exec sp_GetDDLa GMACT
--   or   exec sp_GetDDLa 'bob.example'
--   or   exec sp_GetDDLa '[schemaname].[tablename]'
--   or   exec sp_GetDDLa #temp
--#################################################################################################
-- copyright 2004-2018 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.
--#################################################################################################
--