Click here to Skip to main content
12,074,808 members (55,215 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
Hi to all,

how to generate sql table scripts (include primary key, foreign key, indexes,triggers...so on) through query in sql server.

thanks.
Posted 23-Dec-11 19:34pm
K N R2K
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Try this :

select object_definition(object_id)
from sys.objects
where type_desc in ('SQL_SCALAR_FUNCTION',
'SQL_STORED_PROCEDURE',
'SQL_TABLE_VALUED_FUNCTION',
'SQL_TRIGGER',
'VIEW')
Extracted from Here[^]


And also have a look at here :
http://www.mindsdoor.net/dmo/dmoscriptalldatabases.html[^]

[After you comment]
Try this one to generate script of your 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) + ')'
Extracted from Here[^]

Use google to find more scripts about this issue.

And if you want this to deploy your tables to client side so how about using script generator of SSMS ?

Hope it helps.
  Permalink  
v2
Comments
K N R 24-Dec-11 0:49am
   
hey amir,

how to get one specified table script.
srmohanr 23-Jun-14 3:12am
   
Hi Amir, Thanks your post.
Please help how to get Script along with data for a particular table.

Thanks in advance
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

declare @table varchar(100)
set @table = 'MyTable' -- set table name here
declare @sql table(s varchar(1000), id int identity)
 
-- create statement
insert into  @sql(s) values ('create table [' + @table + '] (')
 
-- column list
insert into @sql(s)
select 
    '  ['+column_name+'] ' + 
    data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +
    case when exists ( 
        select id from syscolumns
        where object_name(id)=@table
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
    ) then
        'IDENTITY(' + 
        cast(ident_seed(@table) as varchar) + ',' + 
        cast(ident_incr(@table) as varchar) + ')'
    else ''
    end + ' ' +
    ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + 
    coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','
 
 from information_schema.columns where table_name = @table
 order by ordinal_position
 
-- primary key
declare @pkname varchar(100)
select @pkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='PRIMARY KEY'
 
if ( @pkname is not null ) begin
    insert into @sql(s) values('  PRIMARY KEY (')
    insert into @sql(s)
        select '   ['+COLUMN_NAME+'],' from information_schema.key_column_usage
        where constraint_name = @pkname
        order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  )')
end
else begin
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
end
 
-- closing bracket
insert into @sql(s) values( ')' )
 
-- result!
select s from @sql order by id
  Permalink  
v2
Comments
csharpbd 1-Nov-12 1:33am
   
my vote 4, because i need more information like Unique and FOREIGN key etc.
Member 7726356 22-Nov-13 11:01am
   
good for generate script of create table
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Go through the below link for different examples..,

Here[^]
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

see Discussion for same.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

select  '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 so
cross apply
    (SELECT 
        '  ['+column_name+'] ' + 
        data_type + case data_type
            when 'sql_variant' then ''
            when 'text' then ''
            when 'ntext' then ''
            when 'xml' then ''
            when 'decimal' then '(' + cast(numeric_precision 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)
left join
    information_schema.table_constraints tc
on  tc.Table_name       = so.Name
AND tc.Constraint_Type  = 'PRIMARY KEY'
cross apply
    (select '[' + Column_Name + '], '
     FROM   information_schema.key_column_usage kcu
     WHERE  kcu.Constraint_Name = tc.Constraint_Name
     ORDER BY
        ORDINAL_POSITION
     FOR XML PATH('')) j (list)
where   xtype = 'U'
AND name    NOT IN ('dtproperties')
  Permalink  
Comments
CHill60 12-Mar-15 11:06am
   
This adds nothing to the solutions posted 3 years ago

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web04 | 2.8.160208.1 | Last Updated 12 Mar 2015
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100