CODEWITHSUNDEEP
sql-server-2008
sharadov
sharadov

Reputation: 1098

Script table schema using tsql

I am writing code to script CREATE TABLE statements for all tables across all databases on a server.The code below will work for a specific database. Would like some ideas on how to modify it, so it can script table schema for all the tables. 

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

Upvotes: 2

Views: 2003

Answers (1)

Jeff Reece
Jeff Reece

Reputation: 36

Since you seem determined on TSQL, a quick and dirty solution would be to use the (undocumented) sp_foreachdb stored procedure. That will give you the name of every database on the sql instance, so you would just USE each database in turn and run TSQL that you posted (after escaping all the single qoutes in it).

Heres a shortened example:

EXECUTE sp_msforeachdb 'USE [?]
select  ''create table ['' + so.name + ''] ''
from    sysobjects so 
-- the rest of your escaped TSQL goes here...
-- then finish with a closing quote: 
'

Upvotes: 2

Related Questions