Copy Sql Objects

/******************************************************************************************************************
Created on :: March 08, 2013
Created By :: Vinay Kumar
Description :: This script Copy Tables, Procedures, Functions and views from one schema to other schema.
******************************************************************************************************************/

USE TEMPDB
GO

SET NOCOUNT ON 
GO

DECLARE @CopyFromSchema Varchar(50)
DECLARE @CopyToSchema Varchar(200)
DECLARE @ObjectName Varchar(100)

---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- SET THE PARAMENTER
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SET @CopyFromSchema = 'Marketing'
SET @CopyToSchema = 'Sales, Account, Economics, Employee, Manager'
SET @ObjectName = 'Table_1, Table_2, Table_3, View_1, View_2, View_3, Proc_1, Proc_2, Proc_3, Func_1, Func_2, Func_3'


DECLARE @SqlString nvarchar(max) = ''
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
SET @ObjectCount = 1
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

---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- THIS IS ONLY FOR DROP TABLES
---++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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+']'
END

SET @ObjectCount = @ObjectCount + 1
END

SET @SchemaCount = @SchemaCount +1
END


SET @SchemaCount = 1

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

SET @ObjectCount = 1

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

IF (@ObjectType in ('VIEW','SQL_STORED_PROCEDURE','SQL_SCALAR_FUNCTION'))
BEGIN

SET @SqlString=@SqlString+'
IF EXISTS (SELECT 1 FROM sys.Objects WHERE NAME ='''+@CurrentObjectName+''' AND SCHEMA_ID= SCHEMA_ID('''+@SchemaName+'''))
BEGIN
DROP '+CASE @ObjectType WHEN 'VIEW' THEN 'VIEW' WHEN 'SQL_STORED_PROCEDURE' THEN 'PROCEDURE' WHEN 'SQL_SCALAR_FUNCTION' THEN 'FUNCTION' END
 +' ['+@SchemaName+'].['+@CurrentObjectName+']
END'

