Copy Table DDL Between Servers

This script will allow you to specify a linked server name, a remote database on that linked server, and a local database name in which to copy all the tables from on server onto another.

[cc lang=”sql”]

CREATE PROCEDURE [dba].[pr_refresh_create_tables]
@linked_server varchar(100),
@source_db varchar(100),
@target_db varchar(100),
@debug_mode int = 0
AS
— dba.pr_refresh_create_tables ‘10.10.60.58’, ‘yourdbname’, ‘targetdb’, 0
— SELECT * FROM admindb.dbo.refresh_table_columns
BEGIN

DECLARE @SQL nvarchar(max);
DECLARE @SQLColumnList nvarchar(max);
DECLARE @SQLSelectColumnList nvarchar(max);
DECLARE @CRLF nvarchar(2);
DECLARE @Tab nvarchar(1);
DECLARE @RecordKeyName nvarchar(50);
DECLARE @RecordKeyDataType nvarchar(50);
DECLARE @TableID int;
DECLARE @TableCount int;
DECLARE @source varchar(max) = ‘[‘ + @linked_server + ‘].[‘ + @source_db + ‘]’

DECLARE @TableName varchar(255);

DECLARE @TableList TABLE (
TableID int IDENTITY(1, 1) NOT NULL,
TableName varchar(255)
);

