SQL Server Metadata DDL scripts

Question

_{Monday, March 17, 2014 6:51 PM}

I need to get DDL scripts for few tables in SQL server. In Oracle I did the same using below query

Select DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','DATABASE_NAME') AS DB from DUAL";

Any Idea how to get the same in SQL server in case we have the table name and corresponding schema name???

All replies (6)

_{Monday, March 17, 2014 7:19 PM ✅Answered}

One option is to right click on the table name in the Object Explorer and select 'Script Table as ' and then 'Create to...'

See also this solution:

http://gallery.technet.microsoft.com/scriptcenter/262deee3-e54a-4f9f-8eec-d3d5c42d9e61

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

---

_{Tuesday, March 18, 2014 7:10 AM ✅Answered}

Try the suggestions in the below link: You may need to test it thoroughly.

http://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table

---

_{Tuesday, March 18, 2014 7:11 AM ✅Answered}

you can write a script based on INFORMATION_SCHEMA views TABLES,COLUMNS ,TABLE_CONSTRAINTS ,CONSTRAINTS_COLUMN_USAGE etc

Another method is to use DMO

http://www.databasejournal.com/features/mssql/article.php/2205291/Generate-Scripts-for-SQL-Server-Objects.htm

alternatively you can use SMO also for the same

http://www.mssqltips.com/sqlservertip/1833/generate-scripts-for-database-objects-with-smo-for-sql-server/

Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

---

_{Monday, March 17, 2014 7:42 PM}

You can try using Powershell.

    
    $server             = "localhost"
    $database           = "PowerSQL"
    $outputfile     = "F:\PowerSQL\table\table.sql"
    $schema             = "dbo"

    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null 
 
    $srv = New-Object Microsoft.SqlServer.Management.Smo.Server("$server") 
    $db = New-Object Microsoft.SqlServer.Management.Smo.Database 
    $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv) 
    
    $scrp.Options.ScriptDrops = $FALSE 
    $scrp.Options.WithDependencies = $TRUE 
    
    $db = $srv.Databases[$database]
    $tbs=$db.Tables | Where-Object {$_.Name -like "TLOG*"}     
 
    foreach($tb in $tbs) 
    { 
        $script=$tb.Script() 
        $script >> $outputfile 
        "GO" >> $outputfile 
 
    } 

OR

Refer the below link

http://dba.stackexchange.com/questions/18059/copy-complete-structure-of-a-table

-Prashanth

---

_{Tuesday, March 18, 2014 6:04 AM}

Thanks a lot for all your replies.

@Moderator: I need to do using some .sql script . As I need to call the script using Java program. User cannot manually click to get create script.

---

_{Tuesday, March 18, 2014 7:02 AM}

@Prashanth: I cannot use any other software other than MS SQL server Management Studio. I need to call all the scripts using Java. If I would use PowerShell then the same should be installed on system of users also.

Has anyone created some stored procedure or written some script for getting DDL scripts?

Regards,

Megha