Create Table with 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 know most of people say that we can generate create table script through SSMS (Sql Server Management Studio) then why we go for this?
Frankly speaking, I have no answer for this. I just want to myself for learn some internal of sql and I learnt so much.
Here, I am just sharing this.
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 @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 
