Get Table Script with Constraints , Index from Sql Server
This procedure will help to get the table script with constraints , index from Sql Server...
/*
declare @lsobjname as varchar(200),@lsobjscript as varchar(max)
set @lsobjname='gnmdcvsclient'
set @lsobjscript=''
exec UIGenTableScript @lsobjname,@lsobjscript out,' ; '
print @lsobjscript
*/
CREATE Proc [dbo].[UIGenTableScript](@lsTableName as Varchar(128),@lsOutScript as Nvarchar(max) out,@lsGo as varchar(10) = 'GO') as
declare @lsSql as Nvarchar(max),
@Table_Schema nvarchar(256),
@Table_Name nvarchar(256),
@ID int ,
@Name nvarchar(256),
@AnsiPaddingStatus bit ,
@Computed bit ,
@ComputedText nvarchar(max),
@DataType nvarchar(256),
@SystemType nvarchar(256),
@DataTypeSchema nvarchar(256),
@RowGuidCol bit ,
@Length int ,
@NumericPrecision int ,
@Identity bit ,
@IdentitySeed bigint ,
@IdentityIncrement bigint ,
@Collation nvarchar(256),
@NumericScale int ,
@Nullable bit ,
@Owner Nvarchar(100),
@IndexKeyType int,
@IsUnique bit,
@IsClustered bit,
@FileGroup nvarchar(256),
@columnname nvarchar(128),
@IsIncluded bit,
@Descending bit,
@DefaultValue nvarchar(256)
set @lsSql=''
Declare #ScriptTable cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Schema],
tbl.name AS [Name],
tbl.object_id AS [ID],
stbl.name AS [Owner]
FROM
sys.tables AS tbl
LEFT OUTER JOIN sys.data_spaces AS dstext ON tbl.lob_data_space_id = dstext.data_space_id
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))
WHERE
(tbl.name in (@lsTableName) /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY
[Schema] ASC,[Name] ASC
open #ScriptTable
fetch from #ScriptTable into @Table_Schema,@Table_Name,@ID,@Owner
While @@FETCH_STATUS =0
Begin
--set @lsSql = @lsSql + 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID([' + @Table_Schema + '].[' + @Table_Name + ']) AND TYPE in (''U'')) ' + CHAR(10)
--set @lsSql = @lsSql + 'BEGIN ' + CHAR(10)
set @lsSql = @lsSql + 'CREATE TABLE ' + @Table_Schema + '.' + @Table_Name + '(' + CHAR(10)
fetch from #ScriptTable into @Table_Schema,@Table_Name,@ID,@Owner
End
Close #ScriptTable
Deallocate #ScriptTable
Declare #ScriptColumns cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
clmns.column_id AS [ID],
clmns.name AS [Name],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
clmns.is_computed AS [Computed],
ISNULL(cc.definition,'') AS [ComputedText],
usrt.name AS [DataType],
ISNULL(baset.name, '') AS [SystemType],
sclmns.name AS [DataTypeSchema],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
CAST(CASE WHEN baset.name IN ('nchar', 'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS bigint) AS [IdentitySeed],
CAST(ISNULL(ic.increment_value,0) AS bigint) AS [IdentityIncrement],
ISNULL(clmns.collation_name, '') AS [Collation], CAST(clmns.scale AS int) AS [NumericScale],
clmns.is_nullable AS [Nullable]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0
)
and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
WHERE
(tbl.name in (@lsTableName) /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[ID] ASC
open #ScriptColumns
fetch from #ScriptColumns into @Table_Schema,@Table_Name,@ID,@Name,@AnsiPaddingStatus,@Computed,@ComputedText,@DataType,@SystemType,
@DataTypeSchema,@RowGuidCol,@Length,@NumericPrecision,@Identity,@IdentitySeed,@IdentityIncrement,@Collation,@NumericScale,@Nullable
While @@FETCH_STATUS =0
Begin
set @lsSql = @lsSql + SPACE(5) + @Name + ' ' + @DataType + ' ' +
Case when @DataType = 'decimal' then '(' + CONVERT(varchar(10),@NumericPrecision) + ',' + CONVERT(varchar(10),@NumericScale) + ')'
when @DataType in ('varchar','nvarchar','char') then '(' + case @Length when -1 then 'MAX' else convert(varchar(50),@Length) end + ')'
else '' end + ' ' + + case @Identity when 1 then 'IDENTITY(' + convert(varchar(10),@IdentitySeed) + ',' + convert(varchar(10),@IdentityIncrement) + ') ' else '' end +
--Identity added by samy on 22-02-2014
Case @Nullable when 1 then 'NULL' else 'NOT NULL' end + ',' + CHAR(10)
fetch from #ScriptColumns into @Table_Schema,@Table_Name,@ID,@Name,@AnsiPaddingStatus,@Computed,@ComputedText,@DataType,@SystemType,
@DataTypeSchema,@RowGuidCol,@Length,@NumericPrecision,@Identity,@IdentitySeed,@IdentityIncrement,@Collation,@NumericScale,@Nullable
End
Close #ScriptColumns
Deallocate #ScriptColumns
Declare #ScriptPrimary cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Name],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
i.is_unique AS [IsUnique],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
CASE WHEN 'FG'=dsi.type THEN dsi.name ELSE '' END AS [FileGroup],
clmns.name AS [indexcolumn],
ic.is_included_column AS [IsIncluded],ic.is_descending_key AS [Descending]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
WHERE
(tbl.name in (@lsTableName) and is_primary_key=1 /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
open #ScriptPrimary
fetch from #ScriptPrimary into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
if @Table_Name not in ('gnmcontrol','gntmenugroup_audit','gnmsessioninfo','gnmlocalsetup','actjobpartyturnover','gnmentityresources','achquarterclosingstatus','qbsamplegl')
begin
set @lsSql = @lsSql + 'CONSTRAINT ' + @Name + ' PRIMARY KEY ' + case @IsClustered when 1 then 'CLUSTERED' else 'NONCLUSTERED' end + '(' + CHAR(10)
end
While @@FETCH_STATUS =0
Begin
set @lsSql = @lsSql + @columnname + case @Descending when 1 then ' DESC' else ' ASC' end
fetch from #ScriptPrimary into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
set @lsSql = @lsSql + case when @@FETCH_STATUS=0 then ',' else '' end + CHAR(10)
End
--set @lsSql = @lsSql + ')) ON ' + @FileGroup + CHAR(10)
if @Table_Name not in ('gnmcontrol','gntmenugroup_audit','gnmsessioninfo','gnmlocalsetup','actjobpartyturnover','gnmentityresources','achquarterclosingstatus','qbsamplegl')
begin
set @lsSql = @lsSql + ' )) ' + CHAR(10) + @lsGo + CHAR(10)
end
if @Table_Name in ('gnmcontrol','gntmenugroup_audit','gnmsessioninfo','gnmlocalsetup','actjobpartyturnover','gnmentityresources','achquarterclosingstatus','qbsamplegl')
begin
set @lsSql = SUBSTRING(@lsSql,0,len(@lsSql) - 1)
set @lsSql = @lsSql + ' ) ' + CHAR(10) + @lsGo + CHAR(10)
end
Close #ScriptPrimary
Deallocate #ScriptPrimary
Declare #ScriptIndex cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Name],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
i.is_unique AS [IsUnique],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
CASE WHEN 'FG'=dsi.type THEN dsi.name ELSE '' END AS [FileGroup],
clmns.name AS [indexcolumn],
ic.is_included_column AS [IsIncluded],
ic.is_descending_key AS [Descending]
FROM
sys.tables AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
WHERE
(tbl.name in (@lsTableName) and is_primary_key=0 /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
open #ScriptIndex
fetch from #ScriptIndex into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
While @@FETCH_STATUS =0
Begin
--Modified by Samy on 13-02-2014 --to remove on primary
set @lsSql = @lsSql + 'CREATE ' + case @IsClustered when 1 then 'CLUSTERED' else 'NONCLUSTERED' end + ' INDEX [' + @Name + '] ON [' + @Table_Schema + '].[' + @Table_Name + ']' + CHAR(10)
set @lsSql = @lsSql + '( ' + CHAR(10)
set @lsSql = @lsSql + @columnname + case @Descending when 1 then ' DESC' else ' ASC' end + ')' + CHAR(10)
--set @lsSql = @lsSql + ') ON ' + @FileGroup + CHAR(10)
set @lsSql = @lsSql + + CHAR(10) + @lsGo + CHAR(10)
fetch from #ScriptIndex into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
End
Close #ScriptIndex
Deallocate #ScriptIndex
Declare #ScriptConstraint cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
clmns.column_id AS [Column_ID],
clmns.name AS [Column_Name],
cstr.name AS [Name],
cstr.definition AS [Text]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
INNER JOIN sys.default_constraints AS cstr ON cstr.object_id=clmns.default_object_id
WHERE
(tbl.name in (@lsTableName) /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[Column_ID] ASC,[Name] ASC
open #ScriptConstraint
fetch from #ScriptConstraint into @Table_Schema,@Table_Name,@ID,@columnname,@Name,@DefaultValue
While @@FETCH_STATUS =0
Begin
set @lsSql = @lsSql + 'ALTER TABLE ['+ @Table_Schema +'].['+ @Table_Name +'] ADD CONSTRAINT ['+ @Name +'] '
set @lsSql = @lsSql + 'DEFAULT ' + @DefaultValue + ' FOR [' + @columnname + '] ' + CHAR(10) + @lsGo + CHAR(10)
fetch from #ScriptConstraint into @Table_Schema,@Table_Name,@ID,@columnname,@Name,@DefaultValue
End
Close #ScriptConstraint
Deallocate #ScriptConstraint
set @lsOutScript=@lsSql
/*
declare @lsobjname as varchar(200),@lsobjscript as varchar(max)
set @lsobjname='gnmdcvsclient'
set @lsobjscript=''
exec UIGenTableScript @lsobjname,@lsobjscript out,' ; '
print @lsobjscript
*/
CREATE Proc [dbo].[UIGenTableScript](@lsTableName as Varchar(128),@lsOutScript as Nvarchar(max) out,@lsGo as varchar(10) = 'GO') as
declare @lsSql as Nvarchar(max),
@Table_Schema nvarchar(256),
@Table_Name nvarchar(256),
@ID int ,
@Name nvarchar(256),
@AnsiPaddingStatus bit ,
@Computed bit ,
@ComputedText nvarchar(max),
@DataType nvarchar(256),
@SystemType nvarchar(256),
@DataTypeSchema nvarchar(256),
@RowGuidCol bit ,
@Length int ,
@NumericPrecision int ,
@Identity bit ,
@IdentitySeed bigint ,
@IdentityIncrement bigint ,
@Collation nvarchar(256),
@NumericScale int ,
@Nullable bit ,
@Owner Nvarchar(100),
@IndexKeyType int,
@IsUnique bit,
@IsClustered bit,
@FileGroup nvarchar(256),
@columnname nvarchar(128),
@IsIncluded bit,
@Descending bit,
@DefaultValue nvarchar(256)
set @lsSql=''
Declare #ScriptTable cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Schema],
tbl.name AS [Name],
tbl.object_id AS [ID],
stbl.name AS [Owner]
FROM
sys.tables AS tbl
LEFT OUTER JOIN sys.data_spaces AS dstext ON tbl.lob_data_space_id = dstext.data_space_id
INNER JOIN sys.database_principals AS stbl ON stbl.principal_id = ISNULL(tbl.principal_id, (OBJECTPROPERTY(tbl.object_id, 'OwnerId')))
WHERE
(tbl.name in (@lsTableName) /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY
[Schema] ASC,[Name] ASC
open #ScriptTable
fetch from #ScriptTable into @Table_Schema,@Table_Name,@ID,@Owner
While @@FETCH_STATUS =0
Begin
--set @lsSql = @lsSql + 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID([' + @Table_Schema + '].[' + @Table_Name + ']) AND TYPE in (''U'')) ' + CHAR(10)
--set @lsSql = @lsSql + 'BEGIN ' + CHAR(10)
set @lsSql = @lsSql + 'CREATE TABLE ' + @Table_Schema + '.' + @Table_Name + '(' + CHAR(10)
fetch from #ScriptTable into @Table_Schema,@Table_Name,@ID,@Owner
End
Close #ScriptTable
Deallocate #ScriptTable
Declare #ScriptColumns cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
clmns.column_id AS [ID],
clmns.name AS [Name],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
clmns.is_computed AS [Computed],
ISNULL(cc.definition,'') AS [ComputedText],
usrt.name AS [DataType],
ISNULL(baset.name, '') AS [SystemType],
sclmns.name AS [DataTypeSchema],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
CAST(CASE WHEN baset.name IN ('nchar', 'nvarchar') AND clmns.max_length <> -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
CAST(clmns.precision AS int) AS [NumericPrecision],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS bigint) AS [IdentitySeed],
CAST(ISNULL(ic.increment_value,0) AS bigint) AS [IdentityIncrement],
ISNULL(clmns.collation_name, '') AS [Collation], CAST(clmns.scale AS int) AS [NumericScale],
clmns.is_nullable AS [Nullable]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0
)
and (baset.is_assembly_type = 1))
LEFT OUTER JOIN sys.schemas AS sclmns ON sclmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
WHERE
(tbl.name in (@lsTableName) /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[ID] ASC
open #ScriptColumns
fetch from #ScriptColumns into @Table_Schema,@Table_Name,@ID,@Name,@AnsiPaddingStatus,@Computed,@ComputedText,@DataType,@SystemType,
@DataTypeSchema,@RowGuidCol,@Length,@NumericPrecision,@Identity,@IdentitySeed,@IdentityIncrement,@Collation,@NumericScale,@Nullable
While @@FETCH_STATUS =0
Begin
set @lsSql = @lsSql + SPACE(5) + @Name + ' ' + @DataType + ' ' +
Case when @DataType = 'decimal' then '(' + CONVERT(varchar(10),@NumericPrecision) + ',' + CONVERT(varchar(10),@NumericScale) + ')'
when @DataType in ('varchar','nvarchar','char') then '(' + case @Length when -1 then 'MAX' else convert(varchar(50),@Length) end + ')'
else '' end + ' ' + + case @Identity when 1 then 'IDENTITY(' + convert(varchar(10),@IdentitySeed) + ',' + convert(varchar(10),@IdentityIncrement) + ') ' else '' end +
--Identity added by samy on 22-02-2014
Case @Nullable when 1 then 'NULL' else 'NOT NULL' end + ',' + CHAR(10)
fetch from #ScriptColumns into @Table_Schema,@Table_Name,@ID,@Name,@AnsiPaddingStatus,@Computed,@ComputedText,@DataType,@SystemType,
@DataTypeSchema,@RowGuidCol,@Length,@NumericPrecision,@Identity,@IdentitySeed,@IdentityIncrement,@Collation,@NumericScale,@Nullable
End
Close #ScriptColumns
Deallocate #ScriptColumns
Declare #ScriptPrimary cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Name],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
i.is_unique AS [IsUnique],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
CASE WHEN 'FG'=dsi.type THEN dsi.name ELSE '' END AS [FileGroup],
clmns.name AS [indexcolumn],
ic.is_included_column AS [IsIncluded],ic.is_descending_key AS [Descending]
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
WHERE
(tbl.name in (@lsTableName) and is_primary_key=1 /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
open #ScriptPrimary
fetch from #ScriptPrimary into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
if @Table_Name not in ('gnmcontrol','gntmenugroup_audit','gnmsessioninfo','gnmlocalsetup','actjobpartyturnover','gnmentityresources','achquarterclosingstatus','qbsamplegl')
begin
set @lsSql = @lsSql + 'CONSTRAINT ' + @Name + ' PRIMARY KEY ' + case @IsClustered when 1 then 'CLUSTERED' else 'NONCLUSTERED' end + '(' + CHAR(10)
end
While @@FETCH_STATUS =0
Begin
set @lsSql = @lsSql + @columnname + case @Descending when 1 then ' DESC' else ' ASC' end
fetch from #ScriptPrimary into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
set @lsSql = @lsSql + case when @@FETCH_STATUS=0 then ',' else '' end + CHAR(10)
End
--set @lsSql = @lsSql + ')) ON ' + @FileGroup + CHAR(10)
if @Table_Name not in ('gnmcontrol','gntmenugroup_audit','gnmsessioninfo','gnmlocalsetup','actjobpartyturnover','gnmentityresources','achquarterclosingstatus','qbsamplegl')
begin
set @lsSql = @lsSql + ' )) ' + CHAR(10) + @lsGo + CHAR(10)
end
if @Table_Name in ('gnmcontrol','gntmenugroup_audit','gnmsessioninfo','gnmlocalsetup','actjobpartyturnover','gnmentityresources','achquarterclosingstatus','qbsamplegl')
begin
set @lsSql = SUBSTRING(@lsSql,0,len(@lsSql) - 1)
set @lsSql = @lsSql + ' ) ' + CHAR(10) + @lsGo + CHAR(10)
end
Close #ScriptPrimary
Deallocate #ScriptPrimary
Declare #ScriptIndex cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
i.name AS [Name],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
i.is_unique AS [IsUnique],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
CASE WHEN 'FG'=dsi.type THEN dsi.name ELSE '' END AS [FileGroup],
clmns.name AS [indexcolumn],
ic.is_included_column AS [IsIncluded],
ic.is_descending_key AS [Descending]
FROM
sys.tables AS tbl INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
WHERE
(tbl.name in (@lsTableName) and is_primary_key=0 /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[Name] ASC
open #ScriptIndex
fetch from #ScriptIndex into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
While @@FETCH_STATUS =0
Begin
--Modified by Samy on 13-02-2014 --to remove on primary
set @lsSql = @lsSql + 'CREATE ' + case @IsClustered when 1 then 'CLUSTERED' else 'NONCLUSTERED' end + ' INDEX [' + @Name + '] ON [' + @Table_Schema + '].[' + @Table_Name + ']' + CHAR(10)
set @lsSql = @lsSql + '( ' + CHAR(10)
set @lsSql = @lsSql + @columnname + case @Descending when 1 then ' DESC' else ' ASC' end + ')' + CHAR(10)
--set @lsSql = @lsSql + ') ON ' + @FileGroup + CHAR(10)
set @lsSql = @lsSql + + CHAR(10) + @lsGo + CHAR(10)
fetch from #ScriptIndex into @Table_Schema,@Table_Name,@Name,@IndexKeyType,@IsUnique,@IsClustered,@FileGroup,@columnname,@IsIncluded,@Descending
End
Close #ScriptIndex
Deallocate #ScriptIndex
Declare #ScriptConstraint cursor for
SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
clmns.column_id AS [Column_ID],
clmns.name AS [Column_Name],
cstr.name AS [Name],
cstr.definition AS [Text]
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
INNER JOIN sys.default_constraints AS cstr ON cstr.object_id=clmns.default_object_id
WHERE
(tbl.name in (@lsTableName) /*and SCHEMA_NAME(tbl.schema_id)='dbo'*/)
ORDER BY [Table_Schema] ASC,[Table_Name] ASC,[Column_ID] ASC,[Name] ASC
open #ScriptConstraint
fetch from #ScriptConstraint into @Table_Schema,@Table_Name,@ID,@columnname,@Name,@DefaultValue
While @@FETCH_STATUS =0
Begin
set @lsSql = @lsSql + 'ALTER TABLE ['+ @Table_Schema +'].['+ @Table_Name +'] ADD CONSTRAINT ['+ @Name +'] '
set @lsSql = @lsSql + 'DEFAULT ' + @DefaultValue + ' FOR [' + @columnname + '] ' + CHAR(10) + @lsGo + CHAR(10)
fetch from #ScriptConstraint into @Table_Schema,@Table_Name,@ID,@columnname,@Name,@DefaultValue
End
Close #ScriptConstraint
Deallocate #ScriptConstraint
set @lsOutScript=@lsSql
Comments
Post a Comment