SET @SqlString=@SqlString+'
SELECT @SQL = REPLACE(REPLACE(OBJECT_DEFINITION (object_id('''+@CopyFromSchema+'.'+@CurrentObjectName+''')),''['+@CopyFromSchema+'].'',''['+@SchemaName+'].''),'''+@CopyFromSchema+'.'','''+@SchemaName+'.'')
exec sp_executeSQL @SQL
'
END
ELSE IF (@ObjectType = '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



----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 3 Create Views only in Marketing Schema
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
GO
CREATE VIEW Marketing.View_1
AS
SELECT [T1_Col1_PK]
      ,[T1_Col2]
      ,[T1_Col3]
      ,[T1_Col4]
      ,[T1_Col5]
      ,[T1_Col6]
      ,[T1_Col7]
      ,[T1_Col8]
      ,[T1_Col9]
      ,[T1_Col10]
      ,[T1_Col11]
      ,[T1_Col12]
  ,[T1_Col13]
  FROM [Marketing].[TABLE_1]
GO


CREATE VIEW Marketing.View_2
AS
SELECT [T2_Col1_FK]
      ,[T2_Col2]
      ,[T2_Col4]
      ,[T2_Col5]
      ,[T2_Col6]
      ,[T2_Col7]
      ,[T2_Col8]
      ,[T2_Col9]
      ,[T2_Col10]
      ,[T2_Col11]
      ,[T2_Col12]
  ,[T2_Col13]
  FROM [Marketing].[TABLE_2]
GO

GO
CREATE VIEW Marketing.View_3
AS
SELECT [T3_Col1]
      ,[T3_Col2]
      ,[T3_Col3_ComputedColumn]
      ,[T3_Col4]
      ,[T3_Col5]
      ,[T3_Col6]
      ,[T3_Col7]
      ,[T3_Col8]
      ,[T3_Col9]
      ,[T3_Col10]
      ,[T3_Col11]
      ,[T3_Col12]
  ,[T3_Col13]
  ,[T3_Col14]
  FROM [Marketing].[TABLE_3]
GO


----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 4 Create Procedure only in Marketing Schema
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
GO
CREATE PROCEDURE Marketing.Proc_1
AS 
SELECT * FROM [Marketing].View_1

GO
CREATE PROCEDURE Marketing.Proc_2 (@Proc2_Para1 int)
AS 
SELECT @Proc2_Para1, * FROM [Marketing].View_2

GO
CREATE PROCEDURE Marketing.Proc_3 (@Proc3_Para1 Varchar(max), @Proc3_Para2 datetime OUTPUT)

AS 
SELECT @Proc3_Para1,* FROM [Marketing].View_3
SET @Proc3_Para2 = GETDATE()
GO


----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 4 Create Function only in Marketing Schema
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
GO
CREATE FUNCTION Marketing.Func_1 ()
RETURNS INT
AS 
BEGIN
DECLARE @A int
SELECT @A = COUNT(*) FROM Marketing.TABLE_1

RETURN @A
END
GO

CREATE FUNCTION Marketing.Func_2 (@Func2_Para1 INT)
RETURNS INT
AS 
BEGIN
DECLARE @A int
SELECT @A = COUNT(*) FROM Marketing.TABLE_2

RETURN @A
END
GO

CREATE FUNCTION Marketing.Func_3 (@Func3_Para1 INT, @Func3_Para2 Varchar(100))
RETURNS INT
AS 
BEGIN
DECLARE @A int
SELECT @A = COUNT(*) FROM Marketing.TABLE_3

RETURN @A
END
GO


----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
---- 5. 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 Functions
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP FUNCTION Marketing.Func_3
GO
DROP FUNCTION Marketing.Func_2
GO
DROP FUNCTION Marketing.Func_1
GO
DROP FUNCTION Sales.Func_3
GO
DROP FUNCTION Sales.Func_2
GO
DROP FUNCTION Sales.Func_1
GO
DROP FUNCTION Account.Func_3
GO
DROP FUNCTION Account.Func_2
GO
DROP FUNCTION Account.Func_1
GO
DROP FUNCTION Economics.Func_3
GO
DROP FUNCTION Economics.Func_2
GO
DROP FUNCTION Economics.Func_1
GO
DROP FUNCTION Employee.Func_3
GO
DROP FUNCTION Employee.Func_2
GO
DROP FUNCTION Employee.Func_1
GO
DROP FUNCTION Manager.Func_3
GO
DROP FUNCTION Manager.Func_2
GO
DROP FUNCTION Manager.Func_1
GO

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----- 2. Drop Procedures 
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP PROCEDURE Marketing.Proc_3
GO
DROP PROCEDURE Marketing.Proc_2
GO
DROP PROCEDURE Marketing.Proc_1
GO
DROP PROCEDURE Sales.Proc_3
GO
DROP PROCEDURE Sales.Proc_2
GO
DROP PROCEDURE Sales.Proc_1
GO
DROP PROCEDURE Account.Proc_3
GO
DROP PROCEDURE Account.Proc_2
GO
DROP PROCEDURE Account.Proc_1
GO
DROP PROCEDURE Economics.Proc_3
GO
DROP PROCEDURE Economics.Proc_2
GO
DROP PROCEDURE Economics.Proc_1
GO
DROP PROCEDURE Employee.Proc_3
GO
DROP PROCEDURE Employee.Proc_2
GO
DROP PROCEDURE Employee.Proc_1
GO
DROP PROCEDURE Manager.Proc_3
GO
DROP PROCEDURE Manager.Proc_2
GO
DROP PROCEDURE Manager.Proc_1
GO

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----- 3. Drop Views
----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DROP VIEW Marketing.View_1
GO
DROP VIEW Marketing.View_2
GO
DROP VIEW Marketing.View_3
GO
DROP VIEW Sales.View_1
GO
DROP VIEW Sales.View_2
GO
DROP VIEW Sales.View_3
GO
DROP VIEW Account.View_1
GO
DROP VIEW Account.View_2
GO
DROP VIEW Account.View_3
GO
DROP VIEW Economics.View_1
GO
DROP VIEW Economics.View_2
GO
DROP VIEW Economics.View_3
GO
DROP VIEW Employee.View_1
GO
DROP VIEW Employee.View_2
GO
DROP VIEW Employee.View_3
GO
DROP VIEW Manager.View_1
GO
DROP VIEW Manager.View_2
GO
DROP VIEW Manager.View_3
GO

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
----- 4. 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