Generate SQL Create Scripts for existing tables with Query

Question

I'm trying to get the CREATE scripts for existing tables within SQL Server 2008. I assume I can do this by querying the sys.tables somehow, however this isn't returning me the CREATE script data.

Answer 1

Possible this be helpful for you. This script generate indexes, FK's, PK and common structure for any table.

For example -

DDL:

CREATE TABLE [dbo].[WorkOut](     [WorkOutID] [bigint] IDENTITY(1,1) NOT NULL,     [TimeSheetDate] [datetime] NOT NULL,     [DateOut] [datetime] NOT NULL,     [EmployeeID] [int] NOT NULL,     [IsMainWorkPlace] [bit] NOT NULL,     [DepartmentUID] [uniqueidentifier] NOT NULL,     [WorkPlaceUID] [uniqueidentifier] NULL,     [TeamUID] [uniqueidentifier] NULL,     [WorkShiftCD] [nvarchar](10) NULL,     [WorkHours] [real] NULL,     [AbsenceCode] [varchar](25) NULL,     [PaymentType] [char](2) NULL,     [CategoryID] [int] NULL,     [Year]  AS (datepart(year,[TimeSheetDate])),  CONSTRAINT [PK_WorkOut] PRIMARY KEY CLUSTERED  (     [WorkOutID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]  ALTER TABLE [dbo].[WorkOut] ADD   CONSTRAINT [DF__WorkOut__IsMainW__2C1E8537]  DEFAULT ((1)) FOR [IsMainWorkPlace]  ALTER TABLE [dbo].[WorkOut]  WITH CHECK ADD  CONSTRAINT [FK_WorkOut_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID])  ALTER TABLE [dbo].[WorkOut] CHECK CONSTRAINT [FK_WorkOut_Employee_EmployeeID] 

Query:

DECLARE @table_name SYSNAME SELECT @table_name = 'dbo.WorkOut'  DECLARE        @object_name SYSNAME     , @object_id INT  SELECT        @object_name = '[' + s.name + '].[' + o.name + ']'     , @object_id = o.[object_id] FROM sys.objects o WITH (NOWAIT) JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id] WHERE s.name + '.' + o.name = @table_name     AND o.[type] = 'U'     AND o.is_ms_shipped = 0  DECLARE @SQL NVARCHAR(MAX) = ''  ;WITH index_column AS  (     SELECT            ic.[object_id]         , ic.index_id         , ic.is_descending_key         , ic.is_included_column         , c.name     FROM sys.index_columns ic WITH (NOWAIT)     JOIN sys.columns c WITH (NOWAIT) ON ic.[object_id] = c.[object_id] AND ic.column_id = c.column_id     WHERE ic.[object_id] = @object_id ), fk_columns AS  (      SELECT            k.constraint_object_id         , cname = c.name         , rcname = rc.name     FROM sys.foreign_key_columns k WITH (NOWAIT)     JOIN sys.columns rc WITH (NOWAIT) ON rc.[object_id] = k.referenced_object_id AND rc.column_id = k.referenced_column_id      JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = k.parent_column_id     WHERE k.parent_object_id = @object_id ) SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((     SELECT CHAR(9) + ', [' + c.name + '] ' +          CASE WHEN c.is_computed = 1             THEN 'AS ' + cc.[definition]              ELSE UPPER(tp.name) +                  CASE WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')                        THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'                      WHEN tp.name IN ('nvarchar', 'nchar', 'ntext')                        THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS VARCHAR(5)) END + ')'                      WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')                         THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'                      WHEN tp.name = 'decimal'                         THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'                     ELSE ''                 END +                 CASE WHEN c.collation_name IS NOT NULL THEN ' COLLATE ' + c.collation_name ELSE '' END +                 CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END +                 CASE WHEN dc.[definition] IS NOT NULL THEN ' DEFAULT' + dc.[definition] ELSE '' END +                  CASE WHEN ic.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(ic.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(ic.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END          END + CHAR(13)     FROM sys.columns c WITH (NOWAIT)     JOIN sys.types tp WITH (NOWAIT) ON c.user_type_id = tp.user_type_id     LEFT JOIN sys.computed_columns cc WITH (NOWAIT) ON c.[object_id] = cc.[object_id] AND c.column_id = cc.column_id     LEFT JOIN sys.default_constraints dc WITH (NOWAIT) ON c.default_object_id != 0 AND c.[object_id] = dc.parent_object_id AND c.column_id = dc.parent_column_id     LEFT JOIN sys.identity_columns ic WITH (NOWAIT) ON c.is_identity = 1 AND c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id     WHERE c.[object_id] = @object_id     ORDER BY c.column_id     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')     + ISNULL((SELECT CHAR(9) + ', CONSTRAINT [' + k.name + '] PRIMARY KEY (' +                      (SELECT STUFF((                          SELECT ', [' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END                          FROM sys.index_columns ic WITH (NOWAIT)                          JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = ic.[object_id] AND c.column_id = ic.column_id                          WHERE ic.is_included_column = 0                              AND ic.[object_id] = k.parent_object_id                               AND ic.index_id = k.unique_index_id                               FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''))             + ')' + CHAR(13)             FROM sys.key_constraints k WITH (NOWAIT)             WHERE k.parent_object_id = @object_id                  AND k.[type] = 'PK'), '') + ')'  + CHAR(13)     + ISNULL((SELECT (         SELECT CHAR(13) +              'ALTER TABLE ' + @object_name + ' WITH'              + CASE WHEN fk.is_not_trusted = 1                  THEN ' NOCHECK'                  ELSE ' CHECK'                END +                ' ADD CONSTRAINT [' + fk.name  + '] FOREIGN KEY('                + STUFF((                 SELECT ', [' + k.cname + ']'                 FROM fk_columns k                 WHERE k.constraint_object_id = fk.[object_id]                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')                + ')' +               ' REFERENCES [' + SCHEMA_NAME(ro.[schema_id]) + '].[' + ro.name + '] ('               + STUFF((                 SELECT ', [' + k.rcname + ']'                 FROM fk_columns k                 WHERE k.constraint_object_id = fk.[object_id]                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')                + ')'             + CASE                  WHEN fk.delete_referential_action = 1 THEN ' ON DELETE CASCADE'                  WHEN fk.delete_referential_action = 2 THEN ' ON DELETE SET NULL'                 WHEN fk.delete_referential_action = 3 THEN ' ON DELETE SET DEFAULT'                  ELSE ''                END             + CASE                  WHEN fk.update_referential_action = 1 THEN ' ON UPDATE CASCADE'                 WHEN fk.update_referential_action = 2 THEN ' ON UPDATE SET NULL'                 WHEN fk.update_referential_action = 3 THEN ' ON UPDATE SET DEFAULT'                   ELSE ''                END              + CHAR(13) + 'ALTER TABLE ' + @object_name + ' CHECK CONSTRAINT [' + fk.name  + ']' + CHAR(13)         FROM sys.foreign_keys fk WITH (NOWAIT)         JOIN sys.objects ro WITH (NOWAIT) ON ro.[object_id] = fk.referenced_object_id         WHERE fk.parent_object_id = @object_id         FOR XML PATH(N''), TYPE).value('.', 'NVARCHAR(MAX)')), '')     + ISNULL(((SELECT          CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END                  + ' NONCLUSTERED INDEX [' + i.name + '] ON ' + @object_name + ' (' +                 STUFF((                 SELECT ', [' + c.name + ']' + CASE WHEN c.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END                 FROM index_column c                 WHERE c.is_included_column = 0                     AND c.index_id = i.index_id                 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')'                   + ISNULL(CHAR(13) + 'INCLUDE (' +                      STUFF((                     SELECT ', [' + c.name + ']'                     FROM index_column c                     WHERE c.is_included_column = 1                         AND c.index_id = i.index_id                     FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '')  + CHAR(13)         FROM sys.indexes i WITH (NOWAIT)         WHERE i.[object_id] = @object_id             AND i.is_primary_key = 0             AND i.[type] = 2         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')     ), '')  PRINT @SQL --EXEC sys.sp_executesql @SQL 

Output:

CREATE TABLE [dbo].[WorkOut] (       [WorkOutID] BIGINT NOT NULL IDENTITY(1,1)     , [TimeSheetDate] DATETIME NOT NULL     , [DateOut] DATETIME NOT NULL     , [EmployeeID] INT NOT NULL     , [IsMainWorkPlace] BIT NOT NULL DEFAULT((1))     , [DepartmentUID] UNIQUEIDENTIFIER NOT NULL     , [WorkPlaceUID] UNIQUEIDENTIFIER NULL     , [TeamUID] UNIQUEIDENTIFIER NULL     , [WorkShiftCD] NVARCHAR(10) COLLATE Cyrillic_General_CI_AS NULL     , [WorkHours] REAL NULL     , [AbsenceCode] VARCHAR(25) COLLATE Cyrillic_General_CI_AS NULL     , [PaymentType] CHAR(2) COLLATE Cyrillic_General_CI_AS NULL     , [CategoryID] INT NULL     , [Year] AS (datepart(year,[TimeSheetDate]))     , CONSTRAINT [PK_WorkOut] PRIMARY KEY ([WorkOutID] ASC) )  ALTER TABLE [dbo].[WorkOut] WITH CHECK ADD CONSTRAINT [FK_WorkOut_Employee_EmployeeID] FOREIGN KEY([EmployeeID]) REFERENCES [dbo].[Employee] ([EmployeeID]) ALTER TABLE [dbo].[WorkOut] CHECK CONSTRAINT [FK_WorkOut_Employee_EmployeeID]  CREATE NONCLUSTERED INDEX [IX_WorkOut_WorkShiftCD_AbsenceCode] ON [dbo].[WorkOut] ([WorkShiftCD] ASC, [AbsenceCode] ASC) INCLUDE ([WorkOutID], [WorkHours]) 

Also check this article -

How to Generate a CREATE TABLE Script For an Existing Table: Part 1

Answer 2

do you mean you wish to create a TSQL script which generates a CREATE script, or use the Management tools in SQL SERVER Management Studio to generate a Create script?

If it's the latter, it's a simply matter of right-clicking a table, and selecting Script Table As -> Create To -> New Query Window.

If you want the whole database scripted, then right click the database and select Tasks--> Generate Scripts... and then follow the wizard

otherwise it's a matter of selecting all sorts of fun things out of the various system tables.