is there a way to create procedure that will automatically create one table with all structure (columns,constraints, PKs, FKs, indexes, etc.) based on 2 other tables?
For example:
Table a
id
a1
a2
Table b
id
b1
b2
b3
The procedure will receive 3 table names (a and b - input, c - output) and will create table c with columns id,a1,a2,b1,b2,b3, including all data - constraints, PKs, FKs, indexes, etc.
I've got more than 80 pairs of tables to create, so manually it'll take like forever...
UPDATE:
This script recreate DDL (columns, FK's, PK, non-clustered index's) from several tables in one. Possible it will be helpful for you -
DECLARE
@table1_name SYSNAME
, @table2_name SYSNAME
, @name_for_new_table SYSNAME
, @schema_for_new_table SYSNAME
SELECT
@table1_name = 'dbo.WorkOut'
, @table2_name = 'dbo.WorkPlace'
, @schema_for_new_table = 'dbo'
, @name_for_new_table = 'TableMerge'
DECLARE @SQL NVARCHAR(MAX) = ''
;WITH tbl AS
(
SELECT o.[object_id]
FROM sys.objects o WITH (NOWAIT)
WHERE SCHEMA_NAME(o.[schema_id]) + '.' + o.name IN (@table1_name, @table2_name)
AND o.[type] = 'U'
AND o.is_ms_shipped = 0
),
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] IN (SELECT t.[object_id] FROM tbl t)
),
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
)
SELECT @SQL = 'CREATE TABLE [' + @schema_for_new_table + '].[' + @name_for_new_table + ']' + CHAR(13) + '(' + CHAR(13) + STUFF((
SELECT DISTINCT CHAR(9) + ', [' + c.name + '] ' + UPPER(t.name) +
CASE WHEN t.name IN ('varchar', 'char', 'varbinary', 'binary', 'text')
THEN '(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CAST(c.max_length AS NVARCHAR(5)) END + ')'
WHEN t.name IN ('nvarchar', 'nchar', 'ntext')
THEN '(' + CASE WHEN c.max_length = -1 THEN N'MAX' ELSE CAST(c.max_length / 2 AS NVARCHAR(5)) END + ')'
WHEN t.name IN ('datetime2', 'time2', 'datetimeoffset')
THEN '(' + CAST(c.scale AS NVARCHAR(5)) + ')'
WHEN t.name = 'decimal'
THEN '(' + CAST(c.[precision] AS NVARCHAR(5)) + ',' + CAST(c.scale AS NVARCHAR(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 c.[definition] IS NOT NULL THEN ' DEFAULT' + c.[definition] ELSE '' END +
CASE WHEN c.is_identity = 1 THEN ' IDENTITY(' + CAST(ISNULL(c.seed_value, '0') AS CHAR(1)) + ',' + CAST(ISNULL(c.increment_value, '1') AS CHAR(1)) + ')' ELSE '' END + CHAR(13)
FROM (
SELECT
c.name
, c.user_type_id
, c.max_length
, c.[precision]
, c.scale
, dc.[definition]
, ic.seed_value
, ic.increment_value
, c.collation_name
, is_nullable = MIN(CAST(c.is_nullable AS TINYINT))
, is_identity = MIN(CAST(c.is_identity AS TINYINT))
FROM sys.columns c WITH (NOWAIT)
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] IN (SELECT o.[object_id] FROM tbl o)
GROUP BY
c.name
, c.user_type_id
, c.max_length
, c.[precision]
, c.scale
, dc.[definition]
, ic.seed_value
, ic.increment_value
, c.collation_name
) c
JOIN sys.types t WITH (NOWAIT) ON c.user_type_id = t.user_type_id
FOR XML PATH(N''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
+ ISNULL((SELECT CHAR(9) + ', CONSTRAINT [PK_' + @schema_for_new_table + '_' + @name_for_new_table + '] PRIMARY KEY (' +
(SELECT STUFF((
SELECT DISTINCT ', [' + c.name + ']' + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END
FROM sys.key_constraints k WITH (NOWAIT)
JOIN sys.index_columns ic WITH (NOWAIT) ON ic.[object_id] = k.parent_object_id AND ic.index_id = k.unique_index_id
JOIN sys.columns c WITH (NOWAIT) ON c.[object_id] = k.parent_object_id AND c.column_id = ic.column_id
WHERE k.[type] = 'PK'
AND ic.is_included_column = 0
AND k.parent_object_id IN (SELECT t.[object_id] FROM tbl t)
FOR XML PATH(N''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '')) + ')' + CHAR(13)), '')
+ ')' + CHAR(13)
+ ISNULL((SELECT (
SELECT CHAR(13) +
'ALTER TABLE [' + @schema_for_new_table + '].[' + @name_for_new_table + '] WITH'
+ CASE WHEN fk.is_not_trusted = 1
THEN ' NOCHECK'
ELSE ' CHECK'
END +
' ADD CONSTRAINT [FK_' + @schema_for_new_table + '_' + @name_for_new_table + '_' + fk.name + '] FOREIGN KEY('
+ STUFF((
SELECT ', [' + k.cname + ']'
FROM fk_columns k
WHERE k.constraint_object_id = fk.[object_id]
FOR XML PATH(N''), TYPE, ROOT).value('root[1]', '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(N''), TYPE, ROOT).value('root[1]', '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 [' + @schema_for_new_table + '].[' + @name_for_new_table + '] CHECK CONSTRAINT [FK_' + @schema_for_new_table + '_' + @name_for_new_table + '_' + 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 IN (SELECT t.[object_id] FROM tbl t)
FOR XML PATH(N''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')), '')
+ ISNULL(((SELECT
CHAR(13) + 'CREATE' + CASE WHEN i.is_unique = 1 THEN ' UNIQUE' ELSE '' END + ' NONCLUSTERED INDEX [IX_' + @schema_for_new_table + '_' + @name_for_new_table + '_' + i.name + '] ON [' + @schema_for_new_table + '].[' + @name_for_new_table + '] (' +
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(N''), TYPE, ROOT).value('root[1]', '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(N''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ')', '') + CHAR(13)
FROM sys.indexes i WITH (NOWAIT)
WHERE i.[object_id] IN (SELECT t.[object_id] FROM tbl t)
AND i.is_primary_key = 0
AND i.[type] = 2
FOR XML PATH(N''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)')
), '')
PRINT @SQL
--EXEC sys.sp_executesql @SQL
For example, this query generate this DDL:
CREATE TABLE dbo.TableMerge
(
AbsenceCode VARCHAR(25) COLLATE Cyrillic_General_CI_AS NULL
, CategoryID INT NULL
, DateOut DATETIME NOT NULL
, DepartmentUID UNIQUEIDENTIFIER NOT NULL
, EmployeeID INT NOT NULL
, IsMainWorkPlace BIT NOT NULL DEFAULT((1))
, PaymentType CHAR(2) COLLATE Cyrillic_General_CI_AS NOT NULL
, PositionID INT NOT NULL
, TariffScaleID INT NULL
, TeamUID UNIQUEIDENTIFIER NULL
, TimeSheetDate DATETIME NOT NULL
, WorkHours REAL NULL
, WorkOutID BIGINT NOT NULL IDENTITY(1,1)
, WorkPlaceUID UNIQUEIDENTIFIER NOT NULL
, WorkShiftCD NVARCHAR(20) COLLATE Cyrillic_General_CI_AS NULL
, CONSTRAINT [PK_dbo.TableMerge] PRIMARY KEY (WorkOutID ASC, WorkPlaceUID ASC)
)
If specify this tables as source:
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,
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]
CREATE TABLE dbo.[WorkPlace](
[WorkPlaceUID] [uniqueidentifier] NOT NULL,
[PositionID] [int] NOT NULL,
[DepartmentUID] [uniqueidentifier] NOT NULL,
[PaymentType] [char](2) NOT NULL,
[TariffScaleID] [int] NULL,
[CategoryID] [int] NULL,
CONSTRAINT [PK_WorkPlace] PRIMARY KEY CLUSTERED
(
[WorkPlaceUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]