Export database schema into SQL file

Question

Is it possible in MS SQL Server 2008 to export database structure into a T-SQL file    I want to export not only tables schema but also primary keys  foreign keys  constraints  indexes  stored procedures  user defined types functions    Also I don t want the data to be present in this T-SQL file    Is there any way to achieve that

User · Answer

In the picture you can see  In the set script options  choose the last option  Types of data to script you click at the right side and you choose what you want  This is the option you should choose to export a schema and data

User · Answer

Have you tried the Generate Scripts  Right click  tasks  generate scripts  option in SQL Management Studio  Does that produce what you mean by a  SQL File

User · Answer

i wrote this sp to create automatically the schema with all things  pk  fk  partitions  constraints    I wrote it to run in same sp  IMPORTANT   before exec     create type TableType as table  ObjectID int   here the SP  create PROCEDURE  dbo   util ScriptTable         DBName SYSNAME       schema sysname       TableName SYSNAME       IncludeConstraints BIT   1       IncludeIndexes BIT   1       NewTableSchema sysname       NewTableName SYSNAME   NULL       UseSystemDataTypes BIT   0       script varchar max  output AS  BEGIN try     if not exists  select   from sys types where name    TableType           create type TableType as table  ObjectID int --drop type TableType          declare  sql nvarchar max         DECLARE  MainDefinition TABLE  FieldValue VARCHAR 200       --DECLARE  DBName SYSNAME     DECLARE  ClusteredPK BIT     DECLARE  TableSchema NVARCHAR 255           --SET  DBName   DB NAME DB ID        SELECT  TableName   name FROM sysobjects WHERE id   OBJECT ID  TableName           DECLARE  ShowFields TABLE  FieldID INT IDENTITY 1 1                                           DatabaseName VARCHAR 100                                           TableOwner VARCHAR 100                                           TableName VARCHAR 100                                           FieldName VARCHAR 100                                           ColumnPosition INT                                          ColumnDefaultValue VARCHAR 100                                           ColumnDefaultName VARCHAR 100                                           IsNullable BIT                                          DataType VARCHAR 100                                           MaxLength varchar 10                                           NumericPrecision INT                                          NumericScale INT                                          DomainName VARCHAR 100                                           FieldListingName VARCHAR 110                                           FieldDefinition CHAR 1                                           IdentityColumn BIT                                          IdentitySeed INT                                          IdentityIncrement INT                                          IsCharColumn BIT                                           IsComputed varchar 255            DECLARE  HoldingArea TABLE FldID SMALLINT IDENTITY 1 1                                           Flds VARCHAR 4000                                           FldValue CHAR 1  DEFAULT 0            DECLARE  PKObjectID TABLE ObjectID INT           DECLARE  Uniques TABLE ObjectID INT           DECLARE  HoldingAreaValues TABLE FldID SMALLINT IDENTITY 1 1                                                   Flds VARCHAR 4000                                                   FldValue CHAR 1  DEFAULT 0            DECLARE  Definition TABLE DefinitionID SMALLINT IDENTITY 1 1                                           FieldValue VARCHAR 200         set  sql        use    DBName     SELECT distinct DB NAME                inf TABLE SCHEMA              inf TABLE NAME                    inf COLUMN NAME       as COLUMN NAME              CAST inf ORDINAL POSITION AS INT               inf COLUMN DEFAULT              dobj name AS ColumnDefaultName              CASE WHEN inf IS NULLABLE     YES   THEN 1 ELSE 0 END              inf DATA TYPE              case inf CHARACTER MAXIMUM LENGTH when -1 then   max   else CAST inf CHARACTER MAXIMUM LENGTH AS varchar  end--CAST CHARACTER MAXIMUM LENGTH AS INT               CAST inf NUMERIC PRECISION AS INT               CAST inf NUMERIC SCALE AS INT               inf DOMAIN NAME              inf COLUMN NAME                           AS FieldDefinition             --caso di viste  d   come campo identity ma nn d   i valori  quindi lo ignoro              CASE WHEN ic object id IS not NULL and ic seed value is not null THEN 1 ELSE 0 END AS IdentityColumn--CASE WHEN ic object id IS NULL THEN 0 ELSE 1 END AS IdentityColumn              CAST ISNULL ic seed value 0  AS INT  AS IdentitySeed              CAST ISNULL ic increment value 0  AS INT  AS IdentityIncrement              CASE WHEN c collation name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn               cc definition              from  select schema id object id name from sys views union all select schema id object id name from sys tables t                 --sys tables t             join sys schemas s on t schema id s schema id             JOIN sys columns c ON  t object id c object id --AND s schema id c schema id             LEFT JOIN sys identity columns ic ON t object id ic object id AND c column id ic column id             left JOIN sys types st ON st system type id c system type id and st principal id t object id--COALESCE c DOMAIN NAME c DATA TYPE    st name             LEFT OUTER JOIN sys objects dobj ON dobj object id   c default object id AND dobj type     D               left join sys computed columns cc on t object id cc object id and c column id cc column id             join INFORMATION SCHEMA COLUMNS inf on t name inf TABLE NAME                                                and s name inf TABLE SCHEMA                                                and c name inf COLUMN NAME     WHERE inf TABLE NAME    TableName and inf TABLE SCHEMA  schema      ORDER BY inf ORDINAL POSITION              print  sql   INSERT INTO  ShowFields  DatabaseName                                      TableOwner                                      TableName                                      FieldName                                      ColumnPosition                                      ColumnDefaultValue                                      ColumnDefaultName                                      IsNullable                                      DataType                                      MaxLength                                      NumericPrecision                                      NumericScale                                      DomainName                                      FieldListingName                                      FieldDefinition                                      IdentityColumn                                      IdentitySeed                                      IdentityIncrement                                      IsCharColumn                                      IsComputed                                           exec sp executesql  sql                         N  TableName varchar 50   schema varchar 50                            TableName  TableName  schema  schema                        SELECT  DBName--DB NAME                TABLE SCHEMA              TABLE NAME              COLUMN NAME              CAST ORDINAL POSITION AS INT               COLUMN DEFAULT              dobj name AS ColumnDefaultName              CASE WHEN c IS NULLABLE    YES  THEN 1 ELSE 0 END              DATA TYPE              CAST CHARACTER MAXIMUM LENGTH AS INT               CAST NUMERIC PRECISION AS INT               CAST NUMERIC SCALE AS INT               DOMAIN NAME              COLUMN NAME                       AS FieldDefinition              CASE WHEN ic object id IS NULL THEN 0 ELSE 1 END AS IdentityColumn              CAST ISNULL ic seed value 0  AS INT  AS IdentitySeed              CAST ISNULL ic increment value 0  AS INT  AS IdentityIncrement              CASE WHEN st collation name IS NOT NULL THEN 1 ELSE 0 END AS IsCharColumn              FROM INFORMATION SCHEMA COLUMNS c             JOIN sys columns sc ON  c TABLE NAME   OBJECT NAME sc object id  AND c COLUMN NAME   sc Name             LEFT JOIN sys identity columns ic ON c TABLE NAME   OBJECT NAME ic object id  AND c COLUMN NAME   ic Name             JOIN sys types st ON COALESCE c DOMAIN NAME c DATA TYPE    st name             LEFT OUTER JOIN sys objects dobj ON dobj object id   sc default object id AND dobj type    D       WHERE c TABLE NAME    TableName     ORDER BY c TABLE NAME  c ORDINAL POSITION            SELECT TOP 1  TableSchema   TableOwner FROM  ShowFields          INSERT INTO  HoldingArea  Flds  VALUES               INSERT INTO  Definition FieldValue VALUES  CREATE TABLE     CASE WHEN  NewTableName IS NOT NULL THEN  DBName          NewTableSchema          NewTableName ELSE  DBName          TableSchema          TableName END      INSERT INTO  Definition FieldValue VALUES          INSERT INTO  Definition FieldValue      SELECT   CHAR 10    FieldName                  --CASE WHEN DomainName IS NOT NULL AND  UseSystemDataTypes   0 THEN DomainName   CASE WHEN IsNullable   1 THEN   NULL   ELSE   NOT NULL   END ELSE UPPER DataType   CASE WHEN IsCharColumn   1 THEN       CAST MaxLength AS VARCHAR 10         ELSE    END  CASE WHEN IdentityColumn   1 THEN   IDENTITY     CAST IdentitySeed AS VARCHAR 5          CAST IdentityIncrement AS VARCHAR 5         ELSE    END  CASE WHEN IsNullable   1 THEN   NULL   ELSE   NOT NULL   END  CASE WHEN ColumnDefaultName IS NOT NULL AND  IncludeConstraints   1 THEN  CONSTRAINT      ColumnDefaultName      DEFAULT    UPPER ColumnDefaultValue  ELSE    END END   CASE WHEN FieldID    SELECT MAX FieldID  FROM  ShowFields  THEN    ELSE     END                   CASE WHEN DomainName IS NOT NULL AND  UseSystemDataTypes   0 THEN DomainName                CASe WHEN IsNullable   1 THEN   NULL                ELSE   NOT NULL                END          ELSE              case when IsComputed is null then                 UPPER DataType                    CASE WHEN IsCharColumn   1 THEN       CAST MaxLength AS VARCHAR 10                          ELSE                      CASE WHEN DataType    numeric  THEN       CAST NumericPrecision AS VARCHAR 10        CAST NumericScale AS VARCHAR 10                              ELSE                         CASE WHEN DataType    decimal  THEN       CAST NumericPrecision AS VARCHAR 10        CAST NumericScale AS VARCHAR 10                                  ELSE                             end                       end                  END                   CASE WHEN IdentityColumn   1 THEN   IDENTITY     CAST IdentitySeed AS VARCHAR 5          CAST IdentityIncrement AS VARCHAR 5                          ELSE                     END                   CASE WHEN IsNullable   1 THEN   NULL                    ELSE   NOT NULL                    END                   CASE WHEN ColumnDefaultName IS NOT NULL AND  IncludeConstraints   1 THEN  CONSTRAINT      replace ColumnDefaultName  TableName  NewTableName       DEFAULT    UPPER ColumnDefaultValue                   ELSE                     END              else                   as   IsComputed                 end         END            CASE WHEN FieldID    SELECT MAX FieldID  FROM  ShowFields  THEN             ELSE              END               FROM     ShowFields          IF  IncludeConstraints   1         BEGIN                      set  sql                    use    DBName           SELECT  distinct     CONSTRAINT        NewTableName       replace name  TableName             FOREIGN KEY       ParentColumns       REFERENCES       ReferencedObject            ReferencedColumns                     FROM   SELECT   ReferencedObject   OBJECT NAME fk referenced object id   ParentObject   OBJECT NAME parent object id  fk name                     REVERSE SUBSTRING REVERSE     SELECT cp name                            FROM   sys foreign key columns fkc                    JOIN sys columns cp ON fkc parent object id   cp object id AND fkc parent column id   cp column id                    WHERE fkc constraint object id   fk object id   FOR XML PATH             2  8000   ParentColumns                     REVERSE SUBSTRING REVERSE     SELECT cr name                            FROM   sys foreign key columns fkc                   JOIN sys columns cr ON fkc referenced object id   cr object id AND fkc referenced column id   cr column id                 WHERE fkc constraint object id   fk object id   FOR XML PATH             2  8000   ReferencedColumns                    FROM sys foreign keys fk                         inner join sys schemas s on fk schema id s schema id and s name  schema  a                 WHERE ParentObject    TableName                                print  sql                  INSERT INTO  Definition FieldValue          exec sp executesql  sql                     N  TableName varchar 50   NewTableName varchar 50   schema varchar 50                            TableName  TableName  NewTableName  NewTableName  schema  schema                           SELECT      CONSTRAINT      name      FOREIGN KEY      ParentColumns      REFERENCES      ReferencedObject          ReferencedColumns                    FROM   SELECT   ReferencedObject   OBJECT NAME fk referenced object id   ParentObject   OBJECT NAME parent object id  fk name                     REVERSE SUBSTRING REVERSE     SELECT cp name                          FROM   sys foreign key columns fkc                    JOIN sys columns cp ON fkc parent object id   cp object id AND fkc parent column id   cp column id                    WHERE fkc constraint object id   fk object id   FOR XML PATH           2  8000   ParentColumns                     REVERSE SUBSTRING REVERSE     SELECT cr name                          FROM   sys foreign key columns fkc                   JOIN sys columns cr ON fkc referenced object id   cr object id AND fkc referenced column id   cr column id                 WHERE fkc constraint object id   fk object id   FOR XML PATH           2  8000   ReferencedColumns                    FROM sys foreign keys fk      a                 WHERE ParentObject    TableName                                             set  sql                            use    DBName               SELECT distinct    CONSTRAINT        NewTableName       replace c name  TableName             CHECK      definition              FROM sys check constraints c join sys schemas s on c schema id s schema id and s name  schema                 WHERE OBJECT NAME parent object id     TableName                                        print  sql             INSERT INTO  Definition FieldValue               exec sp executesql  sql                                 N  TableName varchar 50   NewTableName varchar 50   schema varchar 50                            TableName  TableName  NewTableName  NewTableName  schema  schema                            SELECT   CONSTRAINT      name      CHECK     definition FROM sys check constraints                 WHERE OBJECT NAME parent object id     TableName                                         set  sql                            use    DBName               SELECT DISTINCT  PKObject   cco object id              FROM    sys key constraints cco                 JOIN sys index columns cc ON cco parent object id   cc object id AND cco unique index id   cc index id                 JOIN sys indexes i ON cc object id   i object id AND cc index id   i index id             join sys schemas s on cco schema id s schema id and s name  schema             WHERE    OBJECT NAME parent object id     TableName    AND  i type   1 AND    is primary key   1                           print  sql             INSERT INTO  PKObjectID ObjectID               exec sp executesql  sql                                 N  TableName varchar 50   schema varchar 50                                    TableName  TableName  schema  schema                            SELECT DISTINCT  PKObject   cco object id              FROM    sys key constraints cco                 JOIN sys index columns cc ON cco parent object id   cc object id AND cco unique index id   cc index id                 JOIN sys indexes i ON cc object id   i object id AND cc index id   i index id             WHERE    OBJECT NAME parent object id     TableName    AND  i type   1 AND    is primary key   1                                         set  sql                            use    DBName               SELECT DISTINCT    PKObject   cco object id             FROM    sys key constraints cco                JOIN sys index columns cc ON cco parent object id   cc object id AND cco unique index id   cc index id               JOIN sys indexes i ON cc object id   i object id AND cc index id   i index id             join sys schemas s on cco schema id s schema id and s name  schema             WHERE    OBJECT NAME parent object id     TableName AND  i type   2 AND    is primary key   0 AND    is unique constraint   1                           print  sql             INSERT INTO  Uniques ObjectID              exec sp executesql  sql                                 N  TableName varchar 50   schema varchar 50                                    TableName  TableName  schema  schema                            SELECT DISTINCT    PKObject   cco object id             FROM    sys key constraints cco                JOIN sys index columns cc ON cco parent object id   cc object id AND cco unique index id   cc index id               JOIN sys indexes i ON cc object id   i object id AND cc index id   i index id             WHERE    OBJECT NAME parent object id     TableName AND  i type   2 AND    is primary key   0 AND    is unique constraint   1                                         SET  ClusteredPK   CASE WHEN   ROWCOUNT  gt  0 THEN 1 ELSE 0 END                          declare  t TableType             insert  t select   from  PKObjectID             declare  u TableType             insert  u select   from  Uniques                          set  sql                            use    DBName               SELECT distinct    CONSTRAINT       NewTableName       replace cco name  TableName         CASE type WHEN   PK   THEN    PRIMARY KEY      CASE WHEN pk ObjectID IS NULL THEN    NONCLUSTERED    ELSE    CLUSTERED    END  WHEN   UQ   THEN    UNIQUE    END   CASE WHEN u ObjectID IS NOT NULL THEN    NONCLUSTERED    ELSE      END                      REVERSE SUBSTRING REVERSE   SELECT   c name      CASE WHEN cc is descending key   1 THEN    DESC   ELSE    ASC   END                         FROM   sys key constraints ccok                LEFT JOIN sys index columns cc ON ccok parent object id   cc object id AND cco unique index id   cc index id             LEFT JOIN sys columns c ON cc object id   c object id AND cc column id   c column id              LEFT JOIN sys indexes i ON cc object id   i object id AND cc index id   i index id               WHERE i object id   ccok parent object id AND   ccok object id   cco object id                 order by key ordinal FOR XML PATH          2  8000                       FROM sys key constraints cco              inner join sys schemas s on cco schema id s schema id and s name  schema             LEFT JOIN  U u ON cco object id   u objectID             LEFT JOIN  t pk ON cco object id   pk ObjectID                 WHERE    OBJECT NAME cco parent object id     TableName                                                      print  sql             INSERT INTO  Definition FieldValue              exec sp executesql  sql                                 N  TableName varchar 50   NewTableName varchar 50   schema varchar 50   t TableType readonly  u TableType readonly                                   TableName  TableName  NewTableName  NewTableName  schema  schema  t  t  u  u                                         SELECT   CONSTRAINT     name   CASE type WHEN  PK  THEN   PRIMARY KEY     CASE WHEN pk ObjectID IS NULL THEN   NONCLUSTERED   ELSE   CLUSTERED   END  WHEN  UQ  THEN   UNIQUE   END   CASE WHEN u ObjectID IS NOT NULL THEN   NONCLUSTERED   ELSE    END                     REVERSE SUBSTRING REVERSE   SELECT   c name      CASE WHEN cc is descending key   1 THEN   DESC  ELSE   ASC  END                       FROM   sys key constraints ccok                LEFT JOIN sys index columns cc ON ccok parent object id   cc object id AND cco unique index id   cc index id            LEFT JOIN sys columns c ON cc object id   c object id AND cc column id   c column id             LEFT JOIN sys indexes i ON cc object id   i object id AND cc index id   i index id              WHERE i object id   ccok parent object id AND   ccok object id   cco object id    FOR XML PATH        2  8000                    FROM sys key constraints cco             LEFT JOIN  PKObjectID pk ON cco object id   pk ObjectID                LEFT JOIN  Uniques u ON cco object id   u objectID            WHERE    OBJECT NAME cco parent object id     TableName                        END                     INSERT INTO  Definition FieldValue  VALUES                       set  sql                    use    DBName           select    on      d name          c name                from sys tables t join sys indexes i on i object id   t object id and i index id  lt  2                            join sys index columns ic on ic partition ordinal  gt  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                            join sys schemas s on t schema id s schema id                           join sys data spaces d on i data space id d data space id         where t name  TableName and s name  schema         order by key ordinal                            print  x          print  sql         INSERT INTO  Definition FieldValue           exec sp executesql  sql                             N  TableName varchar 50   schema varchar 50                                TableName  TableName  schema  schema                       IF  IncludeIndexes   1         BEGIN             set  sql                            use    DBName               SELECT distinct    CREATE      i type desc      INDEX       replace i name COLLATE SQL Latin1 General CP1 CI AS  TableName  NewTableName        ON    DBName      NewTableSchema      NewTableName                        REVERSE SUBSTRING REVERSE     SELECT name   CASE WHEN sc is descending key   1 THEN    DESC   ELSE    ASC   END                        FROM  sys index columns sc               JOIN sys columns c ON sc object id   c object id AND sc column id   c column id                WHERE  t name  TableName AND  sc object id   i object id AND  sc index id   i index id                                             and is included column 0             ORDER BY key ordinal ASC   FOR XML PATH              2  8000                        ISNULL     include     REVERSE SUBSTRING REVERSE     SELECT name                        FROM  sys index columns sc               JOIN sys columns c ON sc object id   c object id AND sc column id   c column id                WHERE  t name  TableName AND  sc object id   i object id AND  sc index id   i index id                                             and is included column 1             ORDER BY key ordinal ASC   FOR XML PATH              2  8000                                     FROM sys indexes i join sys tables t on i object id t object id                                join sys schemas s on t schema id s schema id                AND CASE WHEN  ClusteredPK   1 AND is primary key   1 AND i type   1 THEN 0 ELSE 1 END   1   AND is unique constraint   0   AND is primary key   0                  where t name  TableName and s name  schema                                        print  sql             INSERT INTO  Definition FieldValue                  exec sp executesql  sql                                 N  TableName varchar 50   NewTableName varchar 50   schema varchar 50    ClusteredPK bit                                   TableName  TableName  NewTableName  NewTableName  schema  schema  ClusteredPK  ClusteredPK                      END                                                          SELECT    CREATE     type desc     INDEX       name  COLLATE SQL Latin1 General CP1 CI AS      ON       OBJECT NAME object id              REVERSE SUBSTRING REVERSE     SELECT name   CASE WHEN sc is descending key   1 THEN   DESC  ELSE   ASC  END                          FROM  sys index columns sc                   JOIN sys columns c ON sc object id   c object id AND sc column id   c column id                    WHERE  OBJECT NAME sc object id     TableName AND  sc object id   i object id AND  sc index id   i index id                    ORDER BY index column id ASC   FOR XML PATH            2  8000                             FROM sys indexes i                     WHERE   OBJECT NAME object id     TableName                 AND CASE WHEN  ClusteredPK   1 AND is primary key   1 AND type   1 THEN 0 ELSE 1 END   1   AND is unique constraint   0   AND is primary key   0                                                          INSERT INTO  MainDefinition FieldValue                 SELECT FieldValue FROM  Definition                 ORDER BY DefinitionID ASC                           ----------------------------------             --SELECT FieldValue    FROM  MainDefinition FOR XML PATH                 set  script  use    DBName      SELECT FieldValue    FROM  MainDefinition FOR XML PATH                               --declare  q    varchar max              --set  q  select replace  SELECT FieldValue FROM  MainDefinition FOR XML PATH        lt  FieldValue gt                    --set  script  select REPLACE  q   lt FieldValue gt                    --drop type TableType END try --                                                                                                                                                                                BEGIN CATCH             BEGIN         -- INIZIO  Procedura in errore                                                                                                                                                                       PRINT                                                                                                                                                                            PRINT  ErrorNumber                     CAST ERROR NUMBER   AS NVARCHAR MAX               PRINT  ErrorSeverity                   CAST ERROR SEVERITY   AS NVARCHAR MAX                PRINT  ErrorState                      CAST ERROR STATE   AS NVARCHAR MAX                PRINT  ErrorLine                       CAST ERROR LINE   AS NVARCHAR MAX                PRINT  ErrorMessage                    CAST ERROR MESSAGE   AS NVARCHAR MAX               PRINT                                                                                                                                                                        -- FINE  Procedura in errore                                                                                                                                                               END          set  script        return -1 END CATCH    --                                                                                                                                                                                    to exec it  declare  s varchar max  exec  util ScriptTable     db   schema source   table source  1 1  schema dest   tab dest  0  s output select  s

User · Answer

You can generate scripts to a file via SQL Server Management Studio  here are the steps    Right click the database you want to generate scripts for  not the table  and select tasks  - generate scripts Next  select the requested table tables  views  stored procedures  etc  from select specific database objects  Click advanced - select the types of data to script  Click Next and finish   MSDN Generate Scripts  When generating the scripts  there is an area that will allow you to script  constraints  keys  etc  From SQL Server 2008 R2 there is an Advanced Option under scripting

[sql] Export database schema into SQL file

Examples related to sql

Examples related to sql-server

Examples related to database

Examples related to tsql

Examples related to export