How do you extract a CREATE statement for all the tables in a particular database so that you can re-CREATE the tables in another database or maybe for a backup? I know there is a backup tool in most SQL client or for the matter of fact a Microsoft SQL Management Studio for most Microsoft users and lovers. You can simply take the following steps to create the table CREATE statement quickly.

1. Right-click on the database.
2. Choose Tasks from the context menu.
3. Select Generate Scripts.

After that, follow the instructions in the Scrip Wizard that pops-up and provide all the appropriate inputs, and after that the CREATE statements will be available for you.

But these days, most of the database are in the cloud, and yes you can connect to these database using Microsoft SQL Management Studio with the appropriate setting. but for the sake or argument, you do not have Microsoft SQL Management Studio. Then how would you get all the table CREATE statement in that database? Your only tool in your arsenal is a web based SQL client. I fell into that scenario recently being a long time GoDaddy Microsoft SQL user. The only way to access and manipulate my tables were using the web admin tool (as shown below). And there was a ‘Web Analyzer’ tool which can run SQL command.

Now to get the CREATE SQL statement for all the tables in my database, I used the following SQL command (as shown below). Btw, there are 488 tables in my database and I was not about to extract it one table at a time. No way Jose!

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

The output of the above SQL command will produce output as follows. I am showing only 4 table here, but trust me, the list is long.

Column1
create table [pjxCostTypes] ( [cosID] nvarchar(10) NOT NULL , [cosDesc] nvarchar(50) NOT NULL , )
create table [wmsMonth] ( [monID] nvarchar(2) NOT NULL , [monShort] nvarchar(3) NOT NULL , [monLong] nvarchar(16) NOT NULL , )
create table [wowLUCountries] ( [couIDPK] nvarchar(50) NOT NULL , [couName] nvarchar(50) NOT NULL , [couRegion] nvarchar(50) NOT NULL , )
create table [wmsDayOfWeek] ( [dayID] nvarchar(2) NOT NULL , [dayShort] nvarchar(3) NOT NULL , [dayLong] nvarchar(10) NOT NULL , )

I hope this help anyone trying to MacGyver an SQL database table CREATE SQL statement.