Copy Table

,

Hi friends,

This script will copy tables from one schema to another schema.

First execute “CREATE OBJECTS SCRIPT” script for create a testing environment and then execute above script. This script will copy tables from one schema to another schema. And finally don’t forget to execute the “DROP or CLEAR OBJECTS SCRIPT”, which is in the end of script.

I hope this will help you. Your comments are most welcomed.

Keep Learning, Keep Growing!!!

Thanks

Vinay Kumar

http://growwithsql.blogspot.in/

USE TEMPDB
GO

SET NOCOUNT ON
GO    

DECLARE @SqlString nvarchar(max) = ''
DECLARE @CopyFromSchema Varchar(50)
DECLARE @CopyToSchema Varchar(200)
DECLARE @ObjectName Varchar(100)

---- Set The Paramenter
---- Now you can pass multiple parameters in @ObjectName
SET @CopyFromSchema = 'Marketing'
SET @CopyToSchema = 'Sales, Account, Economics, Employee, Manager'
SET @ObjectName = 'Table_1, Table_2, Table_3'


DECLARE @TotalSchema INT
DECLARE @TotalObject INT  
DECLARE @SchemaCount INT
DECLARE @ObjectCount INT
DECLARE @XML XML
DECLARE @Flag INT
DECLARE @SchemaName Varchar(100)
DECLARE @CurrentObjectName NVarchar(max)
DECLARE @ObjectsWithSchema NVarchar(max)
DECLARE @ObjectType varchar(100)
DECLARE @Count_Total int = -1
DECLARE @Count_Current int = 1
DECLARE @Count_String Varchar(max) = ''

DECLARE @SchemaList Table (ID INT Identity(1,1), SchemaName Varchar(200))
DECLARE @ObjectList Table (CreateOrder INT , DropOrder INT , ObjectName Varchar(200), ObjectType char(100), ObjectCreateDate Datetime, ObjectDefinitation Datetime)

SET @SqlString = ''
SET @SchemaName = ''
SET @SchemaCount = 1
SET @ObjectCount = 1
SET @Flag = 0

---- Get All Schema List
SET @XML = N'<root><Schema>' + replace(@CopyToSchema,',','</Schema><Schema>') + '</Schema></root>'
INSERT INTO @SchemaList
SELECT RTRIM(LTRIM(t.value('.','varchar(100)'))) from @XML.nodes('//root/Schema') as a(t)
SELECT @TotalSchema = max(ID) from @SchemaList

---- Get All Object List
SET @XML = N'<root><Schema>' + replace(@ObjectName,',','</Schema><Schema>') + '</Schema></root>'
INSERT INTO @ObjectList ( ObjectName )
SELECT RTRIM(LTRIM(t.value('.','varchar(100)'))) from @XML.nodes('//root/Schema') as a(t)


