7

I want to generate create table script of already created table in my live database.

I know we can generate scripts using right click on table and click on 'script table as' menu and script will be generated. Because my live db UI is running very slow.

I want to do same process using SQL query. is there any way??

1
  • 2
    @nimizen that has limitations: it just creates the table and doesn't create indexes and constraints, you could do this with SELECT top 0 * INTO clone FROM table. Commented Oct 30, 2013 at 9:05

3 Answers 3

3

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)

(I originally answered this here, but SQL Server-only devs probably do not relate the question title to this problem)

Sign up to request clarification or add additional context in comments.

Comments

3

For a more complete(-ish) solution for generating a CREATE TABLE statement with indexes, triggers and constraints try the stored procedure made by Lowell Izaguirre.

It has been tested and developed since 2004, last update was in 2013.

I've also made some improvements to include index options (PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY):

here are the changes, can't fit all the code so you'll find the complete modified version at http://pastebin.com/LXpBeuN1 .


Update

I've talked with Lowell and a new version will be online soon with the new changes for index options and other improvements.

Comments

2

Use this query :

DROP FUNCTION [dbo].[Get_Table_Script]
Go

Create Function Get_Table_Script
(
    @vsTableName varchar(50)
)

Returns
    VarChar(Max)
With ENCRYPTION

Begin

Declare @ScriptCommand varchar(Max)

Select @ScriptCommand =
    ' Create Table [' + SO.name + '] (' + o.list + ')' 
    +
    (
        Case
        When TC.Constraint_Name IS NULL 
            Then ''
        Else 'ALTER TABLE ' + SO.Name + ' ADD CONSTRAINT ' +
            TC.Constraint_Name  + ' PRIMARY KEY ' + ' (' + LEFT(j.List, Len(j.List)-1) + ')'
        End
    )
From sysobjects As SO
    Cross Apply

    (
        Select 
            '  [' + column_name + '] ' + 
             data_type + 
             (
                Case data_type
                    When 'sql_variant' 
                        Then ''
                    When 'text' 
                        Then ''
                    When 'decimal' 
                        Then '(' + Cast( numeric_precision_radix As varchar ) + ', ' + Cast( numeric_scale As varchar ) + ') '
                    Else Coalesce( '(' + 
                                        Case 
                                            When character_maximum_length = -1 
                                                Then 'MAX'
                                            Else Cast( character_maximum_length As VarChar ) 
                                        End + ')' , ''
                                 ) 
                End 
            ) 
            + ' ' +
            (
                Case 
                    When Exists ( 
                                    Select id 
                                    From syscolumns
                                    Where 
                                        ( object_name(id) = SO.name )
                                        And 
                                        ( name = column_name )
                                        And 
                                        ( columnproperty(id,name,'IsIdentity') = 1 )
                                ) 
                        Then 'IDENTITY(' + 
                                Cast( ident_seed(SO.name) As varchar ) + ',' + 
                                Cast( ident_incr(SO.name) As varchar ) + ')'

                    Else ''

                End
            ) + ' ' +

            (
                Case 
                    When IS_NULLABLE = 'No' 
                        Then 'NOT ' 
                    Else '' 
                End 
            ) + 'NULL ' + 
            (
                Case 
                    When information_schema.columns.COLUMN_DEFAULT IS NOT NULL 
                        Then 'DEFAULT ' + information_schema.columns.COLUMN_DEFAULT 
                    ELse '' 
                End 
            ) + ', ' 
        From information_schema.columns 
        Where 
            ( table_name = SO.name )
        Order by ordinal_position
        FOR XML PATH('')) o (list)

        Inner Join information_schema.table_constraints As TC On (
                                                                    ( TC.Table_name = SO.Name )
                                                                    AND 
                                                                    ( TC.Constraint_Type  = 'PRIMARY KEY' )
                                                                    And 
                                                                    ( TC.TABLE_NAME = @vsTableName )
                                                                 )
        Cross Apply
            (
                Select '[' + Column_Name + '], '
                From  information_schema.key_column_usage As kcu
                Where 
                    ( kcu.Constraint_Name = TC.Constraint_Name )
                Order By ORDINAL_POSITION
                FOR XML PATH('')
            ) As j (list)
Where
    ( xtype = 'U' )
    AND 
    ( Name NOT IN ('dtproperties') )

Return @ScriptCommand

End

And you can fire this Function like this :

Select [dbo].Get_Table_Script '<Your_Table_Name>'

And for create trigger use this

SELECT     
DB_NAME() AS DataBaseName,                  
dbo.SysObjects.Name AS TriggerName,
dbo.sysComments.Text AS SqlContent
FROM 
dbo.SysObjects INNER JOIN 
    dbo.sysComments ON 
    dbo.SysObjects.ID = dbo.sysComments.ID
WHERE   
(dbo.SysObjects.xType = 'TR') 
AND 
dbo.SysObjects.Name LIKE '<Trigger_Name>'

5 Comments

source of script ? What are the limitations (will it handle external keys, triggers, etc.) ?
@clickstefan : in this question @Pankaj Agarwal want create table script.for generate trigger scripts i will send another answer.
@clickstefan : use this link for find all triger in database : stackoverflow.com/questions/13200511/…
yes the 'script table as' option will give you just the create table and constraints, dough a complete DLL should contain all that as it's table related (triggers).
also from what I can tell it is missing support for index_options (eg: PAD_INDEX, FILLFACTOR, IGNORE_DUP_KEY etc.).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.