IF OBJECT_ID(N’admindb.dbo.refresh_table_columns_tmp’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns_tmp
IF OBJECT_ID(N’admindb.dbo.refresh_table_columns’) IS NOT NULL DROP TABLE admindb.dbo.refresh_table_columns
IF OBJECT_ID(N’admindb.tmp.refresh_create_tables’) IS NOT NULL DROP TABLE admindb.tmp.refresh_create_tables

CREATE TABLE admindb.dbo.refresh_table_columns_tmp (
RowID int NOT NULL,
TableName varchar(255),
ColumnName varchar(255),
IsIdentity bit,
DataType varchar(50),
IsDataTypeCast bit,
IsNullable bit,
IsLast bit
);

CREATE TABLE admindb.tmp.refresh_create_tables (
ID INT identity(1,1),
table_name varchar(255),
create_table varchar(max)
)

SET NOCOUNT ON;

SET @CRLF = CHAR(13) + CHAR(10);
SET @Tab = CHAR(9);

SET @sql =
‘INSERT INTO admindb.dbo.refresh_table_columns_tmp (
RowID,
TableName,
ColumnName,
IsIdentity,
DataType,
IsDataTypeCast,
IsNullable
)
SELECT
RowID = ROW_NUMBER() OVER(PARTITION BY t.TableName
ORDER BY t.IsIdentity DESC, t.ORDINAL_POSITION),
TableName,
ColumnName,
IsIdentity,
DataType,
IsDataTypeCast,
IsNullable
FROM (
SELECT DISTINCT
TableName = c.TABLE_NAME,
ColumnName = c.COLUMN_NAME,
IsIdentity = sc.is_identity,
DataType = ””
+ CASE
WHEN LOWER(c.DATA_TYPE) IN (”numeric”, ”decimal”, ”real”)
THEN LOWER(c.DATA_TYPE) + ”(” + CAST(c.NUMERIC_PRECISION AS VARCHAR(10)) + ”, ”
+ CAST(c.NUMERIC_SCALE AS VARCHAR(10)) + ”)”
WHEN LOWER(c.DATA_TYPE) = ”ntext” THEN ”nvarchar”
WHEN LOWER(c.DATA_TYPE) = ”text” THEN ”varchar”
WHEN LOWER(c.DATA_TYPE) = ”xml” THEN ”varchar”
ELSE LOWER(c.DATA_TYPE) END
+ CASE
WHEN LOWER(c.DATA_TYPE) IN (”ntext”, ”text”, ”xml”) THEN ”(MAX)”
WHEN st.collation_name IS NOT NULL
OR LOWER(c.DATA_TYPE) IN (”binary”, ”varbinary”) THEN ”(”
+ REPLACE(CAST(c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(10)), ”-1”, ”MAX”) + ”)”
ELSE ”” END,
IsDataTypeCast = CASE WHEN LOWER(c.DATA_TYPE) IN (”ntext”, ”text”, ”xml”) THEN 1 ELSE 0 END,
IsNullable = CASE c.IS_NULLABLE WHEN ”NO” THEN 0 ELSE 1 END,
ORDINAL_POSITION = c.ORDINAL_POSITION
FROM admindb.[tmp].[refresh_table_schema_list] t WITH (NOLOCK)
JOIN ‘ + @source + ‘.INFORMATION_SCHEMA.COLUMNS c WITH (NOLOCK)
ON t.table_name = c.TABLE_NAME
JOIN ‘ + @source + ‘.sys.objects so WITH (NOLOCK)
ON so.name = t.table_name
JOIN ‘ + @source + ‘.sys.columns sc WITH (NOLOCK)
ON so.object_id = sc.object_id
AND c.COLUMN_NAME = sc.[Name]
JOIN ‘ + @source + ‘.sys.types st WITH (NOLOCK)
ON COALESCE(c.DOMAIN_NAME, c.DATA_TYPE) = st.[Name]
WHERE LOWER(c.DATA_TYPE) NOT IN (”image”) ) t’

exec(@sql)

SELECT DISTINCT *
INTO admindb.dbo.refresh_table_columns
FROM admindb.dbo.refresh_table_columns_tmp

INSERT INTO @TableList(TableName)
SELECT DISTINCT TableName
FROM admindb.dbo.refresh_table_columns

SET @TableCount = (SELECT COUNT(DISTINCT TableName) FROM admindb.dbo.refresh_table_columns);
SET @TableID = 1;

UPDATE rtc
SET IsLast = 1
FROM admindb.dbo.refresh_table_columns rtc
JOIN (
SELECT TableName, ColumnName, RowID, MaxRow = MAX(RowID) OVER (PARTITION BY TableName)
FROM admindb.dbo.refresh_table_columns r
) r1
ON rtc.TableName = r1.TableName
AND rtc.ColumnName = r1.ColumnName
AND r1.MaxRow = rtc.RowID

SET @TableID = 1;
WHILE @TableID <= @TableCount BEGIN SET @TableName = (SELECT TableName FROM @TableList tl WHERE TableID = @TableID AND EXISTS (SELECT 1 FROM admindb.dbo.refresh_table_columns WHERE TableName = tl.TableName)); -- Determine Unique Field SELECT @RecordKeyName = ColumnName, @RecordKeyDataType = DataType FROM admindb.dbo.refresh_table_columns WHERE TableName = @TableName AND (IsIdentity = 1 OR ColumnName = @TableName + 'ID' OR ColumnName = @TableName + '_id'); -- Build Column Lists SET @SQLColumnList = ''; SELECT @SQLColumnList = @SQLColumnList + '^^^[' + ColumnName + '], ' + @CRLF FROM admindb.dbo.refresh_table_columns WHERE TableName = @TableName ORDER BY RowID; SET @SQLSelectColumnList = ''; SELECT @SQLSelectColumnList = @SQLSelectColumnList + CASE IsDataTypeCast WHEN 1 THEN '^^^[' + ColumnName + '] = CAST([' + ColumnName + '] AS '+ DataType + '), ' ELSE '^^^[' + ColumnName + '], ' END + @CRLF FROM admindb.dbo.refresh_table_columns WHERE TableName = @TableName ORDER BY RowID; -- Create Target Table SET @SQL = '-- Target Table ' + @CRLF + 'IF NOT EXISTS (SELECT * FROM ' + @target_db + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''' + @TableName + ''') ' + @CRLF + 'BEGIN ' + @CRLF + @Tab + 'CREATE TABLE ' + @target_db + '.[dbo].[' + @TableName + '] ( ' + @CRLF; SELECT @SQL = @SQL + @Tab + @Tab + '[' + ColumnName + '] ' + DataType + ' ' + CASE IsIdentity WHEN 1 THEN 'IDENTITY(1, 1) ' ELSE '' END + CASE WHEN IsNullable = 0 THEN (CASE WHEN IsLast = 1 THEN 'NOT NULL ' ELSE 'NOT NULL, ' END) ELSE (CASE WHEN IsLast = 1 THEN 'NULL ' ELSE 'NULL, ' END) END + @CRLF FROM admindb.dbo.refresh_table_columns WHERE TableName = @TableName ORDER BY RowID; SET @SQL = @SQL + @Tab + @Tab + '); ' + @CRLF + 'END ' + @CRLF INSERT INTO admindb.tmp.refresh_create_tables (table_name, create_table) SELECT @tableName, @SQL IF @debug_mode = 0 BEGIN EXEC(@SQL); END ELSE BEGIN SELECT @SQL END SET @TableID = @TableID + 1; END END [/cc]