2

I was wondering if there is an equivalent in SQL Server 2008 to Oracle's DBMS_METADATA.GET_DDL Function? You can pass this function a table name and it will return the ddl for that table so that you can use it to build a script for a schema.

I know I can go into SSMS and use that, but I would prefer to have a t-sql script that would generate the ddl for me.

Thanks,

S

scarpacci
  • 8,957
  • 16
  • 79
  • 144
  • 3
    Possible duplicate of [In SQL Server, how do I generate a CREATE TABLE statement for a given table?](http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table) – Shannon Severance Aug 31 '16 at 14:44
  • From body text, "... a table name ... return the ddl for that table." looks to be a duplicate of http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table Covers that case. – Shannon Severance Aug 31 '16 at 14:46

2 Answers2

2

I using this query for generate query but this work for 1 table : 

declare @vsSQL varchar(8000)
declare @vsTableName varchar(50)
select @vsTableName = 'Customers'

select @vsSQL = 'CREATE TABLE ' + @vsTableName + char(10) + '(' + char(10)

select @vsSQL = @vsSQL + ' ' + sc.Name + ' ' +
st.Name +
case when st.Name in ('varchar','varchar','char','nchar') then '(' + cast(sc.Length as varchar) + ') ' else ' ' end +
case when sc.IsNullable = 1 then 'NULL' else 'NOT NULL' end + ',' + char(10)
from sysobjects so
join syscolumns sc on sc.id = so.id
join systypes st on st.xusertype = sc.xusertype
where so.name = @vsTableName
order by
sc.ColID

select substring(@vsSQL,1,len(@vsSQL) - 2) + char(10) + ')'
Kevin saja
  • 21
  • 2
0

If you are looking for a TSQL solution, it is quite verbose, as [this example]¹ shows.

A shorter alternative would be using the SMO library (example)

¹ Link for this example deleted. The way Internet Archive Wayback Machine displayed an error saying that they could not display the content. And following the link to the original went someplace malicious. (Grave errors, instruction to call some number, etc.)

Shannon Severance
  • 18,025
  • 3
  • 46
  • 67
devio
  • 36,858
  • 7
  • 80
  • 143