Home > Database > Mysql Tutorial > How Can I Generate SQL CREATE Table Scripts from Existing Tables?

How Can I Generate SQL CREATE Table Scripts from Existing Tables?

DDD
Release: 2025-01-14 08:55:16
Original
988 people have browsed it

How Can I Generate SQL CREATE Table Scripts from Existing Tables?

SQL CREATE script using query to generate existing table

While this query itself does not directly generate a CREATE script, it can be derived from schema information stored in SQL Server.

Method:

  1. Identify the target table and retrieve its details (columns, indexes, constraints).
  2. Build DDL scripts based on collected information.

Code:

<code class="language-sql">DECLARE @table_name SYSNAME = 'dbo.MyTable';

DECLARE @DDL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@table_name) + ' (';

SELECT @DDL += CHAR(13) + '    [' + c.name + '] ' + 
                CASE WHEN c.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ','
FROM sys.columns c
WHERE c.[object_id] = OBJECT_ID(@table_name);

SET @DDL = LEFT(@DDL, LEN(@DDL) - 1) + ')';

SELECT @DDL += CHAR(13) + ' CONSTRAINT [PK_' + @table_name + '] PRIMARY KEY (' + 
                STUFF((SELECT ', [' + c.name + ']' FROM sys.index_columns c
                        WHERE c.[object_id] = OBJECT_ID(@table_name) AND c.index_id = 1), 1, 2, '') + ')';

SELECT @DDL += CHAR(13) + ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF);';

PRINT @DDL;</code>
Copy after login

Output example:

<code class="language-sql">CREATE TABLE [dbo].[MyTable] (
    [ID] INT NOT NULL,
    [Name] VARCHAR(50) NULL,
    [Age] INT NULL,
    CONSTRAINT [PK_MyTable] PRIMARY KEY ([ID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF);</code>
Copy after login

The above is the detailed content of How Can I Generate SQL CREATE Table Scripts from Existing Tables?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template