Dans SQL Server, comment générer une instruction CREATE TABLE pour une table donnée ?

Question

J'ai passé une bonne partie du temps à trouver une solution à ce problème, donc dans l'esprit du ce poste Je le publie ici, car je pense qu'il peut être utile à d'autres.

Si quelqu'un a un meilleur script, ou quelque chose à ajouter, merci de le poster.

Edit : Oui les gars, je sais comment le faire dans Management Studio - mais j'avais besoin de pouvoir le faire à partir d'une autre application.

Answer 1

Si l'application à partir de laquelle vous générez les scripts est une application .NET, vous pouvez envisager d'utiliser SMO (Sql Management Objects). Référence Lien vers l'équipe SQL sur la façon d'utiliser SMO pour script objets.

Answer 2

J'ai modifié la réponse acceptée et maintenant il peut obtenir la commande incluant la clé primaire et la clé étrangère dans un certain schéma.

declare @table varchar(100)
declare @schema varchar(100)
set @table = 'Persons' -- set table name here
set @schema = 'OT' -- set SCHEMA 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 and table_schema = @schema
 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

-- foreign key
declare @fkname varchar(100)
select @fkname = constraint_name from information_schema.table_constraints
where table_name = @table and constraint_type='FOREIGN KEY'

if ( @fkname is not null ) begin
    insert into @sql(s) values(',')
    insert into @sql(s) values('  FOREIGN KEY (')
    insert into @sql(s)
        select '   '+COLUMN_NAME+',' from information_schema.key_column_usage
        where constraint_name = @fkname
        order by ordinal_position
    -- remove trailing comma
    update @sql set s=left(s,len(s)-1) where id=@@identity
    insert into @sql(s) values ('  ) REFERENCES ')
    insert into @sql(s) 
        SELECT  
            OBJECT_NAME(fk.referenced_object_id)
        FROM 
            sys.foreign_keys fk
        INNER JOIN 
            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN
            sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
        INNER JOIN
            sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
        where fk.name = @fkname
    insert into @sql(s) 
        SELECT  
            '('+c2.name+')'
        FROM 
            sys.foreign_keys fk
        INNER JOIN 
            sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
        INNER JOIN
            sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
        INNER JOIN
            sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id
        where fk.name = @fkname
end

-- closing bracket
insert into @sql(s) values( ')' )

-- result!
select s from @sql order by id

Answer 3

Je vais améliorer la réponse en prenant en charge les tables partitionnées :

trouver le schéma de partition et la clé de partition en utilisant les scripts ci-dessous :

declare @partition_scheme varchar(100) = (
select distinct ps.Name AS PartitionScheme
from sys.indexes i  
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id  
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id  
where i.object_id = object_id('your table name')
)
print @partition_scheme

declare @partition_column varchar(100) = (
select c.name 
from  sys.tables          t
join  sys.indexes         i 
      on(i.object_id = t.object_id 
  and i.index_id < 2)
join  sys.index_columns  ic 
  on(ic.partition_ordinal > 0 
  and ic.index_id = i.index_id and ic.object_id = t.object_id)
join  sys.columns         c 
  on(c.object_id = ic.object_id 
  and c.column_id = ic.column_id)
where t.object_id  = object_id('your table name')
)
print @partition_column

puis changez la requête de génération en ajoutant la ligne ci-dessous au bon endroit :

+ IIF(@partition_scheme is null, '', 'ON [' + @partition_scheme + ']([' + @partition_column + '])')

Answer 4

Le crédit est dû à @Blorgbeard pour avoir partagé son script. Je vais certainement l'ajouter à mes favoris au cas où j'en aurais besoin.

Oui, vous pouvez "cliquer avec le bouton droit de la souris" sur le tableau et script la CREATE TABLE script, mais :

• L'a script contiendra charges de cruft (intéressé par les propriétés étendues quelqu'un ?)
• Si vous avez plus de 200 tables dans votre schéma, cela va vous prendre une demi-journée pour script le tout à la main.

Avec ce script converti en une procédure stockée, et combiné avec un script enveloppant, vous auriez une belle façon automatisée de déverser votre conception de table dans le contrôle de source, etc.

Le reste du code de votre base de données (SP, index FK, triggers, etc.) serait de toute façon sous contrôle de la source ;)

Answer 5

J'ai remarqué quelque chose - dans la vue INFORMATION_SCHEMA.COLUMNS, CHARACTER_MAXIMUM_LENGTH donne une taille de 2147483647 (2^31-1) pour les types de champs tels que l'image et le texte. ntext est 2^30-1 (étant unicode à double octet et tout).

Cette taille est incluse dans le résultat de cette requête, mais elle n'est pas valide pour ces types de données dans une instruction CREATE (ils ne devraient pas avoir de valeur de taille maximale du tout). Donc, à moins que les résultats de cette requête ne soient corrigés manuellement, l'instruction CREATE script ne fonctionnera pas avec ces types de données.

J'imagine qu'il est possible de corriger le script pour tenir compte de cela, mais cela dépasse mes capacités SQL.