REPLACE and STUFF functions

Hi Friends,

Today I am going to discuss two string functions STUFF and REPLACE.

We all know very well that whenever we add null in a string, it result null. It doesn't matter the position of NULL in string.

Look at below three statements. I added NULL in different select statement on different place. All three statements give result as NULL.

That why I call NULL as "DEFUSER". It’s only my own opinion.

 

 

Now, i am going to replace some words in a string with the help of REPLACE function.

Syntax

                REPLACE ('String_1', 'String_2' ,'String_3')

                                 

REPALCE function finds String_2 in String_1 and replaces it with String_3.

Check below link for further information.

http://msdn.microsoft.com/en-us/library/ms186862.aspx

Below, I have written down few select statements with REPLACE function below.

Statement 1. REPLACE function work perfectly and result as "ABCTT_%_TTEFG".

Statement 2. This statement give result as NULL", because REPLACE function gives NULL and when we add NULL with other string, it will result as NULL.

Statement 3. In this statement, we try to find "Y" in string “TT_D_TT”. But "Y" not found, so it's not replace any string and result as "ABCTT_D_TTEFG"

Statement 4. This statement returns "NULL" because in REPLACE function we use "NULL". It's surprising behavior of REPLACE function.

 

Now again, I am going to replace some words in a string. But this time I am using STUFF function.

Syntax

                STUFF ('String_1', [Start_Position], [Length], 'String_2')

STUFF function, first delete No of Character from Start point and then replace "String_2" in "String_1". Check below link for further information.

http://msdn.microsoft.com/en-in/library/ms188043.aspx

Below, I have written down few select statements with STUFF function.

Statement 1. In MS BOL (Microsoft Book online), If [Start_Position] or [Length] are NULL then STUFF function return NULL. So it results as NULL.

Statement 2. In MS BOL (Microsoft Book online), If [Start_Position] or [Length] are NULL then STUFF function return NULL. So it results as NULL.

Statement 3. STUFF function work perfectly and result as "ABCTT_%_TTEFG".

Statement 4. Here, STUFF function return String expect then NULL value. It's difference between REPLACE and STUFF function.

Now I am using these two functions in CONCAT function. CONCAT function is used to concatenate multiple strings in sql server.

Check out below statements.

Statement 1. In this, REPLACE function returns NULL. In CONCAT function, if value is NULL then it consider as blank value. So this statement returns "ABCEFG".

Statement 2. STUFF function return "TT__TT". So this statement returns "ABCTT__TTEFG"

Conclusion: - We need to take care of this behavior of STUFF function for future use.  It’s very important when we are spending our most of time playing with string.

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

Keep Learning, Keep Growing!!!

Thanks

Vinay Kumar

Generate Create Table Script By T-SQL

Hi friends,

This script will generate create table script for given tables.

First execute “CREATE OBJECTS SCRIPT” script for create a testing environment and then execute above script. This script will print the create table script. Now copy the script and execute it. And finally don’t forget 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

USE TEMPDB

GO

SET NOCOUNT ON

GO   

DECLARE @SchemaName Varchar(100)

DECLARE @ObjectName Varchar(100)

---- Set The Paramenter

---- Now you can pass multiple parameters in @ObjectName

SET @SchemaName = 'Marketing'

SET @ObjectName = 'Table_1, Table_2, Table_3'

DECLARE @SqlString nvarchar(max) = ''

DECLARE @SqlString_Temp nvarchar(max) = ''

DECLARE @TotalObject INT 

DECLARE @SchemaCount INT

DECLARE @ObjectCount INT

DECLARE @XML XML

DECLARE @Flag INT

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 @ObjectList Table (CreateOrder INT , DropOrder INT , ObjectName Varchar(200), ObjectType char(100), ObjectCreateDate Datetime, ObjectDefinitation Datetime)

SET @SqlString = ''

SET @SchemaCount = 1

SET @ObjectCount = 1

SET @Flag = 0

---- 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(''['+@SchemaName+'].'+OBJECTNAME+'''),' from @ObjectList

select @ObjectsWithSchema=SUBSTRING(@ObjectsWithSchema,1,LEN(@ObjectsWithSchema)-1)

---- Check @SchemaName Variable

IF NOT EXISTS ( SELECT 1 FROM sys.schemas WHERE NAME = @SchemaName)

BEGIN

       RAISERROR ('SCHEMA NOT FOUND. PLEASE CHECK "@SchemaName" 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)= @SchemaName

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) ='''+@SchemaName+'''

       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 ='''+@SchemaName+'''

AND TableName in ('''+REPLACE(REPLACE(@ObjectName,' ',''),',',''',''')+''')'

EXEC (@sqlString)

SET @SqlString='

SET XACT_ABORT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

'

       ----- 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_Temp = @SqlString_Temp + '

                      ----##################### ['+@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

             

              IF ( LEN(@SqlString + @SqlString_Temp) > 4000)

              BEGIN

             

                     PRINT @SqlString

                     SET @SqlString= ''

                     SET @SqlString= @SqlString_Temp

              END

              ELSE

              BEGIN

                     SET @SqlString = @SqlString + @SqlString_Temp

              END

              SET @SqlString_Temp = ''

       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_Temp = @SqlString_Temp + @Count_String

                                  IF (@Count_Current < @Count_Total)

                                         SET @SqlString_Temp = @SqlString_Temp + ' , '

                                  SET @Count_Current = @Count_Current + 1

                           END

                           SET @SqlString_Temp = @SqlString_Temp + ' )

                           '

                           IF ( LEN(@SqlString + @SqlString_Temp) > 4000)

                           BEGIN

                                  PRINT @SqlString

                                  SET @SqlString= ''

                                  SET @SqlString= @SqlString_Temp

                           END

                           ELSE

                           BEGIN

                                  SET @SqlString = @SqlString + @SqlString_Temp

                           END

                           SET @SqlString_Temp = ''

                           ----+++++++++++++++++++++++++++++++++++++++++++++++++

                           ---- 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_Temp = @SqlString_Temp + @Count_String

                                  SET @Count_Current = @Count_Current + 1

                           END

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

                          

                           IF ( LEN(@SqlString + @SqlString_Temp) > 4000)

                           BEGIN

                                  PRINT @SqlString

                                  SET @SqlString= ''

                                  SET @SqlString= @SqlString_Temp

                           END

                           ELSE

                           BEGIN

                                  SET @SqlString = @SqlString + @SqlString_Temp

                           END

                           SET @SqlString_Temp = ''

      

                           ----+++++++++++++++++++++++++++++++++++++++++++++++++

                           ---- 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_Temp = @SqlString_Temp + @Count_String

                                  SET @Count_Current = @Count_Current + 1

                           END

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

                           IF ( LEN(@SqlString + @SqlString_Temp) > 4000)

                           BEGIN

                                  PRINT @SqlString

                                  SET @SqlString= ''

                                  SET @SqlString= @SqlString_Temp

                           END

                           ELSE

                           BEGIN

                                  SET @SqlString = @SqlString + @SqlString_Temp

                           END

                           SET @SqlString_Temp = ''

              END

              SET @ObjectCount = @ObjectCount + 1

       END

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]

SET @SqlString = @SqlString +'

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

IF @@TRANCOUNT>0 COMMIT TRANSACTION'

PRINT @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

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

---- 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

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

----- 2. Drop Schema

----++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

DROP SCHEMA Marketing

GO