SET @ObjectsWithSchema = ''
select @ObjectsWithSchema=@ObjectsWithSchema+'Object_ID(''['+@CopyFromSchema+'].'+OBJECTNAME+'''),' from @ObjectList
select @ObjectsWithSchema=SUBSTRING(@ObjectsWithSchema,1,LEN(@ObjectsWithSchema)-1)


---- Check @CopyFromSchema Variable 
IF NOT EXISTS ( SELECT 1 FROM sys.schemas WHERE NAME = @CopyFromSchema)
BEGIN
	RAISERROR ('SCHEMA NOT FOUND. PLEASE CHECK "@CopyFromSchema" Parameter.',16,1)
	RETURN
END

---- Check @CopyToSchema Variable 
IF ((SELECT COUNT(*) FROM sys.schemas WHERE name IN (SELECT SchemaName FROM  @SchemaList))!= @TotalSchema)
BEGIN
	RAISERROR ('ONE OR MORE SCHEMA NOT FOUND. PLEASE CHECK "@CopyToSchema" Parameter.',16,1)
	RETURN
END

---- Check @ObjectName Variable 
SET @SqlString = 'SELECT @Flag_OUT =  Count(*) FROM sys.objects WHERE [object_id] IN ('+@ObjectsWithSchema+')'
EXEC Sp_executesql @SqlString,N'@Flag_OUT int OUTPUT',@Flag_OUT = @Flag OUTPUT


IF (@Flag!=@TotalObject)
BEGIN
	RAISERROR('SOME OBJECT(S) NOT FOUND. PLEASE CHECK "@ObjectName" Parameter.',16,1)
	RETURN
END


---- Update @ObjectList Table

UPDATE OBJ_LST SET
	OBJ_LST.ObjectType = ALL_Obj.type_desc, 
	OBJ_LST.ObjectCreateDate = ALL_Obj.create_date
FROM @ObjectList OBJ_LST
INNER JOIN SYS.all_objects ALL_Obj
on ALL_Obj.name = OBJ_LST.ObjectName AND SCHEMA_NAME(ALL_Obj.SCHEMA_ID)= @CopyFromSchema


UPDATE OBJ_LST SET OBJ_LST.CreateOrder = Obj_Rank_Table.CreateOrder,
			       OBJ_LST.DropOrder = Obj_Rank_Table.DropOrder
FROM @ObjectList OBJ_LST
INNER JOIN
(
	SELECT ROW_NUMBER() OVER(ORDER BY ObjectCreateDate ASC) AS CreateOrder,
		   ROW_NUMBER() OVER(ORDER BY ObjectCreateDate DESC) AS DropOrder,
		   ObjectName, ObjectType FROM @ObjectList
) AS Obj_Rank_Table
ON OBJ_LST.ObjectName = Obj_Rank_Table.ObjectName 
AND OBJ_LST.ObjectType = Obj_Rank_Table.ObjectType

SELECT @TotalObject = max(CreateOrder) from @ObjectList


IF EXISTS(SELECT * FROM tempdb.sys.tables where Object_ID = OBJECT_ID('tempdb.dbo.Table_Col_String'))  
	DROP TABLE [tempdb].[dbo].[Table_Col_String]

IF EXISTS(SELECT * FROM tempdb.sys.tables where Object_ID = OBJECT_ID('tempdb.dbo.Table_Index_String'))  
	DROP TABLE [tempdb].[dbo].[Table_Index_String]

IF EXISTS(SELECT * FROM tempdb.sys.tables where Object_ID = OBJECT_ID('tempdb.dbo.Table_Const_String'))  
	DROP TABLE [tempdb].[dbo].[Table_Const_String]


CREATE TABLE [tempdb].[dbo].[Table_Col_String]
(
	[TableName] [sysname] NOT NULL,
	[ColumnName] [sysname] NULL,
	[column_id] [int] NOT NULL,
	[Column_Text] [nvarchar](max) NULL,
) 


CREATE TABLE [tempdb].[dbo].[Table_Index_String]
(
	[Idx_Column_Order] [bigint] NULL,
	[SchemaName] [nvarchar](128) NULL,
	[TableName] [sysname] NOT NULL,
	[ConstraintName] [sysname] NULL,
	[index_id] [int] NOT NULL,
	[type_desc] [nvarchar](60) NULL,
	[is_primary_key] [bit] NULL,
	[is_unique_constraint] [bit] NULL,
	[index_column_id] [int] NULL,
	[column_id] [int] NULL,
	[key_ordinal] [tinyint] NULL,
	[is_descending_key] [bit] NULL,
	[is_included_column] [bit] NULL,
	[ColumnName] [sysname] NULL,
	[Index_String] [nvarchar](max) NULL
) 


CREATE TABLE [tempdb].[dbo].[Table_Const_String]
(
	[SchemaName] [nvarchar](128) NULL,
	[TableName] [sysname] NULL,
	[Const_ID] [int] NOT NULL,
	[Constraint_String] [nvarchar](max) NULL
)



---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- GET DETIALS OF TABLE COLUMNS. LIKE DATA TYPE, SIZE
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SET @SqlString = '
WITH TableColumns
AS
(
	SELECT TAB.[name] AS TableName, TAb.object_id,
	Col.[Name] AS ColumnName, Col.[column_id], Col.[user_type_id], Col.[max_length], Col.[precision], Col.[scale], 
	Col.[is_nullable], Col.[is_identity], Col.[is_computed], Col.[default_object_id], Col.[is_sparse], 
	TYP.[name] AS DataType,TAB.create_date, 
	idColumns.seed_value, idColumns.increment_value,
	COM_COL.[definition]
	 FROM SYS.COLUMNS COL
	INNER JOIN SYS.TYPES TYP
	ON COL.user_type_id = TYP.user_type_id
	INNER JOIN SYS.TABLES TAB
	ON TAB.object_id = COL.object_id
	LEFT JOIN sys.identity_columns idColumns
	ON idColumns.object_id = Col.object_id AND Col.is_identity = 1
	LEFT JOIN sys.computed_columns COM_COL
	ON COL.object_id = COM_COL.object_id AND COl.is_computed = 1
	WHERE Schema_Name(TAB.Schema_ID) ='''+@CopyFromSchema+'''
	AND TAB.name in ('''+REPLACE(REPLACE(@ObjectName,' ',''),',',''',''')+''')

)
INSERT INTO [tempdb].[dbo].[Table_Col_String]
SELECT  TableName,ColumnName,column_id
, ''[''+ColumnName+''] ''+ 
CASE WHEN is_computed = 1 Then ''AS (''+[definition]+'')'' 
ELSE '' [''+ DataType +''] '' END +
CASE is_identity WHEN 1 THEN ''Identity (''+Convert(Varchar(10),increment_value)+'',''+Convert(Varchar(10),seed_value)+'')'' ELSE '''' END +
CASE 
	
	WHEN DataType in (''binary'',''char'') Then ''(''+Convert(Varchar(10),max_length)+'')'' 
	WHEN DataType in (''nchar'') Then ''(''+Convert(Varchar(10),(max_length/2))+'')''  
	WHEN DataType in (''nvarchar'') AND max_length != -1 Then ''(''+Convert(Varchar(10),(max_length/2))+'')''  
	WHEN DataType in (''nvarchar'') AND max_length = -1  Then ''(MAX)'' 
	WHEN DataType in (''varbinary'', ''varChar'') AND max_length = -1 Then ''(MAX)'' 
	WHEN DataType in (''varbinary'', ''varChar'') AND max_length != -1 Then ''(''+Convert(Varchar(10),max_length)+'')''
	WHEN DataType in (''decimal'',''numeric'') Then ''(''+Convert(Varchar(10),[precision])+'', ''++Convert(Varchar(10),(scale))+'')'' 
	ELSE ''''
 END +
CASE is_sparse WHEN 1 THEN '' SPARSE ''ELSE '''' END +
CASE WHEN IS_computed = 1 THEN '''' WHEN Is_Nullable = 1 THEN '' NULL '' ELSE '' NOT NULL '' END AS Column_Text
FROM TableColumns
ORDER BY create_date,column_id'
EXEC (@sqlString)

---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- GET DETIALS OF TABLE PRIMARY KEY, UNIQUE KEY AND INDEX PART -I
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SET @SqlString = '
;WITH IndexString
AS
(
	SELECT 
	--TAb.object_id,Tab.name,
	--idx.*,IDX_COL.*, Col.NAME 
	Schema_Name(TAB.schema_id) AS SchemaName,
	TAB.name AS TableName,idx.name AS ConstraintName,
	IDX.index_id,Idx.type_desc,idx.is_primary_key,idx.is_unique_constraint,
	IDX_Col.index_column_id, IDX_COL.column_id,IDX_COL.key_ordinal,IDX_COL.is_descending_key, IDX_col.is_included_column,
	COL.Name AS ColumnName
	FROM SYS.indexes IDX
	INNER JOIN SYS.TABLES TAB
	ON IDX.object_id = TAB.object_id
	LEFT JOIN SYS.index_columns IDX_COL
	ON IDX.object_id = IDX_COL.object_id
	and IDX.index_id = IDX_COL.index_id
	LEFT JOIN SYS.COLUMNS COL
	ON IDX_COL.object_id = COL.object_id 
	AND IDX_COL.column_id = COL.column_id 
)
INSERT INTO [tempdb].[dbo].[Table_Index_String]
SELECT DISTINCT 
DENSE_RANK() OVER(Partition By TableName, index_id ORDER BY TableName, index_id, key_ordinal, is_included_column), * ,''''
FROM IndexString 
WHERE ConstraintName IS NOT NULL
AND SchemaName =''Marketing''
AND TableName in ('''+REPLACE(REPLACE(@ObjectName,' ',''),',',''',''')+''')'
EXEC (@sqlString)

