Search
Sponsor
Winners

Win Prizes

Social Presence
Twitter Twitter LinkedIn YouTube Google

Like us on Facebook
Advertisements
Top Codes Author
Wed, 01-Oct-2014 Authors
Zondray
40
All Time Authors
Satyapriyanayak
12160
Jayakumars
8120
Vishal@Neeraj
7160

Latest members | More ...


(Statistics delayed by 5 minutes)
 Codes

How to generate create table script for all tables in SQL Server database?

Raja
Posted by Raja under ASP.NET on 12/24/2008 5:27:48 PM | Views : 23850 | Status : [Member]

Generate create table script for all tables in the SQL Server database.

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 '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)
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')


Regards,
Raja, USA
Found interesting? Add this to:



Comments or Responses

Posted by: Deeraj on: 1/6/2009 | Level:Starter | Status: [Member]

In SQL 2005:

1. Righ click on the database
2. Choose Tasks
3. Select Generate Scripts.

Follow the instructions in the Scrip wizard that pops-up and provide inputs as necessary. The scripts will be available in a flash.



Posted by: Lokanadam on: 8/27/2009 | Level:Starter | Status: [Member]

Wonderful ! Really Cool SQL ! Saved me a day


Login to post response