---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- GET DETIALS OF TABLE PRIMARY KEY, UNIQUE KEY AND INDEX PART -II
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SET @SqlString = '
UPDATE IndexString SET [Index_String] = 
''
			IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = ''''IDX_''+Convert(Varchar(10),index_id)+''_''+type_desc+''_''+SchemaName+''_''+TableName  COLLATE SQL_Latin1_General_CP1_CI_AS +'''''') ''+
				CASE WHEN is_primary_key = 1 THEN ''
					ALTER TABLE [''+SchemaName+''].[''+TableName+'']''+'' ADD PRIMARY KEY ''+type_desc+'' (''
					+ STUFF((SELECT '', [''+T2.ColumnName+'']''+ CASE WHEN T2.is_descending_key = 1 THEN '' DESC '' ELSE '' ASC '' END FROM [tempdb].[dbo].[Table_Index_String] T2 
						WHERE IndexString.SchemaName = T2.SchemaName AND IndexString.TableName = T2.TableName 
							AND IndexString.index_id = T2.index_id  AND T2.is_included_column = 0 FOR XML PATH('''')),1,2,'''') +'') '' 

					 WHEN is_unique_constraint = 1 THEN ''
					ALTER TABLE [''+SchemaName+''].[''+TableName+'']''+'' ADD UNIQUE ''+type_desc+'' (''
					+ STUFF((SELECT '', [''+T2.ColumnName+'']''+ CASE WHEN T2.is_descending_key = 1 THEN '' DESC '' ELSE '' ASC '' END FROM [tempdb].[dbo].[Table_Index_String] T2 
						WHERE IndexString.SchemaName = T2.SchemaName AND IndexString.TableName = T2.TableName 
							AND IndexString.index_id = T2.index_id  AND T2.is_included_column = 0 FOR XML PATH('''')),1,2,'''') +'') '' 	
					ELSE 
					''
					CREATE ''+type_desc+'' INDEX [IDX_''+Convert(Varchar(10),index_id)+''_''+type_desc+''_''+SchemaName+''_''+TableName  COLLATE SQL_Latin1_General_CP1_CI_AS +''] ON [''+SchemaName+''].[''+TableName+'']''+
'' (''+ STUFF((SELECT '', [''+T2.ColumnName+'']''+ CASE WHEN T2.is_descending_key = 1 THEN '' DESC '' ELSE '' ASC '' END FROM [tempdb].[dbo].[Table_Index_String] T2 
	WHERE IndexString.SchemaName = T2.SchemaName AND IndexString.TableName = T2.TableName 
	AND IndexString.index_id = T2.index_id  AND T2.is_included_column = 0 FOR XML PATH('''')),1,2,'''') +'')''  
END + '' '' +
CASE WHEN is_included_column = 1 THEN
'' INCLUDE (''
			+ STUFF((SELECT '', [''+T2.ColumnName+'']'' FROM [tempdb].[dbo].[Table_Index_String] T2 
				WHERE IndexString.SchemaName = T2.SchemaName AND IndexString.TableName = T2.TableName 
					AND IndexString.index_id = T2.index_id  AND T2.is_included_column = 1 FOR XML PATH('''')),1,2,'''') +'') 
					''
	ELSE ''
	''
END 
FROM [tempdb].[dbo].[Table_Index_String]AS IndexString '
EXEC (@sqlString)

---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- GET DETIALS OF TABLE CONSTRAINTS
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SET @SqlString = '

WITH TAB_Const
AS
(
	select 
	ALL_Const.[constid], ALL_Const.[id] AS TABLE_ID, ALL_Const.[Colid],
	SCHEMA_NAME(TAB.schema_id) AS SchemaName, TAB.name AS TableName,COL.name AS ColumnName,
	ISNULL(ISNULL(D_Const.type_desc,C_Const.type_desc),F_Const.type_desc) AS Const_Type,
	ISNULL(ISNULL(D_Const.[name] ,C_Const.[name]),F_Const.[name] ) AS Const_Name,
	ISNULL(D_Const.[definition] ,C_Const.[definition]) AS Const_Definition
	FROM sysconstraints ALL_Const
	LEFT JOIN sys.default_constraints D_Const
	ON ALL_Const.constid = D_Const.object_id
	LEFT JOIN sys.check_constraints C_Const
	ON ALL_Const.constid = C_Const.object_id
	LEFT JOIN Sys.all_objects F_Const
	ON ALL_Const.constid = F_Const.object_id AND F_Const.[type] =''F''
	LEFT JOIN SYS.TABLES TAB
	ON TAB.object_id = ALL_Const.id
	LEFT JOIN SYS.columns COL
	ON COL.object_id = ALL_Const.id 
	AND COL.column_id  = ALL_Const.colid
),
foreignKey
AS 
(
	select FK.name,FK.type_desc ,FKC.*
	,(SELECT Name from sys.tables Tab where FKC.parent_object_id = Tab.object_id)  AS parent_object_Name
	,(SELECT Name from sys.Columns Col where FKC.parent_object_id = Col.object_id AND FKC.parent_column_id = Col.column_id)  AS parent_column_Name
	,(SELECT Name from sys.tables Tab where FKC.referenced_object_id = Tab.object_id)  AS referenced_object_Name
	,(SELECT Name from sys.Columns Col where FKC.referenced_object_id = Col.object_id AND FKC.referenced_column_id = Col.column_id)  AS referenced_object_Column_Name
	 from sys.foreign_keys FK
	LEFT JOIN sys.foreign_key_columns FKC
	ON FK.object_id = FKC.constraint_object_id
)
INSERT INTO [tempdb].[dbo].[Table_Const_String]
select DISTINCT TAB_Const.SchemaName,TAB_Const.TableName,
Dense_Rank() over(partition by TAB_Const.TableName order by TAB_Const.[constid]) AS Const_ID,
''
			IF NOT EXISTS (SELECT * FROM SYS.OBJECTS WHERE NAME = ''''''+Const_Type+''__''+Convert(Varchar(10),Dense_Rank() over(partition by TAB_Const.TableName order by TAB_Const.[constid])) +''_''+
SchemaName+''_''+TableName  COLLATE SQL_Latin1_General_CP1_CI_AS +'''''')'' +

CASE Const_Type 
WHEN ''FOREIGN_KEY_CONSTRAINT''
	THEN 
	 ''
					ALTER TABLE [''+SchemaName+''].[''+parent_object_Name+''] Add constraint ''+Const_Type+''__''+Convert(Varchar(10),Dense_Rank() over(partition by TAB_Const.TableName order by TAB_Const.[constid])) +''_''+
SchemaName+''_''+TableName  COLLATE SQL_Latin1_General_CP1_CI_AS+
	 '' FOREIGN KEY (''+STUFF((SELECT '', ''+parent_column_Name FROM foreignKey T2 WHERE T1.constraint_object_id = T2.constraint_object_id FOR XML PATH('''')),1,2,'''')+'')''+ 
	 '' REFERENCES [''+SchemaName +''].[''+ referenced_object_Name+''] (''+STUFF((SELECT '', ''+ referenced_object_Column_Name FROM foreignKey T2 WHERE T1.constraint_object_id = T2.constraint_object_id FOR XML PATH('''')),1,2,'''')+'')''
ELSE
	''
					ALTER TABLE [''+SchemaName+''].[''+TableName+''] ADD CONSTRAINT ''+CASE Const_Type 
	WHEN ''DEFAULT_CONSTRAINT'' THEN ''DEFAULT_CONSTRAINT__''
	WHEN ''CHECK_CONSTRAINT'' THEN ''CHECK_CONSTRAINT__''
	END
	+Convert(Varchar(10),Dense_Rank() over(partition by TAB_Const.TableName order by TAB_Const.[constid])) + ''_''+
	+SchemaName+''_''+TableName
	+CASE Const_Type 
	WHEN ''DEFAULT_CONSTRAINT'' THEN '' DEFAULT ('' + Const_Definition +'') FOR [''+ ColumnName +''] ''
	WHEN ''CHECK_CONSTRAINT'' THEN '' CHECK ('' + Const_Definition +'')''
	END
END AS Constraint_String
--INTO Table_Const_String
FROM TAB_Const
LEFT JOIN foreignKey T1
ON constraint_object_id = T1.constraint_object_id AND TAB_Const.Const_Name = T1.Name
WHERE TAB_Const.Const_Type IS NOT NULL
AND SchemaName ='''+@CopyFromSchema+'''
AND TableName in ('''+REPLACE(REPLACE(@ObjectName,' ',''),',',''',''')+''')'
EXEC (@sqlString)



SET @SqlString='
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
'


WHILE ( @TotalSchema >= @SchemaCount )
BEGIN
	SELECT @SchemaName = SchemaName FROM @SchemaList WHERE ID = (@SchemaCount)

	IF (@SchemaCount=1)
	BEGIN
		SET  @SqlString = @SqlString+'
		SELECT ''[ BEFORE ]'',Schema_ID,SCHEMA_NAME(Schema_ID) AS ''Schema_Name'',Name,create_date,modify_date 
		FROM SYS.OBJECTS WHERE Name IN ('''+REPLACE(REPLACE(@ObjectName,' ',''),',',''',''')+''') ORDER BY CREATE_DATE DESC, MODIFY_DATE DESC
		DECLARE @SQL nvarchar(max)
		'
	END
	
----- GENERATE DROP TABLE SCRIPT
			
	SET @ObjectCount = 1

	WHILE (@TotalObject >= @ObjectCount)
	BEGIN
		SELECT @CurrentObjectName = ObjectName , @ObjectType = ObjectType FROM @ObjectList WHERE DropOrder = (@ObjectCount)

		IF (@ObjectType in ('USER_TABLE'))
		BEGIN
			SET @SqlString=@SqlString+'

			 ----##################### ['+@SchemaName+'].['+@CurrentObjectName+']  #####################----

			IF EXISTS (SELECT 1 FROM sys.Objects WHERE NAME ='''+@CurrentObjectName+''' AND SCHEMA_ID= SCHEMA_ID('''+@SchemaName+'''))
				DROP TABLE ['+@SchemaName+'].['+@CurrentObjectName+']'
				
			SET @ObjectCount = @ObjectCount + 1
		END
	END


	----- GENERATE CREATE TABLE SCRIPT
	SET @ObjectCount = 1

	WHILE (@TotalObject >= @ObjectCount)
	BEGIN
		SELECT @CurrentObjectName = ObjectName , @ObjectType = ObjectType FROM @ObjectList WHERE CreateOrder = (@ObjectCount)

		IF (@ObjectType in ('USER_TABLE'))
		BEGIN
			SET @SqlString=@SqlString+'

			 ----$$$$$$$$$$$$$$$$$$$$$ ['+@SchemaName+'].['+@CurrentObjectName+']  $$$$$$$$$$$$$$$$$$$$$----

			CREATE TABLE ['+@SchemaName+'].['+@CurrentObjectName+'] (
				'

				SET @Count_Total = -1
				SET @Count_String = ''
				SET @Count_Current = 1 

				----+++++++++++++++++++++++++++++++++++++++++++++++++
				---- GENERATE TABLE COLUMNS
				----+++++++++++++++++++++++++++++++++++++++++++++++++
				SET @Count_Current = 1
				SELECT @Count_Total = -1 

				SELECT @Count_Total = MAX(column_id) From [tempdb].[dbo].[Table_Col_String] WHERE TableName = @CurrentObjectName

				WHILE (@Count_Current <= @Count_Total)
				BEGIN
					SELECT @Count_String = Column_Text FROM [tempdb].[dbo].[Table_Col_String] WHERE TableName = @CurrentObjectName AND column_id = @Count_Current

					SET @SqlString = @SqlString + @Count_String 

					IF (@Count_Current < @Count_Total)
						SET @SqlString = @SqlString + ' , '

					SET @Count_Current = @Count_Current + 1
				END
				SET @SqlString = @SqlString + ' ) 
				'




				----+++++++++++++++++++++++++++++++++++++++++++++++++
				---- GENERATE TABLE INDEX
				----+++++++++++++++++++++++++++++++++++++++++++++++++
				
				SET @Count_Total = -1
				SET @Count_String = ''
				SET @Count_Current = 1 

				SELECT @Count_Total = MAX(Index_id) From  [tempdb].[dbo].[Table_Index_String] WHERE TableName = @CurrentObjectName

				WHILE (@Count_Current <= @Count_Total)
				BEGIN
				
					SELECT TOP 1 @Count_String = Index_String FROM [tempdb].[dbo].[Table_Index_String] WHERE TableName = @CurrentObjectName AND Index_id = @Count_Current
					SET @SqlString = @SqlString + @Count_String 

					SET @Count_Current = @Count_Current + 1
				END

				SET @SqlString = REPLACE(REPLACE(@SqlString,'_'+@CopyFromSchema+'_','_'+@SchemaName+'_'),'['+@CopyFromSchema+']','['+@SchemaName+']')


	
				----+++++++++++++++++++++++++++++++++++++++++++++++++
				---- GENERATE TABLE CONSTRAINTS
				----+++++++++++++++++++++++++++++++++++++++++++++++++

				SET @Count_Total = -1
				SET @Count_String = ''
				SET @Count_Current = 1 

				SELECT @Count_Total = MAX(Const_ID) From  [tempdb].[dbo].[Table_Const_String] WHERE TableName = @CurrentObjectName

				WHILE (@Count_Current <= @Count_Total)
				BEGIN
					SELECT @Count_String = Constraint_String FROM [tempdb].[dbo].[Table_Const_String] WHERE TableName = @CurrentObjectName AND Const_ID = @Count_Current
					SET @SqlString = @SqlString + @Count_String 

					SET @Count_Current = @Count_Current + 1
				END

				SET @SqlString = REPLACE(REPLACE(@SqlString,'_'+@CopyFromSchema+'_','_'+@SchemaName+'_'),'['+@CopyFromSchema+']','['+@SchemaName+']')
		END

		SET @ObjectCount = @ObjectCount + 1
	END
	
	SET @SchemaCount = @SchemaCount + 1
END


SET @SqlString = @SqlString +'
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT>0 COMMIT TRANSACTION

SELECT ''[ AFTER ]'',Schema_ID,SCHEMA_NAME(Schema_ID) AS ''Schema_Name'',Name,create_date,modify_date 
FROM SYS.OBJECTS WHERE Name in ('''+REPLACE(REPLACE(@ObjectName,' ',''),',',''',''')+''')  ORDER BY CREATE_DATE DESC, MODIFY_DATE DESC
'

IF EXISTS(SELECT * FROM tempdb.sys.tables where Object_ID = OBJECT_ID('tempdb.dbo.Table_Col_String'))  
	DROP TABLE [tempdb].[dbo].[Table_Col_String]

IF EXISTS(SELECT * FROM tempdb.sys.tables where Object_ID = OBJECT_ID('tempdb.dbo.Table_Index_String'))  
	DROP TABLE [tempdb].[dbo].[Table_Index_String]

IF EXISTS(SELECT * FROM tempdb.sys.tables where Object_ID = OBJECT_ID('tempdb.dbo.Table_Const_String'))  
	DROP TABLE [tempdb].[dbo].[Table_Const_String]

--PRINT @SqlString
EXEC (@SqlString)

SET NOCOUNT OFF
GO    






----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- Script for create testing environment. 
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



/************************************ CREATE OBJECTS SCRIPT ************************************/


----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 1. SELECT TEMPDB
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
GO
USE tempdb
GO

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 2. Create schemaes
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE SCHEMA Marketing
GO
CREATE SCHEMA Sales
GO
CREATE SCHEMA Account
GO
CREATE SCHEMA Economics
GO
CREATE SCHEMA Employee
GO
CREATE SCHEMA Manager
GO

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 2. Check the schemaes
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT * FROM sys.schemas WHERE [schema_id]>4 and [schema_id]<16000
GO


----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 3 Create tables only in Marketing Schema
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE Marketing.Table_1 
	(
		T1_Col1_PK int IDENTITY (1,1) PRIMARY KEY,
		T1_Col2 bigint Unique,
		T1_Col3 binary,
		T1_Col4 bit DEFAULT (0),
		T1_Col5 char(10) CHECK (T1_Col5 != 'TEST'),
		T1_Col6 date,
		T1_Col7 datetime,
		T1_Col8 datetime2,
		T1_Col9 datetimeoffset,
		T1_Col10 decimal,
		T1_Col11 decimal (10,2),
		T1_Col12 float,
		T1_Col13 geography
	)
GO

CREATE TABLE Marketing.Table_2
	( 
		T2_Col1_FK int References Marketing.TABLE_1(T1_Col1_PK),
		T2_Col2 geometry,
		T2_Col3 hierarchyid,
		T2_Col4 image,
		T2_Col5 geometry,
		T2_Col6 money,
		T2_Col7 nchar (28),
		T2_Col8 ntext,
		T2_Col9 numeric,
		T2_Col10 nvarchar(100),
		T2_Col11 nvarchar(MAX),
		T2_Col12 real,
		T2_Col13 smalldatetime,
	)
GO

CREATE TABLE Marketing.Table_3
	( 

		T3_Col1 smallint,
		T3_Col2 smallmoney,
		T3_Col3_ComputedColumn AS (T3_Col1 + T3_Col2),
		T3_Col4 sql_variant,
		T3_Col5 sysname,
		T3_Col6 text,
		T3_Col7 time,
		T3_Col8 timestamp,
		T3_Col9 tinyint,
		T3_Col10 uniqueidentifier,
		T3_Col11 varbinary,
		T3_Col12 varchar (123),
		T3_Col13 varchar(MAX) SPARSE NULL,
		T3_Col14 xml
	)
GO

WAITFOR DELAY '00:00:05'
GO
CREATE NONCLUSTERED INDEX IDX_NCL_Marketing_TABLE_1 ON Marketing.TABLE_1 (T1_Col10, T1_Col11)
CREATE CLUSTERED INDEX IDX_NCL_Marketing_TABLE_2 ON Marketing.TABLE_2 (T2_Col1_FK, T2_Col12 DESC)
CREATE CLUSTERED INDEX IDX_CL_Marketing_TABLE_3 ON Marketing.TABLE_3 (T3_Col1)
CREATE NONCLUSTERED INDEX IDX_1_NCL_Marketing_TABLE_3 ON Marketing.TABLE_3 (T3_Col2)
CREATE NONCLUSTERED INDEX IDX_2_NCL_Marketing_TABLE_3 ON Marketing.TABLE_3 (T3_Col2) INCLUDE (T3_Col9, T3_Col14)
GO




----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 4. Check the table and views
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SELECT TOP 15 SCHEMA_ID, SCHEMA_NAME([schema_id]), name, create_date, TYPE FROM sys.objects WHERE TYPE IN ('U', 'V', 'P','FN') 
ORDER BY  CONVERT(datetime,convert(varchar(100),create_date,100)) DESC ,type,name 
GO



/************************************ DROP or CLEAR OBJECTS SCRIPT ************************************/


----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 1. Drop Tables
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP TABLE Marketing.Table_3
GO
DROP TABLE Marketing.Table_2
GO
DROP TABLE Marketing.Table_1
GO
	DROP TABLE Sales.Table_3
	GO
	DROP TABLE Sales.Table_2
	GO
	DROP TABLE Sales.Table_1
	GO
DROP TABLE Account.Table_3
GO
DROP TABLE Account.Table_2
GO
DROP TABLE Account.Table_1
GO
	DROP TABLE Economics.Table_3
	GO
	DROP TABLE Economics.Table_2
	GO
	DROP TABLE Economics.Table_1
	GO
DROP TABLE Employee.Table_3
GO
DROP TABLE Employee.Table_2
GO
DROP TABLE Employee.Table_1
GO
	DROP TABLE Manager.Table_3
	GO
	DROP TABLE Manager.Table_2
	GO
	DROP TABLE Manager.Table_1
	GO



----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----- 5. Drop Schema
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DROP SCHEMA Marketing
GO
DROP SCHEMA Sales
GO
DROP SCHEMA Account
GO
DROP SCHEMA Economics
GO
DROP SCHEMA Employee
GO
DROP SCHEMA Manager
GO

Rate

5 (1)

Share

Share

Rate

5 (1)